How to handle variations of the same data point?

Question

I have a database with three tables:

• books,
• authors and
• books_authors

books_authors is a “junction” table (i.e., it represents a conceptual level many-to-many relationship), so if it has a row with book_id=1 and author_id=5, it means that author 5 is an author of book 1.

Now, the problem is that author names are sometimes written differently depending on the book. For example, in book 1 the name of author 5 is written “洋子”, but in book 2 the name of author 5 is written “ようこ”.

Sometimes these are variations of the same name (ようこ = 洋子) but sometimes they are completely different (e.g., an author changes his or her name after getting married.)

What's the best way to retain these name variations? Should I enter separate rows for the values “洋子” and “ようこ”, and then link them with another ID? Or should I make an array to keep all the variations in a single row?

Additional information

In response to some clarification requests made via comments, I am going to explain several significant aspects in order to contextualize the scenario more fully:

• Authors can actually have six separate name values (First, First-reading, Last, Last-Reading, Full, Full-Reading) but the only one required is Full. The rest "can be" null.

• An Author does not have a, say, "base name" in the business domain of interest.

• I retain Author Names only when they are associated with a Book.

• Authors can be Artists, Editors, etc. I use (what I call) the "junction" table to keep track of which Role they play for each Book.

• I think that, usually, multiple Name Variations represent different values of the same domain, as in the case of “ようこ=洋子”, but I believe that sometimes they are different domains (e.g., as in the situation mentioned above, when an Author changes their Last Name after getting married).

• I want to manipulate the corresponding tables via SELECT operations that, e.g., include the column(s) containing the Authors Names as (a) condition(s) in the WHERE clauses.

• The full properties of interest regarding an Author are just the six Name (what I call) "fields", their Variants, and the Books with which they are associated.

• The property whose values uniquely identify an Author instance is only the ID, as far as I can tell.

---

Note: I'm using Postgres but this question could apply to other relational database management systems.

Answer 1

I may just be repeating @jeffmc's answer, but I would add an author_names table that has a one-to-many relationship with authors, and then make your junction table (books_authors) a junction of books and author_names. (For clarity you may want to rename it books_author_names.) This way you are able to group authors by all their various identities/pseudonyms while being to tell exactly which moniker was used in the publication of a specific book.

I think this is a more precise way of doing it than using a time-based structure, as an author may use multiple identities simultaneously (e.g., Stephen King published both as Richard Bachman and Stephen King for several years).

Answer 2

Well, perhaps the way to think about it is in terms of the relations you need.

As stated, you must maintain the book 1 / author 5 relation, and the book 2 / author 5 relation.

This is an example of an aliasing problem.

One way to solve the problem would be to create an author_alias table that contains id pairs of the authors row.

Maintain the usual book / author relation. This means you will have 2 rows in authors, say 5 and 8; one for each variant of the authors name. From the point of view of the DB, they are separate authors.

Then add another fact in the model by making an entry in author_alias connoting the alias between author id 5 and author id 8.

Now you can query either way by joining on the ids to find the aliases each author name can be expressed as.

Answer 3

It may be of interest to know when an author used a certain name. You could then add a temporal table:

CREATE TABLE AUTHOR_NAME_HISTORY
( AUTHOR_ID ...
      REFERENCES AUTHOR (AUTHOR_ID)
, NAME ... NOT NULL
, START TIMESTAMP NOT NULL
, END TIMESTAMP -- NULL means it has not ended
,   PRIMARY KEY (AUTHOR_ID, START) );

Some DBMS have built-in temporal support. This is an example from DB2:

CREATE TABLE AUTHOR_NAME_HISTORY
( AUTHOR_ID ...
      REFERENCES AUTHOR (AUTHOR_ID)
, NAME ... NOT NULL
, START TIMESTAMP NOT NULL
, END TIMESTAMP -- NULL means it has not ended
, PERIOD BUSINESS_TIME(START, END)
,   PRIMARY KEY (AUTHOR_ID, business_time without overlaps) ) ;

To get all names for an author:

SELECT NAME 
FROM AUTHOR_NAME_HISTORY
WHERE AUTHOR_ID = ? 
ORDER BY START

To get the name of an author at a particular point in time:

SELECT NAME 
FROM AUTHOR_NAME_HISTORY
WHERE ? BETWEEN START AND COALESCE(END, CURRENT_TIMESTAMP)
  AND AUTHOR_ID = ?;