I have theoretical question about SQL design. When I have 10s of tables, between which I need relations m:n, is better approach to do relation table for each required pair or is it possible (or from performance view better) to have one relation table with columns (id,table1,row1,table2,row2), with integers in it?

CodePudding user response：

I am an Informatics student and based on what I have learned in class it is always better to create a table in between the two tables in order for it to hold the primary keys of each of those two tables. This table will hold the relations like the following:

student: student_id, first_name, last_name

classes: class_id, name, teacher_id

student_classes: class_id, student_id     # the relations table

CodePudding user response：

Of course it would be better to have a more detailed example, at least for a smaller scope, which are tables, why do you need that relation etc.

But generally, in my opinion, database design should follow logic of your data, so relations should be where they are needed.

Other thing, if you will have only one table for all relations, it will be kind of bottle neck for the whole application. So then, how critical this situation is, also depends, on how are you going to use it in application (mostly reads, or lots of writes/updates/deletes, how many rows.. etc).

Also this consideration also could be somewhat even dependent on what RDBMS you're using.

CodePudding user response：

Depends on the case, sometimes it's better to follow normal forms for designing (https://www.sqlshack.com/what-is-database-normalization-in-sql-server/), but after years working in software... from experience that is lost with time, the original design gets messy unless there is a team for that.