Re: Making Complicated References

>>>>> "DanielD" == Daniel R Anderson <dan@mathjunkies.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@stonehenge.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!