Table Layout

[Emilie]

I need help figuring out how to set up the design of my database. It is
complicated to use the real variables so I am going to use Alcholic Drinks as
an example:

I will start with the drink name, Sunday Night Special
What ingredients are in it: Liquor, Juice, Soda, Garnishes, and Other.
Lets say Sunday Night Special uses Liquors, juices and garnishes. To get
more specific I need to know the types of each, so for Liquor there is Rum,
Vodka, Whisky, Tequila etc. My drink uses two types of rum, 1 tequila, 2
juices and a cherry.

To further break down the drink, the brands of rum, tequilla, juice and
cherry need to be selected. But lets say there were thousands and thousands
of specific brands so standardized codes were used instead (would manually
input codes).
So for the Liquor it used, Rum and Tequilla -->Rum 2300 and Rum 6555 and
Tequila 8622. Juice, Pineapple 5222 and Cranberry 4B82. Garnish, Cherry 6565.

To be an extra pain I want to know exactly how to make those specific
ingredients, so I will link to an external website that tells me exactyly
what is in each ingredient, eg how to make Rum 6555. To find the specific
ingredients faster on the sites, I will add tags such as Paragraph 4 or Para
7.8.3 to locate it easier. To check the reliabilty of the sites I want to
know the date the specific ingredients were updated to that page, 12/29/2003.
( Finally, I would attach a recipee template that guides me on how to make it
Sunday Night Special. )

So a query I would want to run is which drinks use Rum 6555 and Mixer 29999?

One of my problems is when a drink uses two different types in one category,
eg it uses three liquors: 2 rums and 1 tequilla, and then being able to break
thoes down to add further specifics.

I really hope this makes sense. Anything will help, maybe I am looking at
my information the wrong way? I am sorry if it is confusing!

 

[KARL DEWEY]

I will not have as many layers as you have lain out.
You might start with some method of categorizing the drinks -
Cocktail, Mixed, Highball, some such.
Then names --
ingredients by ID

You start a new set of tables by categorizing ingredients --
Liquor, Mix, Garnishes, etc.
Then another category layer --
Distilled, Brewed, etc.
- Vodka, etc. - Beer, Stout, Ale, etc. These have an ID to
be used above as ingredient.

These layers have a one-to-many relationship.

 

[Bruce Meneghin]

You might consider focusing on the essential entities and not getting
cluttered by their classification. From what I read, you need to key in on
the Drink and the specific Ingredient (Rum 2300) Maybe the classification
of the ingredient doesn't matter too much. The classifications of the
ingredient can just be attributes of that ingredient.

 

[Fred]

You have defined you mission in a way such that a database that solves it
100% will be somewhat complex, and require a longer post to rigorously
describe. In reality, your problem is like databasing manufacturing,
including bills of material.

I would only add a thought or 2 to the good advice of the others.

At a few levels, (to avoid unnecessary complexity) you will need to decide
whether or not there is a need to treat common attributes of groups as
entities. For example, are Rum2300 and Rum6555 simply 2 independent
ingredients (of which "Rum" and "liquid" might be attributes) or do yo also
need a table of groups of liquors (Rums, Vodkas etc.)