Mysql – Modelling a relationship involving books, book parts and reviews

database-designMySQL

I would like to model something similar to the following scenario in a database built on MySQL:

I am managing reviews of texts. These texts can either be books or parts of books (e. g. different articles). There may be multiple reviews per text. It can also be guaranteed there is an identifying relationship between from an article to the containing book.

My approach was the following:

The table Review has an ID as a primary key, a name and a foreign key to the primary keys of Book and Book Part. Book and Book Part have primary keys and some custom attributes for each (see below). Additionally, there is an identifying 1-to-n relationship between Book and Book Part.

Is this a viable approach?

Edit: I created a picture for the model.

Model

Description of attributes

Review
------
author - author of the review
rating - an integer describing how well the author liked the text
idBook - book the review is about
idBook_Part - book part the review is about

Book
----
author - author of the book
year - year the book was published

Book Part
---------
author - author of the book part
start page - page where the book part starts in the book
end page - page where the book part ends in the book
idBook - book that contains the part

Note: The attributes of Book and Book Part are intentionally not supersets of each other.

To make sure Review contains either an idBook or an idBook_Part I have written a MySQL trigger for INSERT and UPDATE statements that checks that exactly one of them is NULL.

References to literature are appreciated as well.

Best Answer

Alternative to your approach (exclusive arcs) which you may want to consider is to create a common parent table for Book, and BookPart. For instance, ReviewableEntity: 

ReviewableEntity 
 (re_id INT NOT NULL ,
  re_type NOT NULL enum('book','part'),
  PK (re_id) 

 );
 Book 
  (
  re_id INT NOT NULL ,

  ... book attributes 
   PK (re_id), 
   FK (re_id) REFERENCES ReviewableEntity(re_id), 
  )

 BookPart 
  (
    re_id INT NOT NULL ,

  ... book part attributes 
   PK (re_id), 
   FK (re_id) REFERENCES ReviewableEntity(re_id), 
  )

Now Review can have just one foreign key to ReviwableEntity .

There is a challenge to enforce that book details are always stored in book table, and book parts in BookPart table which can be solved in multiple ways .
1. Create stored procedures that insert/update data properly and deny direct manipulation of data with REVOKE
2. Carry over re_type attribute to detail tables (Book and BookPart)
3. Ignore the problem on db level, and deal with it by administrative means, e.g. force developers to insert into proper table(surprisingly , for this particular case it's not the worst approach ) .

Update.

Compared to exclusive arcs a parent table has the following advantages :
1. Adding new reviewable type is an easy and cheep operation (creating a new table + modifying enum) which can be performed online even for very large table. Exclusive arc approach requires adding column to existing table and modifying code that enforces non-nullability of only one field
2. Other tables can reference ReviewableEntity without knowing exact type
3. Extracting common attributes from detail tables and storing them in common parent will allow queries to hit only one (parent) table thus eliminating joins.

On the other hand, exclusive arc is a way simpler design, and doesn't require extra table .

Also, your choice may be influenced by RDMBS features. From what I remember, mysql didn't have CHECK constraints, so enforcing on db level NOT NULL for only one of IdBook, IdBookPart will require trigger .

Another option (which I personally don't like) is to have join tables , book_part_review( IdBookPart, IdReview), and book_review(IdBook, IdReview).

Related Question