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.
https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States
At moment there are 50 entries.
| STATE_NAME | STATE_CODE |
| Alabama | AL |
| Alaska | AK |
| Arizona | AZ |
| Arkansas | AR |
| California | CA |
| Colorado | CO |
| Connecticut | CT |
| Delaware | DE |
| Florida | FL |
| Georgia | GA |
| Hawaii | HI |
| Idaho | ID |
| Illinois | IL |
| Indiana | IN |
| Iowa | IA |
| Kansas | KS |
| Kentucky | KY |
| Louisiana | LA |
| Maine | ME |
| Maryland | MD |
| Massachusetts | MA |
| Michigan | MI |
| Minnesota | MN |
| Mississippi | MS |
| Missouri | MO |
| Montana | MT |
| Nebraska | NE |
| Nevada | NV |
| NewHampshire | NH |
| NewJersey | NJ |
| NewMexico | NM |
| NewYork | NY |
| NorthCarolina | NC |
| NorthDakota | ND |
| Ohio | OH |
| Oklahoma | OK |
| Oregon | OR |
| Pennsylvania | PA |
| RhodeIsland | RI |
| SouthCarolina | SC |
| SouthDakota | SD |
| Tennessee | TN |
| Texas | TX |
| Utah | UT |
| Vermont | VT |
| Virginia | VA |
| Washington | WA |
| WestVirginia | WV |
| Wisconsin | WI |
| Wyoming | WY |
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.
Business key
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’
| STATE | TABLE_NAME | |
| STATE_ID | INTEGER | PRIMARY KEY |
| STATE_NAME | VARCHAR2(30) | |
| STATE_CODE | VARCHAR2(2) | BUSINESS KEY |
| 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 )
