Prerequisites
For some parts of this article it is convenient to understand the basics of recursion. You can read my introductory article on this topic Universal Thread Magazine, January 2003.
Single level
First, let us start with a single level of containment, just to get an overview. That is, a finished article only contains raw materials; no intermediate products yet.
Terminology
Here is the basic terminology as used in this article:
Requirements
The requirements of our system, then, or of the part that concerns us here, are to have a list of materials including current prices, and a list of finished articles our company produces. Also, we need to record what materials make up each finished article (what material, in what quantity), so that we can calculate what it costs us to produce the finished article. (At least, the part of the cost related to raw materials; things like wages, social benefits, machine depreciation, taxes, etc., can introduce additional complications, which go beyond the scope of this article.)
Data structure
The data structures are actually quite simple. For a start, we need one table for the material and one for the finished article.
Note 1: A more logical name for the article table would be "Article", but then the referential integrity may fail if there is another table that starts with the same name, like "ArticleMaterial" which we will use later. This is due to a bug in the Referential Integrity code.
Note 2: For the primary fields, I am assuming some sort of auto-generated value, for instance, an Integer with a default value of SerialNumber("TableName"); where the function SerialNumber() fetches the next value from a table of sequences. Other alternatives include functions that generate some pseudo-random value, such as a GUID, or the VFP function sys(2015). If you prefer to use the product code itself for the primary key, that is of course also possible, but a few adjustments are required.
Field structure
Note that this section is for the single-level system. The multi-level system is explained later.
MaterialList - The list of raw materials:
ArticleList - The list of finished articles:
ArticleMaterial - Materials required for each article:
Multilevel - advantages
Now, before we start exploring the structures for a multilevel production system (i.e., one that includes intermediate products), let's take a look at some of the advantages.
The idea is that some finished articles contain intermediate products, which in turn contain raw materials. For example, a finished shoe will be sold in a box; the box is result of another manufacturing process, and might require, as raw materials, cardboard, a sticker, and glue, plus some work input. (Note that work is not considered in detail in this article.) Other example of intermediate articles (specifically for the case of a shoe production) include leather, soles, and diverse mixtures of chemicals - assuming, in all of these cases, that the shoe factory does all of these steps itself, for instance, that it does its own tannery (leather processing).
The big advantage of the intermediate article is that all the parts required for the intermediate article need to be listed only once, in the finished article. Equally important, any change in the definition of the intermediate article needs to be done only once.
Of course, this convenience comes at a price; as in many other cases, making things easier for the end-user means more work for the programmer. But it is worthwhile to consider this, at least for larger production systems: At our company, where we use intermediate articles, a typical list of materials for a shoe fits on a single page; I heard about another shoe factory, whose computer system doesn't support intermediate articles, where a typical list of materials, for an article, spans 3-4 pages, because it has to list all the materials for what we consider "intermediate articles", directly in the listing of the finished article.
Multilevel table structures
Consider the required relations, considering that an intermediate article can play both the role of an article (something that contains), and of a material (something that is contained).
A finished article can contain both raw materials, and intermediate articles. The relations between tables are quite complicated if raw materials and intermediate articles are in separate tables, therefore, they must be stored together, in the same table.
Also, a raw material may be contained either in a finished article, or in an intermediate article. Therefore, the finished articles and the intermediate articles must also be stored in the same table, to simplify relations.
The conclusion seems inescapable: All steps in the production process, from raw materials, passing through possibly several levels of intermediate articles, up to the finished article, must be stored in a single table. At least, it seems to me that this is the only sensible way to structure this particular problem.
The tables required, then, are two:
ArticleList - now contains finished articles, raw material, and intermediate articles:
ArticleMaterial - materials contained in each article. Every finished article or intermediate article contains several intermediate articles or raw materials:
Additional fields may be required, depending on the needs; in the case of table ArticleList, some of these may only be appropriate for finished articles, or for materials, for example. You may want to put some of these into separate tables, to save space (with a one-to-one relationship); my personal preference is to keep them in a single table, for simplicity.
Some selected procedures
Now that we have the data structures, let me briefly outline some of the procedures you need to carry out with this multilevel structure. I will not give any source code here, just the basic idea.
First of all, since all the "articles" are now on the same level (with respect to the data structures, at least), you have to ensure that an article doesn't contain itself – either directly or indirectly. That is, if article "A" contains article "A", there is a problem, but there is also a problem if article "A" contains article "B", and article "B", in turn, contains article "A". (Specifically, this "circular reference" will cause great problems when you calculate the cost of an article, or when you get the list of materials required for one or more articles.) This requires some sort of recursive procedure. The check is best done as part of the data validation, when the user changes data; for greater safety, you should also have a report that lists invalid articles, i.e., articles that contain themselves.
Another procedure you will probably need is to get the list of materials required for a single article, or for a list of articles. (In our system, we get this from the tables for production planning.) This, too, is some sort of recursive procedure; I suggest a generic procedure that can be used for many different reports. The procedure might take, as its "input", a cursor with a list of articles and their corresponding quantities (plus any additional data of interest, for example, for what date the production is planned, in case you want data for a range of dates). That is, the "input" cursor is prepared beforehand, and then the procedure is invoked. The result of running this procedure might be another cursor, with the detailed information on materials and quantities. Since this is meant to be a generic procedure, you may want to include: For what article the material is used; what material is used; in what quantity; and for what date of production (in case you include this information). You may also want to include information about how many levels are required to get from the finished article to the material. This information can then be grouped as needed for specific reports (for instance, in some reports you will only want information about the materials required, and not, for what articles the materials are used for). The recursion basically works like this: For each finished article, put all required materials into the result cursor. If one of the resulting materials is an intermediate article, repeat the procedure for this intermediate article. The result cursor will contain all the intermediate articles and the raw materials. I suggest to also include the original article that is being planned.
Yet another procedure that is required is to recalculate the cost of an article, or the cost of all articles. I do not recommend recursion in this case, since it would be very inefficient: to calculate a finished article you would first have to recalculate all of its intermediate articles. To recalculate all finished articles, a recursive procedure would recalculate the intermediate articles many times (once for each of the articles where it is used).
Instead, I did the following, to recalculate all articles: Replace the price of all articles (not including raw materials) with NULL. Then, loop through all the articles, and recalculate them, by adding the prices of materials that make up the article. If an article happens to have intermediate prices with a NULL price (i.e., the intermediate article was not yet calculated), interrupt the cost calculation for this specific article, leaving the prices at NULL. Another alternative is to store a separate cursor of articles to be processed, and eliminate the articles that have been recalculated.
After going through all articles in this manner, repeat from the beginning, perhaps several times, until all articles have been calculated.
Summary
Managing a multi-level production and cost-calculation system is quite simple, in principle, but of course it will take some time to fine-tune all the little details.
