1

This question already has an answer here:

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.

marked as duplicate by ypercubeᵀᴹ, RolandoMySQLDBA, Mark Sinkinson, Marian, Max Vernon Aug 17 '15 at 18:14

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 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. – Vladimir Baranov Aug 14 '15 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 '15 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 '15 at 8:41
  • @Vladimir Baranov: Added info – g_b Aug 15 '15 at 6:56
  • @tommyfly: Added info – g_b Aug 15 '15 at 6:57
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.