Inspired by a Django modeling question: Database Modeling with multiple many-to-many relations in Django. The db-design is something like:
CREATE TABLE Book
( BookID INT NOT NULL
, BookTitle VARCHAR(200) NOT NULL
, PRIMARY KEY (BookID)
) ;
CREATE TABLE Tag
( TagID INT NOT NULL
, TagName VARCHAR(50) NOT NULL
, PRIMARY KEY (TagID)
) ;
CREATE TABLE BookTag
( BookID INT NOT NULL
, TagID INT NOT NULL
, PRIMARY KEY (BookID, TagID)
, FOREIGN KEY (BookID) REFERENCES Book (BookID)
, FOREIGN KEY (TagID) REFERENCES Tag (TagID)
) ;
CREATE TABLE Aspect
( AspectID INT NOT NULL
, AspectName VARCHAR(50) NOT NULL
, PRIMARY KEY (AspectID)
) ;
CREATE TABLE TagAspect
( TagID INT NOT NULL
, AspectID INT NOT NULL
, PRIMARY KEY (TagID, AspectID)
, FOREIGN KEY (TagID) REFERENCES Tag (TagID)
, FOREIGN KEY (AspectID) REFERENCES Aspect (AspectID)
) ;
and the issue is how to define the BookAspectRating
table and to enforce referential integrity, so one cannot add a rating for a (Book, Aspect)
combination that is invalid.
AFAIK, complex CHECK
constraints (or ASSERTIONS
) that involve subqueries and more than one table, that could possibly solve this, are not available in any DBMS.
Another idea is to use (pseudocode) a view:
CREATE VIEW BookAspect_view
AS
SELECT DISTINCT
bt.BookId
, ta.AspectId
FROM
BookTag AS bt
JOIN
Tag AS t ON t.TagID = bt.TagID
JOIN
TagAspect AS ta ON ta.TagID = bt.TagID
WITH PRIMARY KEY (BookId, AspectId) ;
and a table that has a Foreign Key to the above View:
CREATE TABLE BookAspectRating
( BookID INT NOT NULL
, AspectID INT NOT NULL
, PersonID INT NOT NULL
, Rating INT NOT NULL
, PRIMARY KEY (BookID, AspectID, PersonID)
, FOREIGN KEY (PersonID) REFERENCES Person (PersonID)
, FOREIGN KEY (BookID, AspectID)
REFERENCES BookAspect_view (BookID, AspectID)
) ;
Three questions:
-
Are there DBMS that allow a (possibly materialized)
VIEW
with aPRIMARY KEY
? -
Are there DBMS that allow a
FOREIGN KEY
thatREFERENCES
aVIEW
(and not only a baseTABLE
)? -
Could this integrity problem be solved otherwise – with available DBMS features?
Clarification:
Since there is probably no 100% satisfying solution – and the Django question is not even mine! – I'm more interested in a general strategy of possible attack on the problem, not a detailed solution. So, an answer like "in DBMS-X this can be done with triggers on table A" is perfectly acceptable.
Best Answer
This business rule can be enforced in the model using only constraints. The following table should solve your problem. Use it instead of your view: