>>>Hand crafted parts: Lamps where the base has the cabels, the base, the socket, the uper part of the lamp, and one lamp can be paintet with diferent colors and then it is has a different part number.
>>
>>In a company I worked, it was possible for one product to contain another, "intermediate", product. This could go through several levels of intermediate products. My system allowed this, and if you think you would need this (it can be quite useful, I believe), I can explain the basic structure for the materials (materia prima), the intermediate articles and the finished articles.
>
>Hello Hilmar,
>Yes please, it can be of great help to me
>
>Thanks in advance

OK, here goes the basic description of the materials, and your manufactured products (both intermediate and final).

Problem: Somewhere, sooner or later, you will need a relation between a product, and the material it contains. Let's say that the manufactured product "parent" contains a material "child".

Now, if you put materials and intermediate products into separate tables, you will have trouble with the "child" part, since it is in different tables.

On the other hand, if you put intermediate products and final products into different tables, you will have problems in the relation, with the "parent" part.

The end-result is that everything from materials (materia prima) to the finished product must go into a single table!

To resolve the many-to-many relation for the containership ("parent" contains "child"), you need a separate table.

I will show you, then, the two main tables then, and some relevant fields. I am assuming you want primary keys which the end-user doesn't see - adapt if you prefer otherwise. The following structure has worked well in practice:

Table ArticleMain
* contains definitions of materials, intermediate products,
* and finished products (the ones you sell)

* Relevant fields:
Article I: PK
ArtCode C(10): The code which the user sees (adapt the size to your needs)
ArtName C(30): The description which the user sees
ArtLevel C(1): "M" for material, "I" for intermediate, "F" for finished article
CostPrice Y: assigned by the user for ArtLevel = "M". Calculated for ArtLevel $ "IF".
SalesPrice Y: Assigned by the user for ArtLevel = "F".
Unit: meter, liter, kg., etc., or simply "unit" (FK to the units table)


* Table ArticleComposition (which article contains which other article)
* "parent" contains "child"
ArtComp I: PK (Optional)
Parent I: FK to an ArticleMain (ArtLevel $ "FI")
Child I: FK to an ArticleMain (ArtLevel $ "IM")
Quantity Y: How many unit of "child" do you need for each "parent"

Well, that is it, more or less. You can add additional fields as you need them.

Once you have data in table ArticleComposition, you can answer the following questions:

What materials go into article x? (select * from ArticleComposition for parent = x)

In which articles is material y used? (select * from ArticleComposition for child = y).

When the user enters data for ArticleComposition, you have to be careful to avoid loops, that is, an article that contains itself, directly or indirectly. For instance:

A contains A

A contains B, B contains A

A contains B, B contains C, C contains A

etc.

I hope this is useful for you, and I am available for further clarifications.

Saludos,

Hilmar.

Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)