I have following tables:

class_table (class_id = Auto Incremented Primary Key (AIPK) of this table)
user_table (class_id, user_id (AIPK of this table))
course_table(class_id, course_id (AIPK of this table))

enrollment_table(class_id, user_id, course_id, enrollment_id (AIPK))

Now to maintain data integrity and make sure that both user_id and course_id belong to same class_id, I will have to create 2 composite Foreign Key's (FK) on enrollment_table e.g.

(class_id, user_id) to -> user_table
(class_id, course_id) to -> course_table

With this kind of schema design, if there are more tables involved then number of composite FK's are increasing. So I wanted to know if there is any better way of modeling this kind of schema and avoiding too many composite FK's.

  • And by account_id you mean class_id, correct? – Erwin Brandstetter Dec 25 '13 at 10:50
  • yep, corrected the typo – Rahul Dec 25 '13 at 10:58
  • 2
    Why is class_id included in every other table? Also, why are you worried about composite FKs? Are they considered harmful? – Simon Righarts Dec 25 '13 at 11:00
  • Hi Simon, class_id is included because a user or course must be attached to 1 and only 1 class_id. Therefore I do not have a join table (or course_class association table) and have kept class_id in those tables directly. My worry about composite FK's are that in some tables I am ending up with 5-6 composite Foreign Key's like this. So am thinking if it would impact the insert/delete performance in those tables (considering that each of those tables (e.g. enrollment) could possibly have millions of rows. – Rahul Dec 25 '13 at 12:21

Two composite FKs including the otherwise redundant column class_id are probably the most elegant and efficient solution to enforce that users can only enroll in courses of their class. I see no better alternative.

| improve this answer | |
  • Thanks for the reply Erwin, I also suspected the same based on my limited knowledge of DB design. I just wanted to validate my knowledge as this is my first foray into DataBase design. – Rahul Dec 25 '13 at 12:25

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.