Inheritance of "dictionary tables"

Question

(In case this matters: I'm using SQLite)

My silly little database has a surprising number of tables with identical structure:

CREATE TABLE GTag (
    ID            INTEGER PRIMARY KEY NOT NULL,
    Name          TEXT    UNIQUE      NOT NULL,
    Description   TEXT
);

Back here (Poland) they're known as "dictionary tables" ("tabele słownikowe"); I couldn't find the proper English equivalent. Their role is to provide a limited number of pre-determined choices, usually in drop-down boxes and the like. Storing tags seems like the sort of thing they're designed to handle.

As I'm finalizing the structure of my database, it struck me that all the tags and other tag-like entities (like "GStatus", "SStatus"...) have these exact 3 fields in common, yet are not connected to one another in any way. Should I make them all sub-types of a more general "Tag" table?

I see 3 solutions to my predicament:

• Create a super-type with ID, Name, Description. All current tables become EMPTY save for primary ID keys that correspond to Key.IDs (i.e: are foreign keys). If I want to lookup all the GTags, I get all IDs from GTag, and then get data from corresponding Tag entries. The sub-types, however, bring completely nothing new to the picture. They don't have any fields that would set them apart from one another or even the parent type.
• Dump everything into one big Tag super-table; add Type field. Since I NEVER want tags from more than on category at the same time, all queries will have to filter via the Type field. Seems clunky like hell, and a huge headache of a rewrite.
• Leave things well enough alone. I avoid creating useless entities that have all structure but no meaning in common.

I get the impression that trying to "tidy up" my schema will ultimately result in tangling it up into a confusing mess, but I don't have the experience to say that with conviction. What am I missing here? What possible benefits (and drawbacks) does each solution offer (in terms of performance, neatness, following design principles...), and which should I ultimately implement?

Answer 1

Using one supertable Tag will give you less tables in your schema, but you will basically lose guarantees about referential integrity. If one column in another table must have one 'colour' value, you add a referential constraint to the table 'colours' (one of your dictionaries). If you have a big one, you can have one column that should have one colour to have a value 'wide', because this value is also in your dictionary, even if it is not a colour.

With one super-table, you move the responsibility of guaranteeing referential integrity from the database to your application (or middleware). If you do so, you should make sure there is no other way to change values in your database than through your application.

I don't see any advantage on having an inheritance hierarchy. Even if your structures look all the same, their meanings are not at all the same. You could gain some operational advantage somewhere (like having only one procedure to do the 'maintenance' of the Tag table), but I guess you could do the same using just table names as parameters to a function call or method somewhere in your application.

I'd not really care much about performance. Having a number of small 'dictionary' tables (lookup tables) won't harm you.