Relating two tables together 1

[bernie10]

Hello,

A simple database question. Suppose I have two tables: A commodity types table, and a manufacturer's data table with prices from manufactures of particular commodities all of which must be on the commodity types table. A commodity type is "uniquely defined" by three different fields.

I need to relate the two tables together. Should I include these three fields on the manufacture's data table as well and then join by these three fields, or should I create a new "primary key" field (like an AutoNumber) on the commodity types table which would uniquely identify a commodity and then include this field on the manufactures data table and join by this new field. I guess I'm wondering what the best DB practice is.

There can be many different manufacture entries corresponding to a single commodity type. Each manufacturer entry has to correspond to one of the commodity types.

Thanks in advance for the help,
CM

 

[BNPMike]

Just a picky point - you're not relating the tables; you're joining them. I know Microsoft sometimes think "relational" comes from "relate", but it doesn't. It comes from the word "relation" which is a mathematical term for basically a table.

Anyway to answer your question there is no harm whatsoever in using a three-part key to join tables. You can even declare it as a primary key if you feel so inclined. The plus is that the key is probably recognisable to users whereas a new artificial one wouldn't be. The minus is you've got more typing to do when changing keys/connecting disconnecting manufacturers.

One point - it is rather unlikely that manufacturer to commodity is many-to-one. I suggest it is more realistic to treat it as many-to-many ie via an intersection entity that relates manufacturers to commodities. (Relates in the normal English sense, of course)

 

[bernie10]

Thats what I wanted to know... Thanks.