1 Reply Latest reply: Jun 18, 2014 8:21 AM by Carl Von Stetten RSS

    Database Design Question: What are the consequences of using a "One Table to Rule Them All" Approach?

    Henweigh99 Community Member
    Henweigh99 Jun 17, 2014 6:14 PM

      I have a MSSQL database schema question that I’d love to have the community's opinion on.  I’m looking for the optimal way of storing one-to-many relationships for a variety of table combinations.

       

      I’m familiar with the basic idea of relationship tables  (the green table links Table1 to Table2):

       

      Table1          Table2          Table1Table2

      Table1ID        Table2ID      Table1Table2ID

                                                  Table1ID

                                                  Table2ID

       

      What I’m trying to do though is to be able to link multiple tables together using a single relationship table.  I’m using my server-side code to specify which tables are being linked (green table is relationship table and the red column indicates which table is being linked to Table1):

       

      Table1                Table2                Table3               Table1TableX

      Table1ID            Table2ID            Table3ID             Table1TableXID

                                                                                        Table1ID

                                                                                        TableXID

                                                                                        TableX

       

      In the above example, I’m using the column “TableX” to define which tables (Table2 or Table3) is being joined to Table1.  This brings me to my first question:  What are the consequences of doing things this way?  Am I going to encounter a serious performance hit when records get into the 1000s+ ?  I realize that I’ll have to enforce table integrity on the application layer and I’m okay with that aspect.  I've been developing my application like this for quite a while now and so far it has worked out pretty well in keeping my database structure simplified.

       

      Now I’m looking to simplify things even further and create a “one table to rule them all” approach.  Basically, I’d like to create 1 relationship table that would control the relationships between all of my other tables.  In this method I specify in the column values which tables are being connected.  For example: (green table below is the relationship table and red tables point to the tables that are linked)

       

      Table1                Table2                Table3                Table4               Relationships

      Table1ID             Table2ID             Table3ID           Table4ID             RelationshipsID

                                                                                                                     ParentTable

                                                                                                                     ChildTable

                                                                                                                     ParentID

                                                                                                                     ChildID

       

      Am I totally crazy here? Or would a setup like this be a viable solution for linking multiple tables together in a single place?  So far the only consequences that I see are that my SELECT queries are a little more complex because I have to specify which tables are linked. For example, a simple joined query which looked like this:

       

      SELECT *

      FROM Table1 t1 INNER JOIN

                      Table1Table2 tt ON t1.Table1ID = tt.Table1ID INNER JOIN

                                      Table2 t2 ON tt.Table2ID = t2.Table2ID

       

      Now becomes:

       

      SELECT *

      FROM Table1 t1 INNER JOIN

                      (SELECT ParentID, ChildID

                      FROM Relationships

                      WHERE ParentTable = ‘Table1’

                      AND ChildTable = ‘Table2’

      ) tt ON t1.ParentID = t1.Table1ID INNER JOIN

                      Table2 t2 ON tt.ChildID = t2.Table2ID

       

      So aside from having my queries be a little more complex, the database structure looks a lot cleaner when it’s all diagramed out.  Do you think this is a bad idea to develop a database this way? If so, why?

       

      Thanks for taking the time to check this out… I’d be curious to know your thoughts.

        • 1. Re: Database Design Question: What are the consequences of using a "One Table to Rule Them All" Approach?
          Carl Von Stetten MeganK
          Carl Von Stetten Jun 18, 2014 8:21 AM (in response to Henweigh99)

          @Henweigh99,

           

          I can't quite put my finger on why, but I get a little bit of "code smell" from this approach.  Maybe it's because using a global join table for all relationships is counter to every app and example I've ever seen.  I'm also wondering if some many-to-many relationships might be made more confusing (which is the parent and which is the child might not be clear cut), and keeping consistent throughout your application might be a challenge.

           

          I can see wanting to remove some of the "noise" of all the individual join tables typically found in databases.  Personally, however, I prefer to let the database do as much of the "heavy lifting" as possible, including managing the referential integrity.

           

          Are all of your relationships many-to-many?  If some are one-to-many, you can use a foreign-key column in the child tables that stores the primary-key of the parent table - and the database can still manage referential integrity.  That may reduce the need for some of the join tables.

           

          Regardless, if you decide to proceed with the global join table you need to make absolutely sure your indexes on that table are tuned properly or you'll likely take a performance hit.

           

          -Carl V.