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.
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.