sql server - Relational table design with same type in a many-to-many relationship - Stack Overflow

admin管理员组

文章数量:1128734

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:

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:

• sql-server
• database-design

Share Improve this question Follow edited Jan 8 at 19:54 Thom A 95.3k1111 gold badges5959 silver badges9292 bronze badges asked Jan 8 at 18:00 GH DevOpsGH DevOps 42233 silver badges1515 bronze badges 2

• 1 You made the best fix by adding ID to the DeviceActions table. You might also consider InsertDate and User fields. It seems strange that Device and Action tables do not have Name or Description fields. – Bart McEndree Commented Jan 8 at 18:02
• @BartMcEndree They have description fields, I just omitted them in the diagram and only showing the keys. – GH DevOps Commented Jan 8 at 18:15

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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

本文标签： sql serverRelational table design with same type in a manytomany relationshipStack Overflow