query table with dynamic fields

Question

I have a table with the structure in the image. A post is conformed by all the rows with the same lead_id (17 fields in total). That said, how can I filter posts by a field_value? For instance query all the posts that has the field_value "ibague". Doing that with that table structure seems to be tricky. I was thinking of creating another table with primary key lead_id and create a column for every one of the 17 fields, and exporting the data with some horrible cursor in mysql, or some loop in php, and then make the query in a table easier to query. But with that approach I would have to create manually another column if a new field is added, and export the new data every time someone access to the report module. Any ideas my fellow programmers?

Thanks a lot.

Answer 1

This is a common dilemma in database design: flexibility vs easy to search. Your current table structure is optimised towards flexibility. Whether this is good for your current application or not we cannot tell, that's a decision you have to make.

However, you can search for forms within your current structure as well:

select lead_id
from yourtable
where `value`='ibague'
and form_id=1 and field_number=73 --not clear from your description if these conditions are needed

If you need all field values for a given lead where one of the fields match the search criteria, then place the above query into a subselect:

select * from yourtable t1
inner join (
    select distinct lead_id
    from yourtable
    where `value`='ibague'
    and form_id=1 and field_number=73) t2 on t1.lead_id=t2.lead_id

If form_id is also needed for the match, then include it both in the select list of the subquery and in the on clause of the join.

If you decide to flatten the data structure, then there is no need for complicated cursors, just use a technique called dynamic pivoting or cross tabulated query