I have a table entity called Device, and a table entity called Action. I link them with my junction table DeviceActions on both PKs. Here’s a simplified ERD:
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:
>Solution :
Adding the ID PK to the DeviceActions table is the best fix.