System Const Design

First Problem

Suppose in a system we want to store consts. For example, in CUSTOMER table have TYPE column specify Normal customer or Vip customer.

Problem: What if in the future we want to change name of Normal customer to Standard customer. Update all rows of CUSTOMER table is a bad idea.

Solution: Create another table CONST with columns (Id, Key, Value) have rows (1, N, Normal) and (2, V, Vip) and CUSTOMER.TYPE contains values 'N', 'V'. So for change name of type, we only need to change (1, N, Normal) to (1, N, Standard).

Second Problem

What if we want to add language depend for CONST table.

Solution: Make a CONST table with columns (Id, Key, Value, Language).

Make sure to Language column of CONST table follow ISO 639-1 standard.

Another solution for Second Problem

Create CONST(Id, Key) table and TRANSLATION(Id, Language, Value) with relation one row of table CONST have many rows of table TRANSLATION

Bonus

Create table CONST(Id, Key, Value, Language, Description, Namespace) with Description to store information about const and namespace to group related consts.

Adding a function getConst(Key, Language) for get value of key depend on languge. Make sure to handle case when Key, Language input don't exist on CONST table.

Example

SELECT Id, Name, getConst(Type, 'en') from CUSTOMER;

SELECT Id, Name, co.VALUE from CUSTOMER cu LEFT JOIN CONST co on cu.TYPE = co.KEY and co.LANGUAGE = 'en';