How to make Custom attributes for SQL table

What is the proper way to give user to use custom attributes ?

As an example
Consider that, There is a web application, using the frontend user interface admin user need to be able to add custom attributes to the Employee later. Which means not only for specific employee record but also for all employees.

Initially, There is an employee table which has following fields,

| Employee |
|----------|
| ID       |
| Name     |
| Email    |

Later, system admin want to add few custom fields (attribute) to Employee table such as Nationality, Mobile Number, Address.

Is it good idea to alter the table and add new column to it ? or Is there any proper way to do this.

Currently, I am working on the ER diagram of database and hope to use Postgresql or MySQL to implement it.

Thanks !

Answer

There are multiple approaches to do what you want:

• Add new columns to the given table.
• Create a new table with additional attributes.
• Use a JSON column in the table to handle flexible new attributes.
• Create an entity-attribute-value table, with one row per entity attribute, for the flexible attributes.

How do you choose among these? It depends on factors that you have not discussed in the question. These include:

1. Do all entities have the same attributes?
2. Can you take the table offline to change its structure?
3. How large are the tables and how wide the rows? And are these issues with query performance?
4. How often will new columns be added?

There may be more considerations, and there may be more possible solutions. The point is that there is no generic right answer. Different solutions have different strengths and weaknesses.