Colaluca, Brian wrote:
I have come to a brick wall on one facet of my design, however. I've comeJust an idea ... to get your head spinning (and some sample queries):
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.
Wine
-----
ID int unsigned not null auto_increment primary key,
Name ...
Winery ...
Grapes
-----
ID int unsigned not null auto_increment primary key,
Name ...
Vineyard? ...
GrapesInWine
-----
WineID int unsigned not null,
GrapesID int unsigned not null,
Percentage int unsigned not null
... where Percentage is between 0 and 100.
Then you can, to insert a wine named "Foo" with 50% of each "Grape1" and "Grape2":
INSERT INTO Wine (Name) VALUES ("Foo");
SELECT @WinesID := last_insert_id(); # I'm using server variables here for the sake of demo ...
INSERT INTO Grapes (Name) VALUES ("Grape1");
SELECT @GrapesID := last_insert_id();
INSERT INTO GrapesInWine (WineID, GrapesID, Percent) VALUES (@WinesID, @GrapesID, 50);
INSERT INTO Grapes (Name) VALUES ("Grape2");
SELECT @GrapesID := last_insert_id();
INSERT INTO GrapesInWine (WineID, GrapesID, Percent) VALUES (@WineID, @GrapesID, 50);
Then, to find out what's in the wine named "Foo":
SELECT * FROM Grapes
LEFT JOIN GrapesInWine
ON Grapes.ID = GrapesID
LEFT JOIN Wine
ON WinesID = Wine.ID
WHERE Wine.Name = "Foo";
Or, to find the amounts of "Grape1" in all wines:
SELECT * FROM Wine
LEFT JOIN GrapesInWine
ON WineID = Wine.ID
LEFT JOIN Grapes
ON Grapes.ID = GrapesID
WHERE Grapes.Name = "Grape1";
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock
---------------------------------------------------------------------
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
