Suppose I have a database schema with 3 tables – Area, Work and Author.
Area is a table which contains the list of Areas of interest of an Author / Areas in which a Work has been published, for example Math, Economics, Computer Science.
Author is a list of authors.
Work is a list of published articles.
Work_Area is a many to many relationship describing the area(s) of a Work. For instance Work with ID 1, can be on Math and Economics.
Work_Author is a many to many relationship between Work and Authors. For instance Work with ID 1 can have Authors with ID 1 and 2.
Author_Area is the list of interests of an Author.
Suppose An Author has Math has an interest and he publishes an Article on Math then there is no problem.
Instead an Author who has Math as an interest publishes an Article on Economics. Then my database becomes inconsistent in the following sense:-
-
When I look up the interests of this Author in the table Author_Area then I get ONE result Math.
-
When I join the Author with Work_Author and further join this with Work_Area and extract the list of all Areas in which the author has published work then I get TWO results – Math and Economics.
My query is : How can I prevent this inconsistency in my database?
Also, what is this type of inconsistency called ? Does it have a name in the literature?
Best Answer
A quick solution might be to make a trigger in the Work_Area table that checks the Interests table to see if that field already exists, and if it does not then insert it as a new record. This would prevent the need to manually add an author's new interests, just add their new article and let the interests table take care of itself