0

I currently have 2 entity types, which do not inherit from each other - call them A and B, and I need to create directional Many-to-Many relationships between each of them. The database in this case happens to be MySQL. I am looking for objective pro's and con's for option 1 and 2, in addition to what I listed so far. Maybe even an option 3 which is preferable.

Option 1 - A separate table for each entity type that specifies a hardcoded foreign key

Table 1:
 - From_A int FK
 - To_A int FK

Table 2:
 - From_A int FK 
 - To_B int FK

Table 3:
 - From_B int FK
 - To_A int FK

Table 4:
 - From_B int FK
 - To_B int FK

Option 2 - A single table for each connection

Table 1:
 - From int
 - From_Type int FK
 - To int
 - To_Type int FK

Table 2:
 - ID
 - Type_Name

Pro Option 1 / (Option 2 does not have these pro's):

  • FK constraints prevent incorrect data intries
  • InnoDB buffer pool caches FK relationships
  • ORM framework will pick up on the connections automatically

Pro option 2 / (Option 1 does not have these pro's):

  • Fewer tables need to be searched to find all relations of an entity
  • Scalability - The amount of tables needed for many-to-many relationships will not grow quadratically. If I have 3 entity types, I still need 1 table instead of 9. If I have 4 entity types I will need 1 instead of 16
Daniël Camps
  • 1,737
  • 1
  • 22
  • 33
  • I would have one table `Types`. Then an `Entity` table, with a FK the type. Each Entity has 1 type (i.e. 1 to 1). Then to link Entities together, setup a link table. Two columns, both FK of each Entities. This gives you no extra tables, and is flexible. – Nic3500 Oct 04 '19 at 02:02
  • Thank you, I Whatsapped my colleagues this very idea an hour after typing the question, and I agree with you 100% that it is the neatest solution. If you type it into an answer I will mark it as correct. – Daniël Camps Oct 07 '19 at 08:14
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Oct 07 '19 at 22:02
  • @philipxy the objects do not inherit from each other in my question – Daniël Camps Oct 08 '19 at 10:16
  • Having multiple, virtually identical, tables is almost always a schema design error. – Rick James Oct 11 '19 at 23:20

1 Answers1

1

I would have one table Types.

Then an Entity table, with a FK the type.

Each Entity has 1 type (i.e. 1 to 1).

Then to link Entities together, setup a link table. Two columns, both FK of each Entities. This gives you no extra tables, and is flexible.

Nic3500
  • 8,144
  • 10
  • 29
  • 40