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

Report · Jun 17, 2014

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.

Report · Jun 18, 2014

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