Hi Brian,
By no means am I a MySQL guru (or any other database server environment
guru, for that matter), but could you simply have a reference table that
indicates the percentage of the grape used in the relevant wine?
So, you might have three tables, Wines, GrapeVariety,
Wines_GrapeVarietiesUsed (or whatever).
In Wines, you record the details of the wine in question. In
GrapeVariety you record the details of the different grapes. In
Wines_GrapeVarietiesUsed, you record the key from the Wine table, the
key from the GrapeVariety table, and the percentage of the grape
variety.
As an example:
Wines:
Wineid, winename, winedescription
1, wine1, wine description 1
2, wine2, wine description 2
3, wine3, wine description 3
GrapeVariety:
Varietyid, varietyname, varietydescription
1, variety1, variety description 1
2, variety2, variety description 2
3, variety3, variety description 3
And then in Wines_GrapeVarietiesUsed:
Wineid, varietyid, percentage
1, 1, 1
2, 2, 1
3, 1, 0.2
3, 2, 0.3
3, 3, 0.5
In this example we have 3 bottles of wine and three varieties. Wines 1 &
2 use 100 percent (i.e. 1) of varieties 1 and 2 respectively, whereas
wine 3 uses all three grape varieties with 20 percent of variety1, 30
percent of variety2 and 50 percent of variety3.
To perform a query that would depict all of this in a single resultset,
you might do something like:
SELECT w.winename, w.winedescription, v.varietyname,
v.varietydescription, gv.percentage FROM wines w, GrapeVariety v,
Wines_GrapeVarietiesUsed gv WHERE w.wineid = gv.wineid AND v.varietyid =
gv.varietyid;
What this delivers is a recordset that looks something like:
'wine1','wine description 1','variety1','variety description 1','1'
'wine2','wine description 2','variety2','variety description 2','1'
'wine3','wine description 3','variety1','variety description 1','0.2'
'wine3','wine description 3','variety2','variety description 2','0.3'
'wine3','wine description 3','variety3','variety description 3','0.5'
Below are the CREATE TABLE and INSERT INTO statements I used to build
this example.
Hope this helps a little,
All the best,
MW
CREATE TABLE Wines (wineid INT(10) unsigned NOT NULL auto_increment,
winename VARCHAR(100), winedescription TEXT, PRIMARY KEY (`wineid`))
TYPE = MYISAM;
CREATE TABLE GrapeVariety (varietyid INT(10) unsigned NOT NULL
auto_increment, varietyname VARCHAR(100), varietydescription TEXT,
PRIMARY KEY (`varietyid`)) TYPE = MYISAM;
CREATE TABLE Wines_GrapeVarietiesUsed (wineid INT(10) unsigned NOT NULL,
varietyid INT(10) unsigned NOT NULL, percentage float NOT NULL default
'0') TYPE=MYISAM;
INSERT INTO wines (winename, winedescription) VALUES ('wine1', 'wine
description 1');
INSERT INTO wines (winename, winedescription) VALUES ('wine2', 'wine
description 2');
INSERT INTO wines (winename, winedescription) VALUES ('wine3', 'wine
description 3');
INSERT INTO GrapeVariety (varietyname, varietydescription) VALUES
('variety1', 'variety description 1');
INSERT INTO GrapeVariety (varietyname, varietydescription) VALUES
('variety2', 'variety description 2');
INSERT INTO GrapeVariety (varietyname, varietydescription) VALUES
('variety3', 'variety description 3');
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (1,1,1);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (2,2,2);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (3,1,.2);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (3,2,.3);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (3,3,.5);
-----Original Message-----
From: Colaluca, Brian [mailto:[EMAIL PROTECTED]]
Sent: Friday, 10 January 2003 6:56 AM
To: [EMAIL PROTECTED]
Subject: MySQL Database Design
I am in the midst of designing a personal database for keeping track of
wines. After perusing through several beginner books on MySQL and PHP,
I
have decided that my next step would be to embark upon database design.
My design is almost complete and normalized, although I do expect to be
making many tweakings as my knowledge progresses.
I have come to a brick wall on one facet of my design, however. I've
come
to understand that having a lot of NULLs in your database may be a sign
of a
poor design. And yet I'm having a problem reconciling this with the
wildly
un-uniform world of wines from around the world. For instance, I would
like
to have a table called "GrapeVariety," and have the variety_id be a
primary
key. Another table would be "Wine." And yet, one wine could have one
type
of grape or more.
For instance, let's say that wine XYZ has 80% GrapeA, and 20% GrapeB.
Since
my grape variety would presumably be a foreign key in the Wine table,
how
could I specify a certain *percentage* of a foreign key? I've tried
hashing
this out in numerous ways, including the addition of a "Blend" table
with
multiple primary keys, but anyway I slice it, there will still be an
abundance of NULLs. For while the majority of wines may only contain
one
grape, there could be wines that have up to 5 or 6 in varying
percentages.
My apologies if this is inappropriately posted to this list (i.e. sorry
for
all the "wine"-ing). Any links or suggestions as to where I may find
answers to database design issues would be greatly appreciated.
bjc-
:::::====== Brian J. Colaluca - Software Engineer
:::::====== DRS Technologies - ESG
=========== [EMAIL PROTECTED]
=========== phone: (301) 921-8107
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
