>>>>> "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!