View previous topic :: View next topic |
Author |
Message |
ggelln Tux's lil' helper
Joined: 24 Jan 2003 Posts: 98 Location: Montreal Quebec
|
Posted: Mon Jun 02, 2003 6:46 pm Post subject: mysql database design questions |
|
|
I am setting out to make a db for my home bar.
I have looked up some articles on db normalization . . . and am trying to think about how I can inact this with my db design . . .
I am going to make two tables to deal with the bottles, namely a table called Liquor_Type, and Liquor_Brand. I want to do it this way so that when I start entering cocktail recipes I will be able to have a generic call such as "Vodka" or a more specific call such as "Chopin"
my problem is that I have some Liquor_Brands that have multiple types associated with them,
ie Cointreau is both a triple_sec and a liqueur . . . and I'm not sure how to use mysql to deal with this.
my Liquor_Brand table columns are as follows
+----------+------------+---------+
| brand_id | brandname | type_id |
+----------+------------+---------+
I am using brand_id as my primary key . . . so I'm not sure how I can have
Cointrea inherit two or more type_id's
sorry for the rambling, any help, or pointers to other help would be super !
Gabriel |
|
Back to top |
|
|
Rider Tux's lil' helper
Joined: 07 Jan 2003 Posts: 85 Location: Berne, Switzerland
|
Posted: Mon Jun 02, 2003 8:01 pm Post subject: Re: mysql database design questions |
|
|
Hi!
ggelln wrote: |
I am using brand_id as my primary key . . . so I'm not sure how I can have
Cointrea inherit two or more type_id's
|
you need to extract the type_id out of your Liquor_Brand table and make a third table which is called brand_to_type or something like that:
in this table the relation between the brand_id and the type_id is stored...
your Cointreau example would look like this:
Code: | tbl. Liquor_Brand:
+----------+--------------+
| brand_id | brandname |
+----------+--------------+
1 Cointreau
tbl. Liquor_Type:
+----------+--------------+
| liquor_id | liquorname |
+----------+--------------+
1 triple sec
2 liqueur
tbl. brand_to_type
+----------+--------------+
| brand_id | liquor_id |
+----------+--------------+
1 1
1 2
|
as you can see Cointreau (brand_id = 1) is of type triple sec (liquor_id = 1) and liqueur (liquor_id = 2)
after that your tables ar normalized.
hope this helps you!
have fun and cheeeeeeeeeeeeeeeeeeeeers! |
|
Back to top |
|
|
ggelln Tux's lil' helper
Joined: 24 Jan 2003 Posts: 98 Location: Montreal Quebec
|
Posted: Mon Jun 02, 2003 8:34 pm Post subject: |
|
|
Thank you so much for the quick help,
gentoo forums are really a wonder to behold . . .
I will get right on the fix . . . I am excited
Gabriel |
|
Back to top |
|
|
|