I am struggling with philosophical discussions about whether to use composite primary keys in my SQL Server database. I have always used surrogate keys in the past, and I challenge myself, leaving my comfort zone to try something else. I read a lot of discussions, but so far I can’t come to any decision. The struggle I am experiencing is when I need to update the recording with a composite PC.
For example, the entry in the questions is as follows:
ContactID, RoleID, EffectiveDate, TerminationDT
PK in this case is (ContactID, RoleID, EffectiveDate). TerminationDTmay be zero.
If the user changes in my user interface RoleID, then I need to update the record. Using a surrogate key, I can do it Update Table Set RoleID = 1 WHERE surrogateID = Z. However, using the Composite Key method, when one of the fields in the composite key changes, I cannot refer to the old record to update it without supporting the link to the old values somewhere in the user interface.
I do not bind data sources in my user interface. I open the connection, get the data and save it in a bucket, and then close the connection. What are the opinions of everyone? Thank.
source
share