Re^4: OT - SQL choosing a schema for index tables

[bangor]

Thank you poj, you are of course correct, and my Option 2 will not work as I had hoped - you have saved me some time there. I am using MySQL and in this table there are about 200 rows.

[poj]

I should point out that you could make it work but it would be rather complicated !.
Alternatively you could retain the multiple values and use REGEXP in the SELECT . For example

Read more... (2 kB)

Or you could select the records using the single value fields where possible and then filter them with a perl routine using regular expressions.

poj

[bangor]

This is something like how the current system (in PHP) works, but it uses LIKE in the SELECT and sometimes returns incorrect results. I didn't know you could use REGEXP and I can see how it would fix that problem - thank you.

Your second suggestion could also work for me, but as you imply, it is not guaranteed that the query will contain a value for the single-value fields.

[poj]

One further idea that may be useful for fields that have defined values like spring,summer etc is the SET type http://dev.mysql.com/doc/refman/5.0/en/set.html

but they have to be set up when the table is created

poj