(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,
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?