DB design help for simple product and materials situation

Question

I currently have 3 simple tables: products (prodid, name and prodmatid), materials (matid, name) and prodmat (prodmatid, prodid and matid).

I am not sure if the above is a good design. Products can consist of different materials, the prodmat table only allows for one material at a time for a product - so if productA has 5 materials then it will require 5 rows in prodmat. Is this a silly way of doing it, how should I design this?

Answer 1

In your design, it looks like you have an extra column that is not needed in the products table. You can remove the prodmatid column since you can find that relationship in the prodmat table.

So your structure would be: products (prodid, name), materials (matid, name), and prodmat (prodmatid, prodid, matid)

This allows for a product 'My Product', materials 'Material 1' and 'Material 2', and the result would be:

product (1, 'My Product')
material (1, 'Material 1'), material (2, 'Material 2')
prodmat (1, 1, 1), prodmat (2, 1, 2)

Answer 2

This is called third normal form and is the accepted way to design a relational database.

See http://en.wikipedia.org/wiki/Third_normal_form

EDIT

Some people (including myself) would remove the prodmatid from the material table, as prodid and matid can be used as the primary key. Other people like to have 1 unique primary key for each table.

Answer 3

Assuming this is not homework... I'd do something like this:

Table: Products Prodid (PK), Name (I suppose some extra column would come handy... like... shelf-life? needs-special-storage?)

Table: Materials Matid (PK), Name, unit-of-measure (is it in grams, kilos, meters, items...) (Again, price? shelf-life?)

Table: ProdMat PK: Prodit+MAtid, Prodit (FK), Matid (FK), Quantity.

So, going back to your example, let's see product Gizmo:

Products Table
Prodid  | Name
  00203 | Gizmo

It is made of 5 different "materials":

Table: ProdMat 
Prodit | Matid | Qty
 00203 | 0034  | 2
 00203 | 0010  | 11
 00203 | 0567  | 1 
 00203 | 0002  | 1
 00203 | 0039  | 1.675

and these are:

Table: Materials
 Matid | Name                  | u-o-m 
 0034  | Cogs, brass, diam.2"  | Units
 0010  | Spring, small         | Units
 0567  | Gizmo shell (lower)   | Units 
 0002  | Gizmo shell (upper)   | Units
 0039  | Sand                  | Pounds

Now, if your problems includes sub-assemblies, or stuff that gets in as raw material and can undergo internal processing to become refined, or machined, or anyway transformed into one or more semi-processed materials/products than may become in turn parts of another Product... then things start getting interesting.