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:
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:
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:
Adding the ID PK to the DeviceActions table is the best fix.