database design: link table for multiple different tables

Question

I have a conceptual question about database design which came up multiple times in my history as a developer. Imagine I have a bigger database that is designed in the past, and already in production (for example you can take http://sqlfiddle.com/#!9/7c06f/1, a library database).

Now there is a new feature request: for every existing object in every table there shall be a "help text" (or something different, an error, a tag...) that you can all view in one place.

I implemented something like that multiple times in the past, but every time I'm not satisfied with the solution.

One solution is to have a link table with every table, like that:

CREATE TABLE BooksHelp (
    BookId  INT NOT NULL,
    HelpText VARCHAR NOT NULL
);


CREATE TABLE AuthorHelp (
    AuthorId  INT NOT NULL,
    HelpText VARCHAR NOT NULL
);

...

But I would need multiple link tables, which makes the selection of every existing "help text" difficult.

How would you design this problem? Is there another, better solution?

I would probably add the "helptext" column to each table and create a view that does a UNION to display all of them or function that inspects all tables and uses dynamic SQL to return that information (but you don't have that option if you use MySQL) — , Jan 14 '20 at 14:41

score 0 · Answer 1 · answered Jan 16 '20 at 11:41

In the case of text items of some sort, especially if they are used in several tybles, like help texts, it seems preferable to centralize their text data in one table and store references in the tables that need them. This design also supports multi-lingual apps where the text items would have to be maintained in several languages.

score 0 · Accepted Answer · answered Jan 16 '20 at 12:32

This appears to be similar trying to map polymorphism to the relational model - it's just a bad fit - there's no obvious right answer.

There are a few obvious solutions. The one you've identified (storing the helptext in a linked table) is neat, but requires lots of joins if you're retrieving all the books for an author belonging to a publisher etc. As the business logic says "all objects should have helptext" (and I assume each row has different helptext), it's not super logical to store this in a child table - "helptext" is an attribute of each object, not a related concept.

You could also add a helptext column to each table. That stores the attribute in the main table, and reduces the cognitive load (as well as the number of joins). This is logical if each author, book, etc. has their own helptext.

database design: link table for multiple different tables

2 Answers2