Design problem - a table that is not always present (1 Viewer)

I have the following table structure:



As the picture shows, a Product consists of a number of Components and each Component is going through a number of Processes.

Here is my problem: there are some Products that consist of no Components, but the Product itself is going through a number of Processes (these Processes are the same as those Components do go through).

What do you think is the best way to alter the above table structure as it does not apply to the case where the Product has no Components?

No messing with the structure necessary. Make every product have a component record.

plog said:

No messing with the structure necessary. Make every product have a component record.

Click to expand...

If I follow this option then this component record will be imaginary and the Component table will have two groups of records (real components and imaginary components).

I am afraid this proposal will create complexity in separation of the 2 groups of components in every query that the Component table is involved.

There must be a simpler solution with no duality issues for a single table - at least I hope some forum member to propose one.

Two alternatives

(1) add a foreign key to the Product table in Process table.
(2) add an extra table which includes foreign keys to Process, and to both Product and Component - one of the latter two would be null depending on whether the join was to Product or Component.

I'm inclined to go Plog's method with an additional field in Components to indicate its a 'virtual' entry.

@ OP,

You may want to look at a self referencing file.
From your description, the only way this makes any sense is if all components are serialized and tracked by serial number. This means you don't have "Products", just components. You also have a linking file that says "Child Record belongs to Parent" that links components to each other.

This also allows for dealing with sub components and component assembly.

Example from the auto industry.
Pistons can be made with a serial number. They are components.
An engine can be made with a serial number. It is a component. Is is made of components.
Install the engine onto a frame and it that frame now not only links to the engine, it can link through the engine to the pistons.

I'd say remove the "Product" table entirely and simply have some field that identifies if the component is also a product.