The number of categories must be greater than one but fewer than five

Question

I want to create a database application for creating academic examination. The database contains, for the sake of simplicity, 3 tables as follows.

Problems:

• ProblemID (int, identity, primary key)
• Problem (nvarchar)
• Solution (nvarchar)

Categories:

• CategoryID (int, identity, primary key)
• Category (nvarchar)

ProblemCategory:

• CategoryID (int, composite primary key)
• ProblemID (int, composite primary key

Each problem is linked to at least one category and at most 5 categories. My question is how to make sure this constraint is hold in database level?

Bonus question:

Is the following design recommended as a replacement for the design above?

Problems:

• ProblemID (int, identity, primary key)
• Problem (nvarchar)
• Solution (nvarchar)
• CategoryID1 (int, not null)
• CategoryID2 (int, null)
• CategoryID3 (int, null)
• CategoryID4 (int, null)
• CategoryID5 (int, null)

Categories:

• CategoryID (int, identity, primary key)
• Category (nvarchar)

Answer 1

Question 1: Each problem is linked to at least one Category.

Answer: declare a foreign key constraint on the Problems table.

Problems.ProblemID REFERENCES ProblemCategory.ProblemID

(Presumably there's also foreign key constraints declared on ProblemCategory to reference Problems.ProblemID and Categories.CategoryID.)

Question 2: Each problem is linked to at most 5 Categories.

Answer: declare a constraint on the ProblemCategory table. (As Branko points to.)

WHERE (SELECT MAX(CatCount)
       FROM (SELECT COUNT(*) AS CatCount
             FROM ProblemCategory
             GROUP BY ProblemID)
      ) <= 5

Question 3: Design with up to five Cat ID per Problem record.

Answer: No, that's a terrible idea. (Read any text about normalisation.) Consider:

• How to query for all Problems with Category "Normalisation"?
• How would you prevent the same Cat getting repeated on a given Problem?
• What does it mean for a Problem to have Cat ID3 and ID5 but ID4 IS NULL?