Modeling many-to-one with constraints?
- by Greg Beech
I'm attempting to create a database model for movie classifications, where each movie could have a single classification from each of one of multiple rating systems (e.g. BBFC, MPAA). This is the current design, with all implied PKs and FKs:
TABLE Movie
(
MovieId INT
)
TABLE ClassificationSystem
(
ClassificationSystemId TINYINT
)
TABLE Classification
(
ClassificationId INT,
ClassificationSystemId TINYINT
)
TABLE MovieClassification
(
MovieId INT,
ClassificationId INT,
Advice NVARCHAR(250) -- description of why the classification was given
)
The problem is with the MovieClassification table whose constraints would allow multiple classifications from the same system, whereas it should ideally only permit either zero or one classifications from a given system.
Is there any reasonable way to restructure this so that a movie having exactly zero or one classifications from any given system is enforced by database constraints, given the following requirements?
Do not duplicate information that could be looked up (i.e. duplicating ClassificationSystemId in the MovieClassification table is not a good solution because this could get out of sync with the value in the Classification table)
Remain extensible to multiple classification systems (i.e. a new classification system does not require any changes to the table structure)?
Note also the Advice column - each mapping of a movie to a classification needs to have a textual description of why that classification was given to that movie. Any design would need to support this.
