Does anyone know which of the following would be more efficient with MySQL, or an alternative idea to achieve the following.
I will have a table full of company names, and information on which states each company has a location in. The goal is to search for companies that are in a user selected list of states, ie all companies in TX and TN.
Option 1:
Table with 3 columns, id, company and states. States contains a text string of states that the company has locations in and using either a FULLTEXT search, or a where statement with a whole load of 'state = "%TN%" OR state = "%TX%"' etc etc.
Option 2:
Table with 2 columns, id and company.
Table with 2 columns parent and state.
The tables have a one to many relationship, ie 1 company record, and many state records one for each state the company has a location in.
To perform the search 'select parent from table2 where state IN ("TX", "TN"))', then select * from table1 where id IN (array of results from first query)
Basically, the difference between the 2 options being one would search for values in text strings, and the other search multiple records but comparing the values of whole columns.
Any ideas?
Thanks In Advance!!
I will have a table full of company names, and information on which states each company has a location in. The goal is to search for companies that are in a user selected list of states, ie all companies in TX and TN.
Option 1:
Table with 3 columns, id, company and states. States contains a text string of states that the company has locations in and using either a FULLTEXT search, or a where statement with a whole load of 'state = "%TN%" OR state = "%TX%"' etc etc.
Option 2:
Table with 2 columns, id and company.
Table with 2 columns parent and state.
The tables have a one to many relationship, ie 1 company record, and many state records one for each state the company has a location in.
To perform the search 'select parent from table2 where state IN ("TX", "TN"))', then select * from table1 where id IN (array of results from first query)
Basically, the difference between the 2 options being one would search for values in text strings, and the other search multiple records but comparing the values of whole columns.
Any ideas?
Thanks In Advance!!