>>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?

Oh ... I thought that was your suggestion ? okay, let's bake some cakes here ;)
No, I just suggested that all products are in the one Item table becausse all products are Items (Articles), so no matter whether they are raw, intermediate or end-products. Just look at it : we can resell a raw material, can't we ? or we could by a product we normally produce ... (etc.)

So the comosite table just holds relations from parents to childs, and in fact relates in both cases to the Item.

Now please note this is a little too theoretical, because whether you'd just have a BOM or a Recipe (Formula), in both cases it needs "headers" as well. Thus, the Header-table for the Recipe contains f.e. the amount of cake (KG) the Recipe will be based upon, hence how the quantities of the "raw materials" (could be Intermediates) relate to the quantity (to be) produced. Note that this is necessary when looking at fixed (-time) work like stirring, assuming that "work" is also in the Recipe.

So yes, one table for the composite relations is sufficient in theory, but in practice it always brings two.


>
>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?

Small misunderstanding, see above.

>
>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.

I'm not sure to which table you refer now, but is should be the Item table you are referring to. I mean, "my" relator (the composites table) only contains references to Item Numbers (for parent and child), and quantities and further stuff (normalized) always needed. The Item table however, yes, that one isn't normalized if it 'd contain all attributes for all types of Items (raw, intermediate, end). Thus, the fact that all products are in the Item table, doesn't say that there may be three other tables with attributes per specific type. But, has it to do with the problem ?
One could say Yes, but then you'd be thinking that (f.e.) an end product never can be part in another assembly, and (IMHO) it just always can. F.e. even a car could be part of an assembly later on, whether it was meant for this or not in the beginning. Okay, I don't know what kind of product that would be (a two-tranction vehicle or so), but it wouldn't be allowed to prevent the user from defining it once it is at order.


>
>
>>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).

We have both forms, and mixed too. So 1. the multi-level print, 2. the single-level print, 3. the "flat" print with all (due to what the (end) product needs indeed), and 4. ... many deviations of it. Think of a "demand run", matching (promised) stock to needs (demands), which is a rather complex thing (but usual in ERP).

>
>Hilmar.

Just for fun : look at UT's threads; they are exactly about this subject ... and it's a treeview too. The Thread is the main assembly, and each branche is a part, which -like this one- is a raw material. Until you reply to it ... Then it's Intermediate ...