Are relationship tables really needed?

Relationship tables mostly contain two columns: IDTABLE1, and IDTABLE2.

Only thing that seems to change between relationship tables is the names of those two columns, and table name.

Would it be better if we create one table Relationships and in this table we place 3 columns:

TABLE_NAME, IDTABLE1, IDTABLE2, and then use this table for all relationships?

Is this a good/acceptable solution in web/desktop application development? What would be downside of this?

Note:

Thank you all for feedback. I appreciate it.

But, I think you are taking it a bit too far... Every solution works until one point.

As data storage simple t开发者_JAVA技巧ext file is good till certain point, than excel is better, than MS Access, than SQL Server, than...

To be honest, I haven't seen any argument that states why this solution is bad for small projects (with DB size of few GB).

---

It would be a monster of a table; it would also be cumbersome. Performance-wise, such a table would not be a great idea. Also, foreign keys are impossible to add to such a table. I really can't see a lot of advantages to such a solution.

---

Bad idea.

How would you enforce the foreign keys if IDTABLE1 could contain ids from any table at all?

To achieve acceptable performance on joins without a load of unnecessary IO to bring in completely unrelated rows you would need a composite index with leading column TABLE_NAME that basically ends up partitioning the table into sections anyway.

Obviously even with this pseudo partitioning going on you would still be wasting a lot of space in the table/indexes just repeating the table name for each row.

---

Isn't it a big IF that you're only going to store the 2 ID fields? If I have a StudentCourse (or better yet Enrollment) table that has StudentID & CourseID, but wouldn't EnrollmentDate go in this table as well since not all students enroll on the first day of class. Seems like a bad idea to add this column to an already bloated table where most records will be null.

The benefit of a single table could be a requirement that the application has the ability to allow user/admin to create these relationships with data (Similar to have a single lookup or reference list table) and avoid having to create a new table to address these User Created References. Needing dynamic querying may benefit as well. An application that requires such dynamic data structure requirements might be better suited for a schemaless or nosql database.