On Mon, 2002-02-25 at 12:06, Derek Glidden wrote:
> columns you might have to join - an example is you have a list of
> products and a list of vendors, a product might come from multiple
> vendors, but you don't know beforehand how many, so it doesn't make
After re-reading that message and realizing the description didn't make
any sense, I had a better idea, a concrete example:
BAD WAY:
CREATE TABLE Vendor (
VendorID int PRIMARY KEY,
Name varchar(30),
Phone varchar(30)
);
CREATE TABLE Product (
ProductID int PRIMARY KEY,
Name varchar(30),
Description varchar(80),
Vendor1 int REFERENCES Vendor(VendorID),
Vendor2 int REFERENCES Vendor(VendorID),
Vendor3 int REFERENCES Vendor(VendorID),
Vendor4 int REFERENCES Vendor(VendorID),
Vendor5 int REFERENCES Vendor(VendorID),
Vendor6 int REFERENCES Vendor(VendorID)
);
Then to find out what products a vendor carries you have to do something
ugly like this:
SELECT * FROM Product WHERE Vendor1 = 5
OR Vendor2 = 5
OR Vendor3 = 5
OR Vendor4 = 5
OR Vendor5 = 5
OR Vendor6 = 5;
It's even more hideous to find out what vendors carry a specific
product:
SELECT * from Vendor, Product
WHERE ProductID = 5
AND ( Product.Vendor1 = Vendor.VendorID
OR Product.Vendor2 = Vendor.VendorID
OR Product.Vendor3 = Vendor.VendorID
OR Product.Vendor4 = Vendor.VendorID
OR Product.Vendor5 = Vendor.VendorID
OR Product.Vendor6 = Vendor.VendorID );
The programmer has to do more work, the database has to do a LOT more
work, and of course you're screwed if you have more than 6 vendors that
carry a product. It's amazing how many databases I've seen with this
kind of structure in it though.
THE RIGHT WAY:
CREATE TABLE Vendor (
VendorID int PRIMARY KEY,
Name varchar(30),
Phone varchar(30)
);
CREATE TABLE Product (
ProductID int PRIMARY KEY,
Name varchar(30),
Description varchar(80)
);
CREATE TABLE VendProd (
ProductID int REFERENCES Product(ProductID),
VendorID int REFERENCES Vendor(VendorID)
);
Then to find out what vendors carry a product, you just do:
SELECT * FROM VendProd, Vendor
WHERE VendProd.ProductID = 5
AND VendProd.VendorID = Vendor.VendorID;
To find what products a particular vendor carries, you do pretty much
the same thing, except by the other fields:
SELECT * FROM VendProd, Product
WHERE VendProd.VendorID = 5
AND VendProd.ProductID = Product.ProductID;
It's much easier to read, more concise, much less work on the database,
and just plain The Right Way to do it.
And now everyone knows a little more about database design than anyone
in their right mind should ever care to know. :)
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
#!/usr/bin/perl -w
$_='while(read+STDIN,$_,2048){$a=29;$b=73;$c=142;$t=255;@t=map
{$_%16or$t^=$c^=($m=(11,10,116,100,11,122,20,100)[$_/16%8])&110;
$t^=(72,@z=(64,72,$a^=12*($_%16-2?0:$m&17)),$b^=$_%64?12:0,@z)
[$_%8]}(16..271);if((@a=unx"C*",$_)[20]&48){$h=5;$_=unxb24,join
"",@b=map{xB8,unxb8,chr($_^$a[--$h+84])}@ARGV;s/...$/1$&/;$d=
unxV,xb25,$_;$e=256|(ord$b[4])<<9|ord$b[3];$d=$d>>8^($f=$t&($d
>>12^$d>>4^$d^$d/8))<<17,$e=$e>>8^($t&($g=($q=$e>>14&7^$e)^$q*
8^$q<<6))<<9,$_=$t[$_]^(($h>>=8)+=$f+(~$g&$t))for@a[128..$#a]}
print+x"C*",@a}';s/x/pack+/g;eval
usage: qrpff 153 2 8 105 225 < /mnt/dvd/VOB_FILENAME \
| extract_mpeg2 | mpeg2dec -
http://www.cs.cmu.edu/~dst/DeCSS/Gallery/
http://www.eff.org/ http://www.anti-dmca.org/
This archive was generated by hypermail 2.1.3 : Fri Aug 01 2014 - 16:42:29 EDT