Hi all,

I've been trying for a while to understand how the nested sets model could work for my problem...and I am in need of some help to accomplish it!

-The product I am dealing with are gensets (bigger ones). One must pick an engine and a lot of parts should be delivered with it.

There are about 20 engines.
Many parts are used for all of them (that means the same part will be delivered with many engines).

To define the rest I must know how much power the engine will deliver.

Before one can know how much power the engine will deliver, you need to know many other parameters. Like: fuel type, emissions level, rpms, etc

That results in about 250 possible combinations.

Since I don't want to define ALL the parts I need for every one of my 250 combinations (because many of the parts are valid for more than one combination), I am trying to do the following:


Root node: Engine type

Under Engine type: C100;C200;C300

Under C100: Nat. Gas; Biogas; coal mine gas

under Nat. Gas: NOX 500; NOX 750

under NOX 500: 750 rpms

and so on...

Then there will be a Parts table containing all parts used for all combinations.

A third part would link every node with the parts table. Like:

C100--->Part #2
C100--->Part #67
C100--->Part #54

That means that parts #2,#67 and #54 are common for all C100's


Can aynone tell whether this could work? Am I far away from the truth?

Thank you very much for your comments!

you're not far away,

You would need a series of tables for the parts and options and then one table that linked the IDs in the engines table to the possible options/configurations and run JOIN queries to pull the required parts.

Thank you Chris for your reply!
I don't know if I understood what you mean...

Please correct me if I am wrong:

-I would need three tables: one table ENGINE, the second one will be
PART and a third, OPTION, will associate ENGINE with PART.

-to get to the power output of my engine I must traverse a serie
of nodes (in the table ENGINE)

Like: Engine Type/C100/Biogas/NOX 500/750 rpm

When I get to '750 rpm' I know the engine and the power
the engine will deliver.

On the way there I must look for parts associated to each node.

For example, for the node 'Engine type' I must go to table
PART and pull all parts associated to that node in table
OPTION (these parts would be those used in all engines)

Then I move to node 'C100' and get all parts from PART associated
with node 'C100' in the table OPTION (these are parts used only
for the engine serie 'C100') And so on...

The further I move along the nodes path , the more specialised
the parts are.
------------------------

I appreciate your comments very much.

Thank you!

I would go further down the normalised tables route and have a lot more than 3 tables.

I tend to look at DB design in reverse of what you are doing and start at the least specific (many to many relations)

but it sounds that you are on the right course

Thanks Chris,

sorry for insisting, but I am still a bit lost.

-------------------------------------
-You said you would have a lot more tables: for what
reason would you have more tables?

up to now I see the need for 3 tables:
--------
ENGINE: to store the engine options using nested sets.

For example informartion like
Engine Type/C100/Biogas/NOX 500/750 rpm
When I reach a leaf I get an engine and the power output
for the engine.
---------
PART
Holding information about all parts (yes, i will definitely have
some subtypes here later)
---------
A third table called OPTION
Used to represent the many to many relationship existing between
ENGINE and PART

Parts which are used for many engines will be linked to a node in the
table ENGINE (not to a leaf)
--------------------------------------

Thank you for your patience!

I think Chris is suggesting you create your hierarchy of nodes as a hierarchy of tables instead of using nested sets. If the hierarchy is more or less fixed then this makes sense (ie you are only going to look up based on parameters, not add new types of part (at least not very often)).

Nested sets would be better if your hierarchy is going to change regularly.