What is the best way to implement relationships in a database?

Question

SQL

There is a small project with a set of tables (Users, products, etc.). Some of them have a many-to-many relationship. In this regard, a connection model with fields is assumed: id_of the first_entity, id_of the second_entity, but since there are many relations themselves, it turns out that there are several such intermediate tables.
Is it possible to implement one large table with all connections (because they do not differ in the set of fields), by adding the field type of connection, and selecting specific records in the project already by this type? Or is it better to have more but smaller tables?

Answer 1

If you make the identifiers of each object unique within your database, then you can get by with one table of relationships and you will not need to additionally specify the type of relationship in JOIN.
For example: Objects of type A have type identifiers A1, objects of type B have B1, and so on. Then:
Table A
A1 Data 1
A2 Data 2
A3 Data 3
Table B
B1 Data 1
B2 Data 2
B3 Data 3
Table C
C1 Data 1
C2 Data 2
C3 Data 3
LNK Link table
A1 B1
A1 B3
C3 B2
and so
on Link request A and B (roughly without parsing):
SELECT * FROM A JOIN LNK ON A.id = LNK.id1 JOIN B ON LNK.id2 =
B.id this is due to: either duplicate when adding, or use other approaches, or maybe you just need the direction of the links.
You can use a GUID for identifiers - it is guaranteed not to overlap between tables, but in order to determine the type of an object by GUID you need to go through all the tables.

Answer 2

Is it possible to implement one large table with all connections (because they do not differ in the set of fields), by adding the field type of connection, and selecting specific records in the project already by this type?

It is possible, but you cannot build a foreign key without a diamond, and you will need to add a condition with "relation_type" to the JOIN.
Better and more readable from the table on M<->M.