Since most people believe that the Primary Key is a Clustering Cluster, I am going to interpret your question as “Good or bad, to have 4 columns as a clustering key”.
The situation you are considering is related to a debate such as a discussion of cluster indexes and a surrogate key versus a natural key .
In this situation, I would like to consider the impact of a composite byte key of 12 bytes wide and a four-class clustering key (twice as much if you intend to use bigint ). My decision tree will look something like this:
Will we use Hekaton (OLTP in memory)?
How many rows will be in this table?
How will the feedback table be requested?
Various combinations and not always all course_id, student_Id, question_id => surrogate key.
- In this case, you may need to have several supporting index combinations of
course_id, student_Id, question_id for your queries. The clustering key is included in all non-clustered indexes, and the more, the more space / pages are required for each index entry. => surrogate key.
Almost always all three course_id, student_Id, question_id or almost always course_id or course_id, student_Id ; but not student_id without course_id , and not question_id without course_id, student_Id (zero or only a pair of nonclustered indexes in this table) => continue ...
Will a link to another table link to this table?
Yes: for example. Course instructors will be able to leave a comment or comment regarding the answer to the feedback question. => surrogate key.
Kind ... for example. The Audit / History table will track insert / update / delete rows in this table.
- a surrogate key can make tracking changes less difficult to verify, and the audit table clustering key is likely to be a surrogate key, as well as a datetime or surrogate key or composite key, as well as a datetime or surrogate key.
No => Composite key is a smart option
Even if my first run of the aforementioned decision tree leads me to a composite key, I will probably start my project using a surrogate key because it is easier to get rid of (because it is not used) than to go back and add it and implement its use.
Just to clarify, I had cases where I discovered that a composite key was the best solution and reorganized the design to remove the surrogate key. I do not want to leave the impression that the surrogate key is always the best solution, even if it is a standard default for many designers (including me).
I would start with something like this:
create table feedback ( feedback_id int not null identity(1,1) , course_id int not null , student_id int not null , question_id int not null , response_added datetime not null constraint df_feedback_response_added_gd default getdate() , response nvarchar(max) null , constraint pk_feedback primary key clustered (feedback_id) , constraint fk_feedback_course foreign key (course_id) references course(course_id) , constraint fk_feedback_students foreign key (student_id) references student(student_id) , constraint fk_feedback_question foreign key (question_id) references question(question_id) , constraint uq_feedback_course_student_question unique (course_id, student_Id, question_id) ); create unique nonclustered index ux_feedback_course_student_question_covering on feedback (course_id, student_Id, question_id) include (response_added, response);
Link: