Multi-to-One Modeling with Constraints?

Question

I am trying to create a database model for movie classifications, where each movie can have one classification from each of several rating systems (e.g. BBFC, MPAA). This is the current design with all the implied PCs and FK:

TABLE Movie 
( 
    MovieId INT -- PK
)

TABLE ClassificationSystem 
( 
    ClassificationSystemId TINYINT -- PK
)

TABLE Classification 
(
    ClassificationId INT,          -- PK
    ClassificationSystemId TINYINT -- FK
)

TABLE MovieClassification 
(
    MovieId INT,          -- composite PK, FK
    ClassificationId INT, -- composite PK, FK
    Advice NVARCHAR(250)  -- description of why the classification was given
)

The problem is the table MovieClassification, the limitations of which will allow the use of several classifications from the same system, while ideally it should allow only zero or one classification from the given system.

Is there any reasonable way to restructure this so that a movie that has exactly zero or one classification from any given system is forcibly bound to database constraints, given the following requirements?

• , (, ClassificationSystemId MovieClassification , Classification)
• (.. - )?

Advice - , ​​ . .

Answer 1

, . :

create function dbo.ClassificationSystemCheck()
returns int
as begin
    return (select max(cnt)
    from (
        select count(*) as cnt
        from MovieClassification mc
        left join Classification c
        on c.ClassificationId = mc.ClassificationId
        group by mc.MovieId, c.ClassificationSystemId
    ) qry)
end
go
alter table MovieClassification
add constraint chk_MovieClassification
check (dbo.ClassificationSystemCheck() <= 1)
go
alter table Classification
add constraint chk_Classification
check (dbo.ClassificationSystemCheck() <= 1)
go
insert into Classification select 1,1
insert into MovieClassification select 1,1
insert into MovieClassification select 1,1 -- Boom!

. , ClassificationSystemId MovieClassification.

Answer 2

, , ClassificationSystemId MovieClassification, ( ) MovieClassification.

, Classification :

• .
• .
• ( ).

. , , , .

. - , , .

, , . , , , . .

, , , ClassificationSystemId MovieClassification MovieClassification ( ClassificationSystemId Classification ):

-- Tables Movie, ClassificationSystem not included for brevity

CREATE TABLE Classification
(
  ClassificationId INT,
  ClassificationSystemId INT,
  PRIMARY KEY(ClassificationId, ClassificationSystemId),
  FOREIGN KEY(ClassificationSystemId) REFERENCES ClassificationSystem(ClassificationSystemId)
);

CREATE TABLE MovieClassification
(
  ClassificationId INT,
  ClassificationSystemId INT,
  MovieId INT,
  Advice NVARCHAR(MAX),
  PRIMARY KEY(ClassificationId, ClassificationSystemId, MovieId),
  FOREIGN KEY(ClassificationId, ClassificationSystemId) REFERENCES Classification(ClassificationId, ClassificationSystemId),
  FOREIGN KEY(MovieId) REFERENCES Movie(MovieId),
  UNIQUE(ClassificationSystemId, MovieId)
);

Answer 3

, ?

TABLE Movie 
( 
    MovieId INT
)

TABLE ClassificationSystem 
( 
    ClassificationSystemId TINYINT
)

TABLE Classification 
(
    ClassificationId INT,
)

TABLE MovieClassification 
(
    MovieId INT,
    ClassificationId INT,
    ClassificationSystemId TINYINT,
    Advice NVARCHAR(250) -- description of why the classification was given
)

, .

Answer 4

?

""?

Answer 5

• (ClassificationId, ClassificationSystemId)
• FK, MovieClassification.
• MovieClassification (MovieId, ClassificationSystemId)

Answer 6

OK. , , , , .

{movieID, }, {movieID categoryScheme}.

Movie {movieID}, { }.

// . , , , , // .

Answer 7

, .

create table #Movies (PK_moID int identity(1,1), moName varchar(50) primary key(PK_moID))
create table #ClassificationSystem (PK_csID int identity(1,1), csName varchar(50) primary key(PKcsID))
create table #Classification (PK_clID int identity(1,1), FK_csID int)
create table #MovieClassification (FK_moID int, FK_csID int, FK_clID int primary key (FK_moID, FK_csID))

, pk , ( ). classificaiton .