Help with table structure 3

[bernie10]

Hello everyone,
Suppose on a form I allow a user to pick a particular product type. After the user picks the product type a combo box is enabled which lists manufacturers who make that particular product type. The user then selects a manufacturer. A manufacturer can make many different product types. So the data looks something like this:

ProductType1:

Manufacturer1
Manufacturer2

Product Type 2:

Manufacturer1
Manufacturer3

'
'
'
(etc)

My question is how I should represent this information in a table.

Would it be like this?

Manufacturer ProductType
Manufacturer1 ProductType1
Manufacturer1 ProductType2
Manufacturer2 ProductType1
Manufacturer2 ProductType3
'
'
'
(etc)

In other words, should I list a new record for each product type each manufacturer produces? Just wanted to make sure I structure my table properly.

Thanks for the help,
Collen

 

[jrbarnett]

You want 3 tables:

ProductTypes
(ProductTypeID primary key, ProductTypeDescription)

Manufacturers (ManufacturerID primary key, ManufacturerName)

ManufacturerProducts (ProductTypeID, ManufacturerID) - composite primary key

This way, any manufacturer can have any combination of product types. Add in any extra fields to the tables you need to store information about product types, manufacturers or the combination of mf and product.

John

 

[lespaul]

read The Fundamentals of Relational Database Design for the explanation of what John is suggesting.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[johnherman]

If I had a dollar for every time lespaul suggested The Fundamentals of Relational Database Design I could take a month-long vacation. Star for lespaul and his patience.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[bernie10]

jrbarnett,
Thanks for the help with the table structure.

lespaul,
Thanks for the article reference. I had read it before, but had apparently forgotten a great deal of it.

Collen