I would appreciate views on the following:
When the unique combination for table involves several columns is it best to:
a) Make the primary key include these three columns
b) Make a unique reference (such as a guid) as the primary key and use a constraint, on the original columns, to enforce the uniqueness?
If I use option a) then if another table references this one then it has to include all the extra columns which uses extra space and someone could miss a column from the join. However if I use option b) then I'm adding an extra column and kind of moving away from pure normalisation?
I've always used option b) and it's never caused me any issues but I would interested in other opinions. From memory I used b) as each table required a guid for replication but that could be incorrect? I tend to use availability groups now and, as far as I know, that restriction doesn't apply.
I'm not sure if there is a clear answer, which I've forgotten, or whether it is a case of "it depends"?