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
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
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
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:
FROM Table1 t1 INNER JOIN
Table1Table2 tt ON t1.Table1ID = tt.Table1ID INNER JOIN
Table2 t2 ON tt.Table2ID = t2.Table2ID
FROM Table1 t1 INNER JOIN
(SELECT ParentID, ChildID
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.