Making Complicated References

On Sun, Mar 30, 2003 at 03:06:46 +0000,
"Daniel R. Anderson" <dan@mathjunkies.com> wrote:

Hi all,

I have tables A, B, and C. foo within Table B & C references foo in
table A. I want bar in table C to reference bar in table B -- but I
want it to reference only those bars in B with a foo that is the same.

Is that possible?

If foo + bar is a candidate key for B, then you can use a multicolumn
foriegn key. If foo + bar isn't a candidate key for B, then I think you
will need to explain more about what you are trying to do.

How about some table definitions?

(amazing! what everyone else asked me for before they'd help me when **I** first
got here, now I ask others to supply :-)

Bruno Wolff III wrote:

<snip>

How about some table definitions?

</snip>

below is a simplified version:

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

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

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

This is what I mean by ¨complicated references¨

Thanks in advance,

-Dan

"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/&gt;
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!