many to many to many? 1

[mmaglio]

After reading up on database theory and Access, I'm only more confused on how to get this database to work.

Its a database of manufacturers, vendors and products. Many vendors represent many manufacturers, who make many different products. Manufacturers are represented by many vendors.

is it possible to connect tables in such a way as to be able to see which vendors sell which products, without repetition?

thanks to all,

Mark

 

[Norris68]

The usual way, or at least the way Microsoft does this sort of thing in exam questions is to create a table that relates the two. i.e.

Table manufacturer
ManfID, ident, PK
MName, text(50)

Table vendor
VendID, ident, PK
VName, text(50)

Table product
ProdID, ident, PK
PName, text(50)

Table manfvend
ManfID, long, PK, FK
VendID, long, PK, FK

Table manfprod
ManfID, long, PK, FK
ProdID, long, PK, FK

So, we have a table for each of: manufacturer, vendor and product; then a table manfvend which lists the relationship between manufacturers and vendors; and a table that relates products to manufacturers. In these join tables, the two Foreign Keys to the main tables are used as a composite Primary Key (this has the added advantage that a PK has an implicit unique key so you won't be able to add the same combination more than once)

 

[RickSpr]

Actually, this might be a little more flexible than you need. It depends on how you define a "product".

If a product is defined by a product ID that is unique to a particular manufacturer, the relationship is one (manufacturer) to many (products). In that case, you don't really need the manfprod table, and it would just get in your way. You should instead define a relationship between manufacturer and product.

On the other hand, if a product is defined by some generic identifier, such as "gauze bandage 4x4", so that it might be made by different manufacturers, the manfprod table is the right way to represent it.

To help you find the structure of your data, a good rule of thumb is to always look for one-to-one relationships first, then one-to-many relationships, and finally many-to-many relationships. One-to-one relationships almost always indicate that the two things that are related should be in the same table. One-to-many relationships indicate that you should make a parent table and a child table, and the child table's key is usually going to be the parent table's key plus an additional column.

When you get to the many-to-many relationships, first try to decide which ones really represent facts you're interested in, information that's actually important to you. (For example, an employee may work on many orders, and an order may be worked on by many employees, but if you don't need to track who works on each order, this many-to-many relationship is not important to you and you shouldn't represent it in the database.)

When you find a many-to-many relationship that is important, you need to create a table to represent this relationship itself. The key of this table consists of the combined key columns of the two tables it relates. If there is additional information about this relationship that you want to track, you'll have non-key columns for that. (For example, employees can work on many projects, and projects can be assigned to many employees, so you have a many-to-many relationship that you want to track. If all you need to know is who is working on which project, all you need in the table is the keys EmployeeID and ProjectID. But if you want to track how much time each employee spends on each project, you also need a non-key column to hold the hours worked.)

I hope that helps.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein