Possibly inconsistent database?

Question

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:-

1. When I look up the interests of this Author in the table Author_Area then I get ONE result Math.

2. 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?

Answer 1

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

Answer 2

I would suggest that you do something like this (see fiddle here).

Create an author table and populate it:

CREATE TABLE author
(
  author_id SERIAL NOT NULL PRIMARY KEY,
  author_name TEXT NOT NULL,
  author_name_no SMALLINT NOT NULL,
  CONSTRAINT author_name_name_no_uq UNIQUE (author_id, author_name_no)
);

INSERT INTO author (author_name, author_name_no) 
VALUES ('Paul', 1), ('Jim',  1), ('Mary', 1), ('Fred', 1);

The author_name_no field is to take account of authors with the same name - I have done a bit of work in this area and it occurs frequently enough to be an issue worthy of consideration. You could also have an alternate names table - i.e. in some publications, initials only are used and in others, it's the full name - this can also be confusing. In your definitive author table, include the most accurate and detailed data possible.

Obviously, you can have more details (excluded for brevity) like DOB, place of birth, Wiki reference, LinkedIn... you get the picture. Also, don't forget that for academic works in particular, Institutions, Organisations and Consortia can be authors. Legal persons like companies can also be authors of documentation and studies &c. For an example, see the paper "Genome Sequence of the Nematode C. elegans: A Platform for Investigating Biology" where the author is "The C. elegans Sequencing Consortium" (see my avatar).

Then create and populate a subject table (I prefer this word to area which has another meaning):

CREATE TABLE subject
(
  subject_id SERIAL NOT NULL PRIMARY KEY,
  subject_name TEXT NOT NULL
);

INSERT INTO subject (subject_name)  -- only 4 for brevity in example
VALUES
('Genetics'), ('Maths'), ('Economics'), ('Comp_Sci');

Then, we have the work table:

CREATE TABLE work
(
  work_id SERIAL NOT NULL PRIMARY KEY,
  work_title TEXT NOT NULL,
  work_type TEXT NOT NULL
  -- other work details... 
  -- deliberately not putting in DATE for example, this fiddle is complex enough
  -- and not including Journal or Book name, page numbers, publisher... the list goes on
);  

INSERT INTO work (work_title, work_type) 
VALUES 
('G_1', 'Book'), ('G_2', 'Journal'), 
('M_1', 'Book'), ('M_2', 'Journal'), ('M_3', 'Journal'),
('E_1', 'Book'),
('CS_1', 'Book'), ('CS_2', 'Book'), ('CS_3', 'Journal'),
('G_CS_1', 'Book'), ('G_CS_2', 'Journal'), ('G_CS_3', 'Journal'),
('G_M_1', 'Journal'), ('G_M_2', 'Journal'),
('M_E_1', 'Book'), ('M_E_2', 'Book'), ('M_E_3', 'Journal'),
('E_CS_1', 'Journal'), ('E_CS_2', 'Book'), ('E_CS_3', 'Journal'),
('G_M_CS_1', 'Book');

The work_type field in this example can be either 'book' or 'journal', but it can be 'newspaper article', 'company report', 'chapter', 'section'... &c... Can become complicated - maybe another reference table? You could perhaps take a look at F/LOSS systems and see how they deal with this issue?

For the works, I have used 'G' for Genetics, 'M' for Maths, 'E' for Economics and 'CS' for Computer Science - rather than try to make up realistic titles - thus 'E_CS_2' is the second work on the topic of both Economics and Computer Science.

Next, we have the work_subject table - for this fiddle, it's obvious from the title. I had to do this to keep track of what was referring to what, but in real life, the subject of a particular work may not be immediately obvious (or even obvious at all) from the title of the work.

CREATE TABLE work_subject -- obviously a work can have many areas, note that all
                          -- have 1 or 2 except work 21 which has 3
(
  work_id INTEGER NOT NULL,
  subject_id INTEGER NOT NULL,
  CONSTRAINT ws_pk PRIMARY KEY (work_id, subject_id),
  CONSTRAINT ws_work_id_fk FOREIGN KEY (work_id) REFERENCES work (work_id),
  CONSTRAINT ws_subject_id_fk FOREIGN KEY (subject_id) REFERENCES subject (subject_id)
);

INSERT INTO work_subject VALUES
(1, 1), (2, 1), (3, 2), (4, 2), (5, 2), (6, 3), (7, 4), (8, 4), (9, 4), (10, 1),
(10, 4), (11, 1), (11, 4), (12, 1), (12, 2), (13, 1), (13, 2), (14, 1), (14, 2),
(15, 2), (15, 3), (16, 2), (16, 3), (17, 2), (17, 3), (18, 3), (18, 4), (19, 3),
(19, 4), (20, 3), (20, 4), (21, 1), (21, 2), (21, 4);

In my example here, all the works have 1 or 2 subjects except work 21 which has 3.

Finally, we have the work_author table - obviously a work can have many authors.

CREATE TABLE work_author  -- obviously a work can have many authors
(
  work_id INTEGER NOT NULL,
  author_id INTEGER NOT NULL,
  CONSTRAINT wa_pk PRIMARY KEY (work_id, author_id),
  CONSTRAINT wa_work_id_fk FOREIGN KEY (work_id) REFERENCES work (work_id),
  CONSTRAINT wa_author_id_fk FOREIGN KEY (author_id) REFERENCES author (author_id)
);

INSERT INTO work_author
VALUES
(1, 1), (2, 1), (2, 3), (3, 3), (4, 3), (4, 4), (5, 3), (5, 4), (6, 4), (7, 2),
(8, 2), (9, 1), (9, 2), (10, 1), (11, 1), (11, 2), (12, 1), (12, 2), (13, 3),
(14, 1), (14, 3), (15, 2), (15, 4), (16, 2), (17, 4), (18, 2), (18, 4),
(19, 4), (20, 2), (21, 1), (21, 2), (21, 3);

Again, all works have 1 or 2 authors, except work 21 which has 3.

The authors:

Paul has works in Genetics  and/or Computer Science
Jim  has works in Maths     and/or Computer Science
Mary has works in Maths     and/or Genetics
Fred has works in Economics and/or Maths

I have made no effort, nor should any effort be made, to take account of an author's "interests" - they are defined by the works they have produced. As I mentioned in comments, I have lots of interests (Ecclesiastical History, Politics, Current Affairs) about which I may have read a lot or a little, but I doubt if I'll ever be published formally in virtually any of them.

So, with this schema, we can answer many questions.

How many authors have published works in Genetics?

SELECT COUNT(*) FROM work_subject ws
WHERE ws.subject_id = 1;

or

SELECT COUNT(*) FROM work_subject ws
WHERE ws.subject_id = 
(
  SELECT s.subject_id FROM subject s 
  WHERE subject_name = 'Genetics'
);

Result (both):

count
    8

What are the names of authors and their works which are in either Genetics or Computer Science?

SELECT DISTINCT author_id, waw, author_name, work_title, work_type
FROM
(
  SELECT
    wa.work_id AS waw, wa.author_id, 
    ws.work_id AS wsw, ws.subject_id,
    a.author_name, 
    w.work_title,
    w.work_type
  FROM work_author  wa 
  JOIN work_subject ws
    ON wa.work_id = ws.work_id
  JOIN author a
    ON wa.author_id = a.author_id
  JOIN work w
    ON ws.work_id = w.work_id
  WHERE ws.subject_id = 1 OR ws.subject_id = 4
  ORDER BY wa.work_id, wa.author_id
) AS tab_1
ORDER BY waw, author_id, work_title;

Result:

author_id   waw author_name work_title  work_type
1   1   Paul    G_1       Book
1   2   Paul    G_2       Journal
3   2   Mary    G_2       Journal
2   7   Jim     CS_1      Book
2   8   Jim     CS_2      Book
1   9   Paul    CS_3      Journal
2   9   Jim     CS_3      Journal
1   10  Paul    G_CS_1    Book
1   11  Paul    G_CS_2    Journal
2   11  Jim     G_CS_2    Journal
1   12  Paul    G_CS_3    Journal
2   12  Jim     G_CS_3    Journal
3   13  Mary    G_M_1     Journal
1   14  Paul    G_M_2     Journal
3   14  Mary    G_M_2     Journal
2   18  Jim     E_CS_1    Journal
4   18  Fred    E_CS_1    Journal
4   19  Fred    E_CS_2    Book
2   20  Jim     E_CS_3    Journal
1   21  Paul    G_M_CS_1  Book
2   21  Jim     G_M_CS_1  Book
3   21  Mary    G_M_CS_1  Book

Who are the authors of and what is the title of works with more than 2 authors - all as 1 record?

SELECT 
  STRING_AGG(DISTINCT a.author_name, ',') AS names, 
  w.work_title AS title
FROM work_author wa
JOIN work_subject ws
  ON wa.work_id = ws.work_id
JOIN author a 
  ON wa.author_id = a.author_id
JOIN work w
  ON wa.work_id = w.work_id
WHERE wa.work_id IN
(
  SELECT wa.work_id
  FROM work_author wa
  GROUP BY wa.work_id
  HAVING COUNT(wa.work_id) > 2
)
GROUP BY title;

Result:

names           title
Jim,Mary,Paul   G_M_CS_1

I hope this clarifies my meaning in the comments above. If not, please let me know and I will endeavour to clarify! I can't think of any reasonable question about works and authors which cannot be answered using this schema - the key is the two tables work_author and work_subject using which you can JOIN any fields of interest.

With respect to your questions about consistency, the FOREIGN KEY constraints that I have created in the work_author and work_subject tables mean that you can't have inconsistent data.

Of course, errors can creep in. You can have authors without works (maybe this is OK for unpublished - as of yet - workers in a field, depends on your requirements) and a work can have no authors or subjects if that data hasn't been input. You can, of course, run queries to check for this and rectify the situation - possibly a daily or weekly report?

You ask in your comment here - please tell me what this kind of inconsistency called?. I call it a childless parent - DRI (Declarative Referential Integrity) constraints make it impossible to have parent-less children. A trigger solution could help prevent this sort of error occurring, as could running periodical reports.