Products with options

Hi all!

I am designing a DB for a company that sells industrial equipment.

The problem is that those equipments are quite complex and can be quite customised. Up to now I have the following tables:

EQUIPMENT: holding technical data
PART: where all parts and assemblies are
ASSEMBLY: linking rows in PART to build assemblies. An ASSEMBLY can be made up of parts and assemblies.

Every row in EQUIPMENT is linked to one or more rows in PART
Rows in PART can occur in many rows in EQUIPMENT

-First problem is that many assemblies might have options (an option can be an assembly or a single part) and that sometimes there are alternatives for an option. For example: the assembly 'Engine' (made up many parts and assemblies) might have an 'Engine Starter' which is EITHER electrical or pneumatical. The 'Electrical engine starter' is in turn made up parts and assemblies.

-Second problem: I should keep track of the equipment sold by the employees of my company. Not only which equipment was sold, but also which options. The configuration of the assemblies changes relatively seldom. But it changes!

It would be great if someone could assist me with these issues.
Thank you very much for your comments!

You have a many to many relation between EQUIPMENT and PARTS so create a 3rd table with PK of both the parent tables with other necessary information in it. That will allow you to have 1 EQU with many parts and 1 PART in many EQU. This is very common practice in DB design. Search on net for many to many relations for more info.

Database Systems: Design, Implementation & Management

Beginning Relational Data Modeling

Thank you guys for your replies.
Yes, I know about many to many relationships.
It's more complicated than that and that's why I'm posting here.

The problem is not the relationship between PART and EQUIPMENT but rather the hierarchical relationship existing among PARTs...which are resolved in the table ASSEMBLY. The problem is that some of these relationships among records in PART are optional and for some of them there are like alternatives: Part #1 needs either Part#2 or part #3...part#1 MAY also need Part #4...this kind of relationship is the one I am having trouble with.

Could anyone please help me further with this?

Thank you very much!

This sounds like you need a self referencing table set.

A master record describes the hierarchy of an entity (part / assembly / thing / etc) it is keyed on a part number. It has a child table which list the part numbers of all the entities which make up the owner.

This a single HUGE THING will have a number of child records pointing to Big Things. Each Big Thing record will have a number of child records pointing to Medium Things. And so on.

Don't try to store all of the part details in this table set. This is for the hierarchy alone. Have each master record hold the record type (or table ID) and primary key of the item decription record.

Thank you wommit for your time.
I understand what you explained. But my problem are all those parts which are optional (i.e. you can take them or leave them) and those for which you have alternatives. For example an engine starter. For the same engine I have two possibilities: elect. or pneumatic.

Do you have any suggestions on how to model something like that?
Thank you once again!

The addition of optional entities is a little more complexe. One option :D is to set up a record set in the self ref table for all possible combinations. This does not mean EVERY possible combination just all legal / acceptable combinations. Thus the record set that you SELECT shrinks with every chosen option until there is just a single record (single configuration) left.

A second method would be to add a third table. This table (call it Options) will act in a similar manner to the Children table. It will hold the parent (Master) primary key and a list of optional entities for that parent.

Thus you can maintain your hierarchy of components, have self contained (which have no parent) entities, have entities which are children of other entites and have entities which are options for the parent. Each of these entities can have children, and each of the children can have children and options.

I hope that I haven't caused confusion instead of enlightenment. :)

Wommit

Hi wommit,

thank you for your reply. And yes, I understand what you wrote (I hope so)
I still have some questions regarding this model:

-You said I shouldn't store all details in the two tables used to keep the hierarchies (tables PART and ASSEMBLY).
Should I create one table for each part type I have? Table PART is containing ALL the parts in my system, if I need specific details for pumps, should I then create a table called PUMP for those details? (in this case I'll end up having some 60-70 tables for the different part types)
-Besides storing which parts our products are composed of, I need to store orders too. What I can't understand well is which order details I need to store. I mean, obviously I need to keep the products sold with the order, but should I store the hierarchy along with them?

Thank you for your help!

This question is now reaching the stage where further explaination would be wasted. I feel that this task should be given to an experienced professional who could guide you through the design phase and help increase your knowledge and understanding of database design. This option will obviously cost it would provide you with a database solution that will work. It will be cheaper to do this now rather than later in the project when time and effort has already been expended.

Wommit

Thanks wommit