January 16, 2009 at 8:02 am
I have a question on schema design. Here is the situation:
There are four tables: Accounts, Courses, Units, UnitCourses
1. Accounts: Each row is a unique customer (PKey: AccountId )
2. Courses: An account has one or more Courses (PKey: CourseId, FKey: AccountId)
3. Units: Courses are grouped into Units, and a given course can be in one or more units. An account can have one or more Units (Pkey: UnitId, Fkey: AccountId)
4. UnitCourses: A many to many table for Courses within Units (Composite PKey: UnitId, CourseId)
Possible Scenerio:
Account A
-- Unit1
---- Course_a
---- Course_b
-- Unit2
---- Course_a (note: in both Unit1, Unit2 is ok because same account)
---- Course_c
Account B
--- Unit3
-----Course_d
-----Course_e
--- Unit4
-----Course_f
BAD:
Account A
--- Unit1
----- Course a
----- Course_d (this belongs to account B)
Question: How can I ensure a course belonging to Account "A" does not get put into a Unit belonging to Account "B"? Do I need to modify my schema?
January 16, 2009 at 10:43 am
You could make the Course and Unit pk into a compound PK that includes AccountId. Then the UnitCourse table will have an added column used as a constraint against each table. Ought to work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 16, 2009 at 12:13 pm
Grant Fritchey (1/16/2009)
You could make the Course and Unit pk into a compound PK that includes AccountId. Then the UnitCourse table will have an added column used as a constraint against each table. Ought to work.
If the tables Courses and UnitCourses have compound PK with AccountId then,
the Courses table allows a combination of:
Account_ACourse_b
Account_BCourse_b
So, Course_b is repeated for different accounts. But the requirement was that if the course is assigned to Account "A" then it must not be assigned to Account "B" no matter which Unit.
Similarly even the UnitCourses table allows Course_b to be repeated for another account.
steve (1/16/2009)
There are four tables: Accounts, Courses, Units, UnitCourses1. Accounts: ... (PKey: AccountId )
2. Courses: ... (PKey: CourseId, FKey: AccountId)
3. Units: ... (Pkey: UnitId, Fkey: AccountId)
4. UnitCourses: ... (Composite PKey: UnitId, CourseId)
Steve, as I see the tables are designed correctly for your requirement. Since the Courses table has CourseId as the PK, it will not allow Course_b to be repeated and only one Account can be assigned for that course(Course_b). So, there won't be a scenerio like:
Account_ACourse_b
Account_BCourse_b
January 16, 2009 at 12:35 pm
gyessql (1/16/2009)
Grant Fritchey (1/16/2009)
You could make the Course and Unit pk into a compound PK that includes AccountId. Then the UnitCourse table will have an added column used as a constraint against each table. Ought to work.If the tables Courses and UnitCourses have compound PK with AccountId then,
the Courses table allows a combination of:
Account_ACourse_b
Account_BCourse_b
So, Course_b is repeated for different accounts. But the requirement was that if the course is assigned to Account "A" then it must not be assigned to Account "B" no matter which Unit.
Similarly even the UnitCourses table allows Course_b to be repeated for another account.
steve (1/16/2009)
There are four tables: Accounts, Courses, Units, UnitCourses1. Accounts: ... (PKey: AccountId )
2. Courses: ... (PKey: CourseId, FKey: AccountId)
3. Units: ... (Pkey: UnitId, Fkey: AccountId)
4. UnitCourses: ... (Composite PKey: UnitId, CourseId)
Steve, as I see the tables are designed correctly for your requirement. Since the Courses table has CourseId as the PK, it will not allow Course_b to be repeated and only one Account can be assigned for that course(Course_b). So, there won't be a scenerio like:
Account_ACourse_b
Account_BCourse_b
Actually, I don't think you laid that out correct.
Account
AccountID, Desc
1, AccountA
2, AccountB
Course
CourseID,AccountId,Desc (you may need a unique constraint on the name, but then it might make a better PK then)
1, 1, CourseA
2, 2, CourseB
Unit
UnitID,AccountId
1,1
2,1
UnitCourse
UnitId,CourseId,AccountId
1,1,1 --possible
1,1,2 --not possible
1,2,2 --not possible
2,2,2 -- possible
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 16, 2009 at 12:59 pm
Grant Fritchey (1/16/2009)
Actually, I don't think you laid that out correct.Account
AccountID, Desc
1, AccountA
2, AccountB
Course
CourseID,AccountId,Desc (you may need a unique constraint on the name, but then it might make a better PK then)
1, 1, CourseA
2, 2, CourseB
Unit
UnitID,AccountId
1,1
2,1
UnitCourse
UnitId,CourseId,AccountId
1,1,1 --possible
1,1,2 --not possible
1,2,2 --not possible
2,2,2 -- possible
Ya, you are right Grant. I was thinking in terms of Course_desc (considering it being unique) and AccountId, totally forgot about CourseID. My bad!!
January 16, 2009 at 1:06 pm
gyessql (1/16/2009)
Ya, you are right Grant. I was thinking in terms of Course_desc (considering it being unique) and AccountId, totally forgot about CourseID. My bad!!
But if he did go with Course_Desc as a unique value, then he might want to consider tossing the ID fields. Plus, of course, the design (such as it is) I proposed would be off then too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply