5

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)
)
  • 2
    Why is it a drawback to use 2 columns for a join? (sidenote: the year should be included in the fields table primary key as well, right?) – ypercubeᵀᴹ Feb 17 '14 at 10:04
  • 5
    Why do you even have year in all three tables? I think that you should remove it at least from fields (add surveyID instead). Even if fields.year is used to make questions available only for a surveys from particular years, it's still a bad design, because you can't reuse same questions in next years. – Adam Luniewski Feb 17 '14 at 12:48
  • 2
    I agree with @frikozoid, the whole design does not make much sense. Can a question be asked/answered by many students? By same student in many years? Why do you name it fields if it is the questions table? Why the other is called data if it is an answers table? (Everything is data in the database) Shouldn't the student_id be in the answers table? – ypercubeᵀᴹ Feb 17 '14 at 15:12
11

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.

  • 1
    Thank you for the clear explanation. I didn't understand what a composite key was until I read, 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. Also, the smallint for the year is a good tip for anyone reading this. – HPWD May 31 '16 at 13:11
1

Assuming you are using MySQL....

IMHO, Best practice is to AVOID using a composite PRIMARY KEY - especially composed from the row data. The reason being is a) the primary key (at least in MySQL) is what is stored for the Foreign Key and Index constraints. This helps to minimize the index size, sorting and updates and; b) it helps avoid the messy and expensive FK constraint updates if you have to change the data in or structure of the table. Thus every table typically will have a primary key that has no relationship to the table data. Basically you have a unique record id.

Also, as a side note, I recommend that the primary key be an UNSIGNED INTEGER (because you really never have a -101 primary key) to maximize your storage capacity. In MySQL I would recommend a BIGINT.

Therefore, your NO COMPOSITE key examples are better (here optimized for MySQL):

CREATE TABLE survey (
 surveyID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 student_name varchar(20),
 year int,
 primary key (surveyID)
)

CREATE TABLE fields (
 fieldID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 field_name varchar(20),
 year int,
 primary key (fieldID)
)

CREATE TABLE data(
  dataID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  surveyID BIGINT UNSIGNED NOT NULL,
  fieldID BIGINT UNSIGNED NOT NULL,
  value float,
  primary key (dataID),
  CONSTRAINT data_surveyID FOREIGN KEY (surveyID) REFERENCES survey (surveyID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT data_fieldID FOREIGN KEY (fieldID) REFERENCES field (fieldID) ON DELETE CASCADE ON UPDATE CASCADE,
)
  • 4
    The question is tagged with [sql-server] – ypercubeᵀᴹ Feb 17 '14 at 15:34
  • Good. In this case, I will need to add a check contrainst. It will make the data structure maybe a little bit more complicated to understand. – motsdujour Feb 17 '14 at 18:26
  • Second, how does this answer the question, "how to make sure that field.year is the same as survey.year?" – ypercubeᵀᴹ Feb 17 '14 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.