SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2000
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table questions with mySQL

    Hi,

    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

  2. #2
    SitePoint Zealot
    Join Date
    Nov 2001
    Location
    Earth
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Pardon the pun, but anything that has a "set" limit (i.e. 64) is bad. As soon as you get a company with 65 locations, the design falls apart.

    I would suggest a company table and a location table. The location table would have the key being the key of the company table + a sequence (or anything to make it unique).

    How you get the data is up to you. Either querying both tables or a join will work.

    If you join the table, you don't have to display the company info for each row. If you don't join (i.e. search company, then search location) you don't have to worry about returning so much "extra" data.

    I'm not sure if that helps, but it's my 2 cents.

    Frank
    http://www.serverexpert.com/ Discuss servers and the software that makes them work.
    http://www.webmasters-resources.com/ Resources for webmasters.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •