Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
1  
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
add comment

1 Answer 1

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.

share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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