How can i enforce data integrity in this setup?

Question

I have these tables:

   |-------------tblSchool -------------|
   |               + ID                 |
   |               + Name               |
   |                                    |
   |                                    |
tblSchoolYear                        tblTerm
 | + SchoolID                        | + SchoolID
 | + Name                            | + Name
 |                                   |
 |                                   |
 |---------- tblSchoolTerm ----------|
               + SchoolYearID
               + TermID
               + StartDate
               + EndDate

As you can see, I have 2 tables tblSchoolYear and tblTerm that both have SchoolIDs. These 2 tables then makesup a SchoolTerm. My problem is I want the to prevent the combination of tblSchoolYear and tblTerm in tblSchoolTerm withe different SchoolIDs.

For example, if I have 2 schools with IDs 100 and 101, I don't want to have a SchoolYear and Term in SchoolTerm in which SchoolYear has a SchoolID of 100 and the Term a SchoolID of 101.

What I can think of is using a trigger to enforce this, but I wanted to ask if maybe there is a better design to solve this problem without resorting to triggers.

Using SQL Server 2012 by the way.

ADDITIONAL INFO:

For those asking why I can't just have 2 tables, tblSchool and tblSchoolTerm, I have other columns in tblSchoolYear and tblTerm that contains data that I don't wnat duplicated. Sorry, I'll add some sample columns more to make it clearer.

score 1 · Answer 1 · answered Aug 14 '15 at 10:19

Have two columns in tblSchoolTerm one called TermSchoolID and the other YearSchoolID. Add a check constraint that enforces they are equal.

From the information given, however, it would seem to me that your model is incorrect. I would have thought a Term would relate to a Year, then School would have many-to-many with Term. You know your business rules best, though.

How can i enforce data integrity in this setup?

1 Answers1