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
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
UPDATE statements that checks that exactly one of them is
References to literature are appreciated as well.