Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
) ;

db diagram

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 a PRIMARY KEY?

  • Are there DBMS that allow a FOREIGN KEY that REFERENCES a VIEW (and not only a base TABLE)?

  • 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.

share|improve this question
    
Posting as a comment to your first two questions - and not necessarily for you, as I'm sure you're already aware - but SQL Server does not support primary or foreign keys for views. –  Aaron Bertrand May 14 '12 at 23:11
    
@Aaron: yes, thank you. I've read that Oracle supports PK costraints in views. But not sure if it would work in this situation. And the answer to 2nd question (about FKs to views) is probably negative in Oracle. –  ypercube May 14 '12 at 23:17
    
So is your goal to move to an RDBMS that supports what you're looking for, or are you trying to get a solution in a specific RDBMS? I can see the potential answers you'll get really forking or stagnating depending on your goal... :-) –  Aaron Bertrand May 14 '12 at 23:18
    
But I'm interested to learn if there is any other solution (triggers, Check costraints or other combo) –  ypercube May 14 '12 at 23:18
    
So I suggested a trigger for SQL Server, but I wasn't planning to actually write the code because it wasn't clear if you would care about such a solution in a specific RDBMS. :-) –  Aaron Bertrand May 14 '12 at 23:25
add comment

5 Answers 5

up vote 6 down vote accepted

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:

    CREATE TABLE BookAspectCommonTagLink
    (  BookID INT NOT NULL
    , AspectID INT NOT NULL
    , TagID INT NOT NULL
--TagID is deliberately left out of PK
    , PRIMARY KEY (BookID, AspectID)
    , FOREIGN KEY (BookID, TagID) 
        REFERENCES BookTag (BookID, TagID)
    , FOREIGN KEY (AspectID, TagID) 
        REFERENCES AspectTag (AspectID, TagID)
    ) ;
share|improve this answer
    
Oh nice. The only issue I can think is the complexity introduced in inserting/deleting BookTags and TagAspects. Every time a new BookTag (or TagAspect) is removed for example, a search has to be done to remove corresponding rows in this table and/or change the TagID to another Tag that is related to the same BookAspect combination. –  ypercube May 15 '12 at 19:50
    
And similar search would have to be done for inserting into those 2 tables. But complex rules require complex procedures, so this looks really good. –  ypercube May 15 '12 at 19:52
    
@ypercube When you delete a tag, you do need to check and possibly switch to another tag linking the same Book and Aspect. When you insert new tags, however, there is no need to do any checks until you need to insert a rating. –  AlexKuznetsov May 15 '12 at 20:04
1  
If the troubleshooter and the data entry person is the same person, or if you expose the error message to the end user, sure. You are thinking too much about one-person shops where you're doing everything. –  Aaron Bertrand May 15 '12 at 20:45
3  
@AaronBertrand You just did me a huge favor. I am finishing an article entitled "Developing Low Maintenance Databases", and I forgot to mention that app servers must log the original error messages coming from databases. I just added it. Thank you for reminding ;) –  AlexKuznetsov May 16 '12 at 0:26
show 4 more comments

I think you'll find that in a lot of cases, complex business rules cannot be enforced via the model alone. This is one of those cases where, at least in SQL Server, I think a trigger (preferably an instead of trigger) better serves your purpose.

share|improve this answer
    
Hey Aaron, can you please explain why in this case a trigger is a better choice than an entity and a few constraints? –  AlexKuznetsov May 15 '12 at 19:47
1  
@AlexKuznetsov Sure, because I didn't spend 17 hours thinking about how to implement this with multiple multi-column foreign keys, and all the extra logic that might be required to deal with validation and error handling anyway? –  Aaron Bertrand May 15 '12 at 19:49
    
+1 I see your point. Makes sense. –  AlexKuznetsov May 15 '12 at 20:11
    
Be careful about race conditions that a naive implementation of triggers might introduce. For example, one transaction might disconnect a book from the tag and another still think it is OK to connect to the corresponding aspect, simply because the first transaction hasn't committed yet. The complexities introduced by @AlexKuznetsov answer are probably less than the complexity and fragility of the locking "protocol" necessary to prevent race conditions in triggers, IMHO. –  Branko Dimitrijevic Jan 7 at 2:36
add comment

In Oracle, one way to enforce this sort of constraint in a declarative fashion would be to create a materialized view that is set to refresh fast on commit whose query identifies all the invalid rows (i.e. BookAspectRating rows that have no match in BookAspect_view). You can then create a trivial constraint on that materialized view that would be violated if there are any rows in the materialized view. This has the benefit of minimizing the amount of data that you have to duplicate in the materialized view. It can cause problems, however, since the constraint is only enforced at the point that you're committing the transaction-- many applications aren't written to expect that a commit operation might fail-- and because the constraint violation can be somewhat hard to associate with a particular row or a particular table.

share|improve this answer
    
The implied reason for this solution is that in Oracle a foreign key can't be created referencing a view. A primary key can be created on a view, but only in a disabled (documentation only) state. –  Leigh Riffel May 15 '12 at 19:39
    
@LeighRiffel (and Justin): thnx. I haven't yet to understand fully what a disabled PK is good for. But it seems that only Oracle supports primary keys on views. –  ypercube May 16 '12 at 15:45
1  
It provides the knowledge that the intention was for the PK columns to be unique in the view, nothing more. All that said, I like AlexKuznetsov's answer. –  Leigh Riffel May 16 '12 at 15:53
add comment

In PostgreSQL, I cannot imagine a solution without involving triggers, but it certainly can be solved that way (be it maintaining a materialized view of some sort or a before trigger on BookAspectRating). There are no foreign keys referencing a view (ERROR: referenced relation "v_munkalap" is not a table), let alone a primary key.

share|improve this answer
add comment

SIRA_PRISE allows that.

Although the FK isn't called "FK" anymore, but just "database constraint", and the "view" does in fact not even have to be defined as a view, you can just include the view defining expression inside the declaration of the database constraint.

Your constraint would look something like

SEMIMINUS(BOOKASPECT , JOIN(BOOKTAG , TAGASPECT))

and you're done.

In most SQL DBMSs however, you'd have to do the analysis work on your constraint, determine how it can be violated and implement all needed triggers.

share|improve this answer
    
Interetsing. I'll have a look at it. –  ypercube Sep 11 '12 at 13:56
    
The Terms of use of the SIRA_PRISE site are at least, unorthodox. –  ypercube Sep 11 '12 at 14:09
    
I know. It reflects what I thought was important at the time of writing. –  Erwin Smout Sep 11 '12 at 14:57
    
(But thoughts can turn out to be somewhat silly in retrospect.) –  Erwin Smout Sep 11 '12 at 15:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.