Circular Reference and Composite Keys

[ZainZaidi]

I have a problematic data model. There is a circular reference present by default.

                                 

I have considered making a bridge table but then all tables will be linked to bridge table using a 3 key composite key. However, the tables are connected with each other at 2 key composite keys. Another problem is of granularity. Table1 has data at the highest level of granularity (Item). Table2 has a lower granularity level (Item - Carton). And Table3 has an even lower granularity (Carton-Container).

What is the correct method for handling this situation? Generic keys? I'm stumped. 

[Vegar]

It's hard to give an absolute answer not knowing your data, but I'll give a suggestion.  You say that table 1 has the highest granularity and therefore I would consider removing the A_D_Key from table 3, the lowest granularity table. Hopefully you will still have access to all the correct A_D_Key values.

[Vegar]

Another solution if you have a hierarchy relationship between the three tables is to remove the A_C_Key in the Table 1.

Good luck with your data modelling. 

Vegar

[ZainZaidi]

Hi Vegar, I really appreciate the quick response.

Like you said, i've removed the A_D_Key but i'm worried whether this is logically correct or not.

I don't know if this will result in lost data during association.

The relationship is not hierarchical so i cannot remove A_C_Key.

Thanks for the suggestion though.

[Vegar]

A simple check you can do is to rename the A_D_Key instead of removing it. Then you can create a validation object in QlikView comparing the output of A_D_Key and your renamed version of A_D_Key. It will give you an indication of the quality of your new data model. 

[marcus_sommer · Accepted solution]

Personally I wouldn't tend to link all of these tables else I would try to merge them. It may be more or less simple joins/mappings depending on the relation of their keys and/or how to handle missing keys (maybe checking in beforehand and adding them before the join/mapping).

Logically more easier would be just to concatenate all these tables. Non-synchron tables and/or tables with a different granularity work often quite well in Qlik - therefore just give it a try.

- Marcus