Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 8 of 8
  1. #1
    Member TheGardener's Avatar
    Join Date
    Jun 2006
    Posts
    62
    Member #
    13332
    I'm working on a product information database. Each product has many information columns describing its features. For example, let's say I'm creating a database of information on cars. I create a column titled "drivenWheels," with enum values '4WheelDrive','frontWheelDrive','rearWheelDrive'. Another column could be "fuelType," with values like 'gasoline','diesel', and so on

    While working on this database, it occurred to me that I might want to have a written description associated with each of these values. For example, when the data for a car is displayed in a table on a webpage, I might want to allow users to click on the descriptors like "Rear Wheel Drive" to get a popup window with a written description of what rear wheel drive is.

    One logical approach to this would be to abandon the use of enum variables and instead create separate tables just for the feature descriptors and link them to the master table, which would have integer values referring to the features listed in the linked tables. This would allow me to create text columns in the linked tables containing descriptions of each feature.

    On the downside, there are a lot of columns in the car data table, and if I created a linked table for each set of features -- fuel types, engine types, upholstery types, transmission features, etc. -- I would end up with a huge number of tables and it may be difficult for me to keep track of all their relationships.

    So what do you think is the best approach?

  2.  

  3. #2
    Junior Member
    Join Date
    Jun 2007
    Location
    Los Angeles
    Posts
    25
    Member #
    15371
    The approach I would take is to list each attribute in it's own table and use a "pointer" int to point to that table from a master table. The advantage to this is that if you ever need to make changes to the attribute, you only have to make it in one place. This is basically what your second suggestion to yourself was and I think that's the way you should go.
    So, for example, if I have a master table called cars it might have the following attribute fields:
    engine_type
    drive_train
    etc.
    The table engines would have entries such as 4 cyl, 6 cyl, 8 cyl
    The table drivetrains would have 2 wd, 4 wd, etc.
    Then your master table field attributes would be int types pointing to the unique record number of the attr tables.
    -------------------------------------
    RalphF
    Domains & $4.99 Web Hosting
    http://www.GoldRushWebHosting.com

  4. #3
    Member TheGardener's Avatar
    Join Date
    Jun 2006
    Posts
    62
    Member #
    13332
    Yes, that's the other approach I was considering. The problem with it is that I'll end up with dozens of linked tables. Right now, I can look at my main car information table in phpMyAdmin and get a pretty good idea of the possible configurations for a product, because the enum values are all listed in one place. If I use linked tables, I'll no longer be able to see all those values; I'll have to click on the individual tables to view the values inside them.

    And it seems that if I wanted to change a value (say I wanted to change "4wd" to "4WheelDrive") I could just create the new 4WheelDrive value in the enum column, change all cars with the 4wd value to 4WheelDrive, and then erase the 4wd value from the enum column.

    Do you think there are other advantages to the linked-table strategy?

  5. #4
    Junior Member
    Join Date
    Jun 2007
    Location
    Los Angeles
    Posts
    25
    Member #
    15371
    I've worked on many IT projects in the past with DBA's and linked pointers is the way to go - however - implement what works for you and what you are more confortable with.
    Yes, you can makes changes in your master table that contains everything but you have to be very careful with SQL queries else a mistake could be made that affects the whole master table!
    Do what makes you comfortable and good luck!
    RalphF
    Domains & $4.99 Web Hosting
    http://www.GoldRushWebHosting.com

  6. #5
    ljm
    ljm is offline
    Senior Member ljm's Avatar
    Join Date
    Aug 2006
    Location
    Manchester, England
    Posts
    284
    Member #
    13684
    Liked
    1 times
    You could always put your database down on paper and keep it relational, rather than shoving everything into one huge table. If you have a diagram that explains the schema, you can refer to that rather than try to get through the database itself. You may have lots of tables and links, but you will have a database that separates things clearly and makes it easily accessible, and that's what databases are designed for.

  7. #6
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    Separate tables, always. enum is not only MySQL-specific, but is the exact wrong thing to do compared to just making the database normalized in the first place.

    And I speak from first-hand experience. It was a bitch converting some old crusty MySQL code at work to PostgreSQL, which rightly doesn't support enum.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  8. #7
    Member TheGardener's Avatar
    Join Date
    Jun 2006
    Posts
    62
    Member #
    13332
    Looks like linked tables are the way to go. I'm planning on having the feature lists be expandable by users, which I realized could be a serious problem with enums if something goes wrong. A little work now will save a whole lot later, I guess.

  9. #8
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    If you want to associate the items with descriptions, then you definitely want separate tables with foreign keys referencing them. If you don't need those descriptions, there's really little reason why you shouldn't use enums. That it's MySQL-specific isn't a problem unless you're considering using something else.

    Others, naturally, disagree with me (see filburt's post ). However, I'm more and more a fan of the approach of agile development, where you do the simplest thing that achieves what you want right now, and enums do that if you don't need associated descriptions.


Remove Ads

Posting Permissions

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

Search tags for this page

enum or separate table

,

separate table for vehicle

Click on a term to search for related topics.
All times are GMT -6. The time now is 05:28 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com