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:
Review has an
ID as a primary key, a
name and a foreign key to the primary keys of
Book Part have primary keys and some custom attributes for each (see below). Additionally, there is an identifying 1-to-n relationship between
Is this a viable approach?
Edit: I created a picture for the model.
Description of attributes
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
author - author of the book
year - year the book was published
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
UPDATE statements that checks that exactly one of them is
References to literature are appreciated as well.