'ERD - Recursive relationship with two primary keys
I have a product table:
Product (ID, Version)
And I am having trouble creating a recursive table named "Update". this table should have a product ID and for each product, there will be an old version and a new version.
this is my desired outcome:
Update (ProductID, OldVersion, NewVersion)
but unfortunately, if I have two primary keys the result would be:
Update (NewProductID, NewVersion, OldProductID, OldVersion)
How can I solve this issue?
Solution 1:[1]
In UPDATE you need to make the PK the ProductID and either the OldVersion or NewVersion. The other version ID is then an attribute of this table. It doesn't matter which Version ID you choose to be part of the PK (from a technical perspective, it may make a difference from a usability/business perspective) but you need to be consistent, obviously.
Solution 2:[2]
This relationship should not be recursive, it is a normal relationship. My proposed solution should be like:
Product (ProductID, name, etc..) // ProductID is the primary key
Update (ProductID, Version) // both are the primary key
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | NickW |
| Solution 2 | Ahmet Feyzi |