Data model design

[markp201]

We have a requirement in our department on what constitutes good data design.  I wanted to get some thoughts on one of the required concepts having to do with bridge tables.  For example,

A child table has 1,000,000 rows.  Each child has exactly one parent but the parent can have many children - classic 1:M.  The normal procedure is add ParentID to connect with the parent table but this we're told violates good data modeling.  Because the child has  data not purely about the child (e.g. product group is related to the product but does describe the product) we're required build a bridge with two fields: ChildID, ParentID.  Since there are 1,000,000 child rows, there will be 1,000,000 bridge rows.  This would be great if the performance improved but seriously, 1,000,000 x 2 is not going degrade performance?

For me, this is akin to applying normal forms 4 and 5 which even DBA's say creates too many joins and affects performance.

BTW, the ID fields are Int32.  Hey, at least there not string.

[marcus_sommer]

In my openion it's not very helpful to think in normal forms and classical database-structures if you develop datamodels with qlik than qlik worked different and you are developing rather in the opposite direction especially if you handle larger datasets - but there is no general recommendation, it will always depend on the data and the insights which are required which way is more suitable as others. Regarding to your hierarchy-topic might be this useful: Unbalanced, n-level hierarchies.

- Marcus

[markp201]

As always HIC never fails to share his knowledge but this is the most simple form - a 1-level balanced hierarchy.

[marcus_sommer]

Maybe this could give you an input to extend your hierarchy-task:

Bill of Materials

Hierarchy and Hierarchy Belongs to

- Marcus