2

I have a question on the proper design of tables. Let's say I have a table called Hamburger and this hamburger can have many condiments on it such as cheese, ketchup, etc.. I want to hold this information in a set of tables. Each condiment must be in its own table because there are different columns for each condiment.

Here is what I have:

hamburger - (ham_id, title, etc..)

ham_condiments (ham_id, condiment_id, parent_condiment_id,condiment_type)

cheese (cheese_id, name, cheese specific columns)

meat (meat_id, name, some beef specific columns)

mayo (mayo_id, name, some mayo specific columns)

So, the ham_condiments is sort of showing the one to many from the hamburger table and the mayo_id, meat_id and cheese_id are used in the condiment_id column in the ham_condiments table.

The reason for all of this is the parent_condiment_id which will show the correct order of the condiments in the hamburger.

So, if you wanted to see all of the condiments on a hamburger, you would:

select ham.title, c.name, m.name, mo.name, condiment_type
hamburger ham,
ham_condiments hc,
cheese c,
meat m,
mayo mo
where
ham.ham_id = hc.ham_id and
hc.condiment_id = c.cheese_id and
hc.condiment_id = m.meat_id and
hc.condiment_id = mo.mayo_id
order by hc.parent_condiment_id

(parent_condiment_id could just be a sort_order column with values starting at 1,2,3,4 as well)

Am I just over thinking this here? I'm basically adding another table ust to hold the order of the condiments inside the hamburger but am having a hard time putting it together. Thanks for any help.

EricW
  • 23
  • 4

1 Answers1

1

First, you want to look into Type / SubType database patterns—search for SO answers on those topics, or online for detailed articles. Here are two such questions I posted to in the past:

Foreign Key Referencing Multiple Tables

Derived concepts - database design considerations

The general idea is you have a “master” table that defines all possible condiments, then you have another table for each type of condiment, where each as the “master condiment” table as it’s parent. The details are subtle and the implementation can be tricky.

Second, what your describing as “parent_condiment_id” sounds more like “condiment_order”, an attribute of the join between hamburger and condiments. (Or does it actually reference another table?)

Community
  • 1
  • 1
Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • Thanks for the quick response. You are correct, the parent_condiment_id should really just be condiment_order. You are correct again in I was trying to push too much out to the child tables. I need to just have the flow_condiments table be the master that holds basic info on condiments with sub tables for each condiment details. Thanks again Philip. – EricW Oct 21 '15 at 14:11