Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Relating two tables together 1

Status
Not open for further replies.

bernie10

Technical User
Sep 13, 2004
219
US
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

 
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)

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top