How to deal with lots of parameters in database?

Question

Let's say you need to store 4 different document types somewhere (i.e, draft, sent for approval, approved, rejected).

You can create a new table for that and store it there. But it seems a bit excessive to create a table just for 4 entries.

Another approach is to create a one big table for all parameters in general and store them there. So if you ever in a need of storing 4, 15 or 2000 new parameters, you can just simply insert them into the Parameters table and store them there all together and not create a new table.

But what if the number of parameters gets bigger? Let's say, 1.000.000, 5.000.000 or even more? What would be the best approach then?

I'm not talking about the particular database but if that would help, it's either Oracle 12c or OpenEdge (Progress) database.

Answer 1

But it seems a bit excessive to create a table just for 4 entries.

Work with well-designed databases for a while, and it won't seem excessive.

The main problem with one big "parameters" table is that foreign keys can reference any unique row, not just the rows you want. For example, if you had one big table like this . . .

draft
sent for approval
approved
rejected
USA
Great Britain
California
...

. . . then sooner or later you'll have a document whose type is "California".

But this stems from a fundamental misunderstanding of the relational model. In the relational model, a domain consists of all possible values for a particular attribute. There are only four possible values for the "document type" attribute. For reliable data, the dbms needs to know that. And usually the best way to tell the dbms there are only four possible values is to set a foreign key reference to a column that contains only those four values.

It's an anti-pattern. Search online for "one true lookup table" or "OTLT".