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.


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.

  • It is not clear why you can't have just two tables: School (1) and SchoolYearAndTerm (many), i.e. put your three tables tblSchoolYear plus tblTerm plus tblSchoolTerm into one table. Aug 14, 2015 at 7:45
  • 2
    A term is a part of a school year, so why does tblTerm reference tblSchool rather than tblSchoolYear?
    – Andriy M
    Aug 14, 2015 at 8:23
  • I agree with Vladimir, you probably need and an additional table. You may need to expand your question though. I'm not clear as to why two schools cannot have the same term. If I understand correctly, Year is ever increasing: 2015,2016,..2050 and terms is finite: 1,2,3,4. Is that correct? So, how could schools avoid sharing the same SchoolTerm? And, if I do understand correctly, Andriy is correct. Term and year should not reference school, rather the join/lookup table tblSchoolTerm should have SchoolID in it, or you need an additional join/lookup table joining tblSchool and tblSchoolTerm.
    – tommyfly
    Aug 14, 2015 at 8:41
  • @Vladimir Baranov: Added info
    – g_b
    Aug 15, 2015 at 6:56
  • @tommyfly: Added info
    – g_b
    Aug 15, 2015 at 6:57

1 Answer 1


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.

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