Relations via magic-table

Question

I encountered an interesting SQL table (column names listed):
From_TableName | From_Id | To_TableName | To_Id

From what I understood, this table is used for all table relationships in the database. There's not a lot of data (more than 10000 lines in the previously mentioned table) in the database, so, I guess, performance was not that important. I'm not a SQL expert, so I decided to ask here: should I send this to DailyWTF or is this a viable solution in some situations?

My thoughts: I just don't see any upsides with this approach. Sure, you only need one table for all your relationships, but that's hardly a plus. And I'm not sure if there's really less work, when you create a new table. Unless you want to let your users do anything at all, there must be some kind of validation logic (what kind of relationships are valid) somewhere and you need to update that.

Answer 1

This was most probably used in mapping Entity-like objects in the application layer (hopefully) and not in the sql database itself.

And even though it is not recomended, I have seen these approaches before.

This can lead to some serious performance issues, but as you said, the database was quite small.

I do not think that this fully warants a DailyWTF though.

Answer 2

I've seen a design like that in a custom ORM.

This makes sense if:

• "Table name" is read as "class name"
• All class instances are stored in a separate table
• Actual tables contain the class properties

Answer 3

I worked on a system like this once where the users could add new tables and relationships at any time, without developer involvement.

Answer 4

The approach of using separate tables to contain relational information is not all that uncommon. Many frameworks (Rails, for example) use them to store many-to-many relationships. In these cases, however, it's normally one table per relationship, with only the IDs of both in the table.

Having said that, I haven't seen this approach before, where instead of having separate tables for each relationship, all relationships are collected in one. The only real downside to this approach compared to the approach I just described would be performance. The upside would be (for some, anyway) less clutter in the database by having less tables.

But no, definitely not DailyWTF-worthy. I'd question the design but not ridicule it.