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 )