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.