I'm starting out on the long road of Database design and after reading Kevin Yanks book, and the relevant sections in WROX I have a few questions on the 'best' methods of database design.
I have a Table 'companies' which lists relevant contact details for a whole load of companies. Each company may operate from 1 or more locations.
To represent this my initial thoughts on the best way to do this was to use a 'SET' column type. This would allow me to keep all this information in one table as SET would allow me to add in any combination of upto 64 seperate values.
However in Kevin's book SET types are not mentioned and they seem to go against the rule of thumb that you should never store more than one value in any single column.
Therefore the alternative would be to use a seperate 'location' table and then have a lookup table.
However - Say I wanted to retrieve all the information on one company (inc all the locations they operate) - using a SET would be simple - you would just select everything from the 'companies' table relevant to company ID and all the locations would be included in the row. To view the company info I'd just have to iterate through the fetched array and 'echo' out $Keys and corresponding $Values - SIMPLE.
To end up with the same information via the 'locations' table method seems much more complex: - Say I had a company that operated from 3 locations - If I did a 'table join' and executed 1 query to get the same info then I would end up with 3 rows each containing the exact same 'company information' but each with a different location value - If I echoed that out its just going to output the data in a repetative format.
The other option would be to have to execute two queries - one to get the main company info, then another to get the locations. Then store the locations in an array, implode and make them into a string and then echo then out in the middle of where I'm echoing out the main company info.
As you can see from the above using a SET seems far more simple, however what Kevin's book has said has worried me about their use.
If I need to change elements in a SET what happens to the data ?
If I use the seperate table method then does anyone know how I can quickly get the information I want on company X in a more simple method than the ones I've outlined above.
When I read WROX they do use ENUM for several things such as say 'locations' from a set list (although ENUM only allows you to store one value from a set list, rather than SET which allows 1 or more) - WROX even goes as far as to show you a script to get all the possible values from a SET or ENUM so that you can uses them for dropdown menu's etc etc.
I would really appreciate any thoughts you have on the above as I want start using the 'best' approach to DB design but the information I seem to have found kind of gives me conflicting ideas.
Thanks in advance