Composite primary keys and influence on natural/surrogate keys usage

I have a fairly simple question about natural/surrogate key usage in a well-defined context wich manifests itself often, and that i'm going to illustrate.

Let's assume you are designing the DB schema for a product using SQL Server 2005 as DBMS. For the sake of simplicity let's say there are only two entities involved, wich have been mapped to 2 tables, Master and Slave. Assume that:

  1. We can have 0..n Slave entries for a single Master's row;
  2. Column set (A, B, C, D) in Master is the only candidate for primary key;
  3. Column B in Master is subject to changes over time;
  4. A, B, C, D are a mix of varchar, decimal and bigint columns.

The question is: how would you design keys/constraints/references for those tables? Would you rather (argumenting your choice):

  1. Implement a composite natural key on Master on (A, B, C, D), and a related composite foreign key on Slave, or
  2. Introduce a surrogate key K on Master, let say an IDENTITY(1,1) column with a related (single column) foreign key on Slave, adding a UNIQUE constraint on Master's (A, B, C, D), or
  3. Use a different approach.

As for me I'd go with option 2), mainly because of assumption 3) and performance-wise, but I'd like to hear someone else's opinion (since there is quite an open debate on the topic).

thank you

8
задан Andrea Pigazzini 24 May 2011 в 12:33
поделиться