i was playing around with ideas below,
so they may be worthless chatter :-)
what i was
thinking "I'll just assume the wine world is not
wildly un-uniform..." and see where that gets me... :-)
and "wine is just an object..."
but the wine has many grape varieties sounds a hell
of a lot like "one to many" in sql
thanks
Jim
===============================start chatter
n = null
anything else = not null
all attributes analysis
a1 a2 a3 a4 a5 a6 a7
wine1 x n x n x n x
wine2 y x x n n x n
wine3 z x x x x n n
8 nulls
common attributes table <parent>
ID a1 a3
wine 1 x x
wine 2 y x
wine 3 z x
special attributes table 1 <child0>
ID a5 a7
wine 1 x x
wine 3 x n
special attributes table 2 <child1>
ID a2 a6
wine 2 x x
wine 3 x n
special attributes table 3 <child2>
ID a4
wine 3 x
2 nulls
=======================end chatter
-----Original Message-----
From: Michael T. Babcock [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 09, 2003 6:09 PM
To: Colaluca, Brian
Cc: [EMAIL PROTECTED]
Subject: Re: MySQL Database Design
Colaluca, Brian wrote:
>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.
>
>
Just an idea ... to get your head spinning (and some sample queries):
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
---------------------------------------------------------------------
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
