Single enumeration table or multiple MySQL

Question

Similar to this question: Is it better to have large tables or many tables (MySQL)

I am attempting to rework part of a database architecture and I noticed that there is a lot of small tables (2-8 records) that contain enumeration data (e.g. dynamic form options). I am trying to make a decision between creating new tables with more enumerations or to create a single central "enumeration table" that contains all of them with a look-up key. Similar to this:

create table options_table (
 type NVARCHAR,
 value NVARCHAR,
 description NVARCHAR
);

And the value would be similar to:

('print_options', '1', '5x5')
('print_options', '2', '10x10')
('exam_type', 'o', 'Online')
('exam_type', 'i', 'In Person')

These values would of course join to the main table where the core information is kept.

Should this kind of information be included in the main table, a side table, or multiple side tables? What is the most efficient implementation for the database in terms of speed/storage?

I suspect a single table with all options would be the easiest implementation since otherwise we might have a database of around 50 tables and around 100 enumeration style tables.

---

It's the backend for a website, the number of users (across the entire website) is around 5-15,000. We handle regularly thousands of records with the possibility of hundreds of thousands (maybe low millions? But the data is regularly cleaned). The options table I mentioned, I cannot ever see it going above 1000 individual records. Hardware is "OK" but not great (can scale if needed but outdated).

Answer 1

You can simply merge them into one table, as long as you ensure that the data does not conflict. But the purpose of the enumeration type is to make the meaning of the field value more clear, otherwise you can just use numbers.

So merging them together, you have to rethink the meaning setting, which is more hassle.

If you only look at the execution performance, spreading to dozens of small tables will not cause performance issues

Answer 2

It will work. But I recommend changing the names, datatypes and indexing:

create table options_table (
 `type` VARCHAR(20) NOT NULL, -- keep it short
 `value` VARCHAR(20) NOT NULL, -- keep it short
 description VARCHAR(999) NOT NULL,
 PRIMARY KEY(type, value) -- probably the only index needed
) ENGINE=InnoDB;

I did something like that once in 30 years. I used description to make some web pages more user-friendly.

One table (and 2-column JOIN) versus many tables (and 1-column JOINs) -- very little performance difference.

See also ENUM and SET and FIND_IN_SET().

• Fatih Mar: shouldn't this line PRIMARY KEY(type, value) be PRIMARY KEY(grouping, key) instead?