Database Design Composite Keys

Question

I am going to use a contrived example: one headquarter has one-or-many contacts. A contact can only belong to one headquarter.

---

TableName = Headquarter

Column 0 = Id : Guid [PK]
Column 1 = Name : nvarchar(100)
Column 2 = IsAnotherAttribute: bool

---

---

TableName = ContactInformation

Column 0 = Id : Guid [PK]
Column 1 = HeadquarterId: Guid [FK]
Column 2 = AddressLine1
COlumn 3 = AddressLine2
Column 4 = AddressLine3

---

I would like some help setting the table primary keys and foreign keys here?
How does the above look?
Should I use a composite key for ContactInformation on [Column 0 and Column1]?
Is it ok to use surrogate key all of the time?

Answer 1

I would stay away from composite keys. The surrogate key issue is up for debate, but I always use an INT Identity column (in SQL Server) if I can get away with it. I only use GUIDS if the database must support replication or merging distributed data.

I think your columns look OK other than the GUIDs.

Answer 2

I would say that your primary key should be Id. A composite key on ContactInformation of Id and HeadquarterID might make sense if you will use those two pieces of information often together, but I'd prefer using just Id as a key and then you can create an index on Id and HeadquarterID if you really need to.

Answer 3

You would only need a composite key on columns 0 and 1 of ContactInformation if each contact could belong to more than one headquarter; since you need the opposite, what you've descrbed should work fine.

Personally I would only use Guids if I really really needed to. Stick to ints otherwise. I also tend to use surrogate keys nearly everywhere.

Answer 4

As well as the surrogate key (your GUID Id) it is often a good idea to identify a business key (natural key/domain key) that is based on the business attributes that define the entity.

With the present table design, there is nothing stopping you adding two contact with the same attributes (name, headquater, address etc..). This is usually not desirable,so you add a composite natural key on the attributes that define the contact. As a PK is already defined, the Inatural key will be a unique constraint/index on those attributes.

I agree that the PK should be simple rather than composite. Composite PKs are a real pain to work with and queries become more complex.

Answer 5

It's a mistake to imagine this is a choice between using a composite key OR a surrogate. A surrogate key achieves something quite different from a key constraint on other attributes. The surrogate does not prevent those attibute values from being duplicated so the meaning of the table would be quite different if you just used one or the other key.

You should implement whatever keys you need to ensure the uniqueness and integrity of the data. If that means using a composite key as well as a surrogate then do so.

Having said that, it isn't clear to me what the likely keys are in your example. If Id is a candidate key of ContactInformation then (HeadquarterId, Id) is not - it's a superkey but it's not irreducible.