Best practice to avoid a composite key?

Suppose my students fill every year a survey, containing some questions (called fields) which answer is real-valued (called value). Every student may answer the survey, at most once a year.

A simple example for the survey:

• in 2012: Quality of the lecture (0-100)

• in 2013: Quality of the lecture (0-100) and Difficulty level (0-100)

I am faced with a dilemma : use composite keys or check constraint. Which solution is a best practice ?

No composite key :

I create the following table structure but I need a constraint to make sure that field.year is the same as survey.year

CREATE TABLE survey (
   surveyID int primary key,
   student_name nvarchar(20),
   year int,
)

CREATE TABLE fields (
   fieldID int primary key,
   field_name nvarchar(20),
   year int,
)

CREATE TABLE data(
   surveyID int,
   fieldID int,
   value float,
   primary key (surveyID, fieldID), 
   foreign key(studentID), 
   foreign key(fieldID)
)

With composite key :

With the following table structure, I have the drawback to use the tuple (surveyID, year) for each join I make.

CREATE TABLE survey (
   surveyID int primary key,
   student_name nvarchar(20),
   year int,
   primary key (surveyID, year)
)

CREATE TABLE fields (
   fieldID int primary key,
   field_name nvarchar(20),
   year int,
   primary key(fieldID)
)

CREATE TABLE data(
   surveyID int,
   fieldID int,
   year int,
   value float,
   primary key (surveyID, year, fieldID), 
   foreign key(studentID, year), 
   foreign key(fieldID, year)
)

To start with the correct primary key (single column vs multiple or artificial vs natural) is to use the primary key that is correct for the task.

Some people will tell you to always use an Artificial or Surrogate Key. This is a key that is a generated key and has nothing to do with the information it represents. For example an identity column or a GUID. Others will tell you that an artificial key is the wrong way to go and you want to use a Natural Key. This is a key that is constructed from one or more columns of the data itself. FYI a key with multiple columns is sometimes called a composite key. LastName, FirstName for example. Or in your case it looks like you are using a combination of the two. You have an integer ID (presumably artificial) + a year (presumably natural).

I'm not really going to go into the arguments here. You can Google them yourself. In your particular case I would leave your primary keys alone. In my opinion t makes sense to have SurveyId and FieldId columns that are your primary key. These are unique values (I assume) and the tables are basically lookup tables in this particular case. I would however move the Year column out of those tables and into the data table. If you don’t want to do this then take advantage of the fact that foreign keys can join to a ‘unique key’ as well as a primary key. A unique key is an index that enforces uniqueness but is necessarily the primary key. As an added bonus columns in a unique key can be nullable. In this case your structure would look like this:

CREATE TABLE survey (
   surveyID int primary key,
   student_name nvarchar(20),
   year smallint
)
GO

CREATE UNIQUE INDEX ix_survey ON survey(surveyID, year)
GO

CREATE TABLE fields (
   fieldID int primary key,
   field_name nvarchar(20),
   year smallint
)
GO

CREATE UNIQUE INDEX ix_fields ON fields(fieldID, year)
GO

CREATE TABLE data(
   surveyID int,
   fieldID int,
   year smallint,
   value float,
   primary key (surveyID, year, fieldID), 
   foreign key(surveyID, year) REFERENCES survey(surveyID, year), 
   foreign key(fieldID, year) REFERENCES fields(fieldID, year)
)
GO

I did take the liberty of changing the year column to a smallint which is more than large enough to hold a 4 digit year and only takes 2 bytes instead of 4.

Using the unique key you can enforce the fact that you want the year columns to be the same while not adding an unnecessary column (the IDs are already unique) to your primary key.

StackExchange Database Administrators Q#59029, answer score: 12