How to design a database table. There is no common answer, but we can discuss some best practices.
Let us start with a simple example, a config or master table for saving all states and territories of Unites States.
At moment there are 50 entries.
Here we have state_name and state_code. This is the actual data. Now we have to identify/add primary key (a column which identifies each row uniquely ). Here either we can use state_code as primary key. But more appropriately we can have another column state_id ( as a number data type )
Below are some basic rules(best practices) to follow while creating a database table
Primary Key or Technical Key
Every tables should have a primary key or technical key ( if possible ) . Primary key is referred as Technical key because it is generally for technical purpose ( Mainly for IT team )
Here STATE_ID number will be added as a primary column.
It is a best practice to identify/create a business key it this table is used by business team. Some scenarios primary key will act as a business key. In our example STATE_CODE will be our business key and business team will always refer with this column only
Soft Delete Indicator
Another best practice to have a soft delete indicator. Here we should add another column DELETE_IND to store Y or N ( or you can use 1 or 0 also )
Why we need DELETE_IND. Suppose we need to remove operation/business of a particular state. We can do this by setting DELETE_IND ‘Y’
|DELETE_IND||VARCHAR2(1)||SOFT DELETE KEY|
below table structure in Oracle database
Also let us answer below questions
- Purpose of table :- To store 50 states and territories of USA
- Is it a Master table :- Yes
- Table has a primary key – Yes
- Table has a business key – Yes
- Table has a toggle key(delete indicator) – Yes
- Is it normalized – Yes
( disclaimer :- The above example is only to explain the basic table creation guidelines )