Implementing a child-parent hierarchical datamodel using double relationships

[#1]

Let me first explain you the details about my context.

The child-parent hierarchical structure in my database model concerns PARTS (mechanical structures, electrical assemblies, bits and pieces, etc.) identified with part numbers (unique alphanumeric sequence) and each PART can (or not) be made of sub-components which also are PARTS (and therefore parts also have parents). However, the same PART can be used in more than 1 assembly (i.e. can have completely different parents)

(just to give you a better idea visually speaking)

I am aware that this might not be the usual hierarchical scenario, and this could make things harder, but I do not really need to have a hierarchical tree-view or anything like that (unless it's possible of course), basically I just want to be able to know which parts are the children and parents of 1 PART (even if it's just using basic highlighted SELECT fields).

Using MS Access, I made a model that looks like this (if you have a better idea please let me know) :

As you can see in the 2 tables shown here, it's BOMHierarchy that hold which parts are made with which other parts.
The problem here is that within 1 table there are 2 foreign keys referencing the same primary key.
I am afraid that such a "double relationship" would force me to duplicate (or "fork") the Part table into Part_Child and Part_Parent, causing all sorts of other tables to be duplicated in the same way (Part is pretty much the central point of my model).
If you don't understand what I am talking about (it's mostly about avoiding loops), please see my other thread here : http://community.qlik.com/message/204778

If you need I can provide you with a full screenshot of my whole Access data model.

Thanks a lot for your time

[pover]

You should be able to use the Hierarchy() and HierarchyBelongsTo() functions without trouble.  See the attached example.

Karl

[#3]

Thanks for the document, it helped me understand how these functions work.
But it seems that it only works with Nodes that have 1 or 0 parent, and that the reference to the parent node is in the same table.

As you can see from the models in my original post, the nodes are stored in PART and the hierarchy (links between PART nodes) are held in BOMHIERARCHY.
I fail to see how I could use such Hierarchy() functions when there are 2 tables involved (Node table and Hierarchy table

[pover]

Dominique,

QlikView hierarchy functions work with multiple parents.  The example has a part called wheels that is found in skates and skateboards.  QlikView can sometimes use the same model you have designed for operational systems, but it is usually better to create a dimensional model in QlikView or a Star Schema.  In this case, you could have a parts catalog that is linked to a bridge table that is based on you BOMHierarchy table and helps link the parts catalog correctly to a fact table where you might have costs or quantity. 

I've included a slightly more complex example in the post.

[#5]

Hello Karl,

I have looked at your most recent hierarchy example and I have decided to duplicate my PART table into a PartCatalog, which would only contain the ID and the name, but the orignal PART  keeps all the data and facts. The "bridge" table would be my BOMHierarchy table indeed.

However, since I only have the Personal Desktop edition, I have very limited uses to open your documents, so I would greatly appreciate if you could paste the load script here (or any other piece of code deemed useful).

I am struggling to fully comprehend the Hierarchy functions, but I am using the Reference manual to help me.

Thank you

[pover]

Here's my script:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;-$#,##0.00';

SET TimeFormat='hh:mm:ss TT';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';

SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';

SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

//Source Data

Part:

LOAD * INLINE [

    PartNumber_Part, Name_Part

    1000, SkateBoard

    2000, Skates

    1100, Board

    1200, Shoe

    1110, Wheels

];

BOMHierarchy:

LOAD * INLINE [

    Child_Part_BOMHierarchy, Parent_Part_BOMHierarchy

    1000, 1000

    2000, 2000

    1100, 1000

    1200, 2000

    1110, 1100

    1110, 1200

];

Fact:

LOAD * INLINE [

    PartNumber_Part, Cost

    1100, 5

    1200, 2

    1110, .1

];

//Data Model

Hierarchy_Bridge:

HierarchyBelongsTo(PartNumber_Part,Parent_Part_BOMHierarchy,PartNumber_Drop,PartNumber_Ancestor, Ancestor_Drop)

Load Child_Part_BOMHierarchy as PartNumber_Part,

          Child_Part_BOMHierarchy as PartNumber_Drop,

          Parent_Part_BOMHierarchy

Resident BOMHierarchy;

Part_Catalog:

Load PartNumber_Part as PartNumber_Ancestor,

           Name_Part

Resident Part;

Drop table BOMHierarchy, Part;

Drop fields PartNumber_Drop, Ancestor_Drop from Hierarchy_Bridge;