3 Replies Latest reply: Aug 25, 2016 9:15 AM by Marcus Sommer RSS

    Data model design

    Mark Perrone

      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.