Re: Making Complicated References

>>>>> "DanielD" == Daniel R Anderson <dan(at)mathjunkies(dot)com> writes:

DanielD> <snip>
>> How about some table definitions?
DanielD> </snip>

DanielD> below is a simplified version:

DanielD> CREATE TABLE products
DanielD> (
DanielD> product_key varchar(80), primary key,
DanielD> attribute_1 varchar(80), not null,
DanielD> attribute_2 varchar(80), not null,
DanielD> ...
DanielD> attribute_n varchar(80) not null
DanielD> );

DanielD> CREATE TABLE sizes
DanielD> (
DanielD> product_key varchar(80), references products,
DanielD> packaging_type varchar(80), -- i.e. Bags
DanielD> -- i.e. Drums
DanielD> -- i.e. Bottles
DanielD> weight varchar(80) -- i.e. 5lbs
DanielD> -- i.e. 1 gallon
DanielD> -- i.e. 8 oz.
DanielD> );

DanielD> So now I want to create a table called orders. This table must
DanielD> reference the item_number in products AND reference the packaging type
DanielD> and weight so that if somebody orders stearic acid they can't select a
DanielD> 4.3 lb bag if we only sell 50lb bags.

That's not normalized. Your type/weight depends partially on your key.

What you really have is a product with a size and weight, and
a foreign key to an attribute set for a collection of products.

Then your order refers to a particular product, and you can drill
down from there to get to the common attributes.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn(at)stonehenge(dot)com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!