>Despite somewhere is some truth in Hilmar's reply about the two tables, his solution can (and should ;) be approached differently, but which is not important to the problem. But anyway : whether you buy a nail or produce one, it's just another Item in the Item file.
>The composites are another story, which can (no, should) just be held in one separate table, and the PK always consists of Parent and Child (in fact it's a relator). Never mind this too, because it isn't your problem.

If I understand correctly, you suggest keeping raw material in one table, and products made by my company in another one?

I thought about this approach too, at first, but what makes this complicated is the relationship between the "composition" table (what article contains what item). How do you relate one table to two alternate tables? (that is, "Composition" table states that a product made in my company contains either a piece of raw material, or an intermediate product). How would you manage RI in this case?

My solution (BTW, it works in practice - we are using it for 2 years now) has wasted space (fields that only apply to raw material, or only to finished products), but I don't find this to be a problem in practice.


>Now just think of printing the full expanded bill of materials, for which you will need a recursive function indeed; the print will start with the main Item (may be the car, or may be the motor, just where you like it to begin), and the explosion of the first main part will follow.
>Your recursive function will stop when all the branches of this main branche are finished printing, and the next branche will be taken. In fact not difficult at all when you have some experience on recursive programming. BTW, I can predict already that someone will come up with some super-smart three line SQL commands for it, which doesn't look recursive at all, but in fact is in the inside. But never mind this again.

For printing, nobody here ever asked for the "expanded" bill of materials (in form of a treeview), but the requirements at other companies might be different. What we do is a) print one level at a time, b) use a recursive function to calculate all materials required, but only show the last level (raw materials).

Hilmar.