sql-serverdatabase-design

Relational table design with same type in a many-to-many relationship


I have a table called Device, and another table called Action. I link them with my junction table DeviceActions on both PKs.

Here's a simplified ERD model:

DeviceERD

If each action is only recorded once for each device inside DeviceActions, everything is fine. However, a device may have the same action more than once. I cannot enter the same action into the DeviceActions table without violating the PK/FK constraints on DeviceId and ActionId.

How can I set up my junction table so it can store the same device Id with the same action Id?

Here's a record example of what I'm looking for:

DeviceDataExample

Device 2536 has the same action Id (1) twice. I added a PK to DeviceActions and made DeviceId and ActionId FKs in the junction table but I don't know if this is the correct approach especially for performance once the table starts to grow:

New Device ERD


Solution

  • Adding the ID PK to the DeviceActions table is the best fix.