Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2012
    Posts
    4

    Unanswered: Database Table Structure Design - One to Many

    I am new to database design and have a question on how to structure some tables.
    For clarity, I made a short spreadsheet that gives a snapshot of the data I am dealing with (an example), though this does not represent the database structuring.
    Here is my question:
    I have a table of options a customer can choose. Each option then might have multiple choices.
    For example, they can choose to purchase the option for "driveway lights." Once done, they can choose from "Configuration A" or "Configuration B"
    Or if they choose to purchase a "front gate" it can be configured in multiple ways, giving them up to 5 choices.
    There is a table for "Options." Should I have an "Option Choices" field in that table, or should "Option Choices" be a separate table in itself, thus creating a One-To-Many Relationship, where one option has 0 - n choices.
    This is a very critical part of the database and if it's not done correctly it can really complicate things.
    There are hundreds of options and each option will have 1 - 5 choices (or values)..,thus thousands of possibilities.
    And finally, there will be a complex application that performs some "AND" & "OR" logic on the choices made.
    Example..."IF: Option 5 == Choice 3 AND Option 35 == Choice 1, THEN: "DO THIS." There are again hundreds of these types of combinations. I am trying to structure the database to make the design of this application as simple as possible.
    Attached Thumbnails Attached Thumbnails DB Structure Question.png  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd suggest you consider making the final product to be option a or b, and have 'driveway lights as the product type, or the final lower level of product type
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2012
    Posts
    4
    Unfortunately I can't change the way this is done in terms of Options and the Option Values (Choices) as that is based on an existing system that cannot be changed. The example I gave is just that, only an example...
    Another way to think of it is like buying a car: first you decide on the make and you contact a dealer, then you choose a general model based on your needs (SUV, Sedan, etc.). Once you've done that, you speak with a salesman to decide on what options you want on the car. Some come standard (anti-lock brakes, airbags, parking brake, windshield wipers) and others you can choose to have or not (cd player, on-board computer, sun roof) and some you can choose how to configure (leather vs. cloth seats, heated seats: enabled or disbaled, power windows: enabled or disabled, factory rims vs custom rims (which then may have 5 possibilties).
    So, the product is being sold strictly based on a list of options. The customer chooses which options they want and how they want that option configured. Some items are more customizable than others, some are simply "enabled or disabled" such as "on board GPS" - you either have it or you don't.
    So again, the question is how to link the OPTION with it's mutiple OPTION-CHOICES.
    I cannot change this terminology or the way in which the process works. I need to a build a system around the process.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You're better off with options and choices in separate tables.

    You would index the option table with option (or ID#, which makes more sense) being a unique Primary key, and the choices table containing a Foreign key (ID# from options table) and the choices (or choice ID#), with the two of them being the primary key for that table. Then make the one-to-many relationship. You can then, if you wish, also have a sub-form on the order entry form so you can enter both the option and the choice desired in the same form, and they will be saved in the separate tables.

    The above is not hard at all. If you need more guidance, see the Help file for Indexing, Relationships, and Sub-forms.

    Sam

  5. #5
    Join Date
    Jun 2012
    Posts
    4
    Thank you Sam, that does make sense. I definitely know how to do the steps you describe but will check the help files if I run into trouble.
    The key question was whether or not to separate them into distinct tables and I agree that is the right way to go.

  6. #6
    Join Date
    Jun 2012
    Posts
    4
    One other question I now have on this same topic as I work on this more. Can I make a custom primary key for the "choices" table? Because all of the "choices" are simply a sub category of "options" I would like them linked numerically if possible. So, if an option is "Option 190" and I am choosing "Option 190, Choice #4" I would like the "Choice" to have a primary key of "190-4." Is this possible without creating other difficulties down the road? Options are simply added numerically in order...so if I have to add an option down the road, it will just take on the next value, i.e. 191, whatever. If I need to add a "choice" I would like then be able to add "190-5," "190-6" etc...without causing other problems. Is this possible? Again I am stressing that this numbering would be the PRIMARY key for this table.
    Thank you!!

Posting Permissions

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