How to design a database table

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.

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’


below table structure in Oracle database


Also let us answer below questions

  1. Purpose of table :-  To store 50 states and territories of USA
  2. Is it a Master table :- Yes
  3. Table has a primary key – Yes
  4. Table has a business key – Yes
  5. Table has a toggle key(delete indicator) – Yes
  6. Is it normalized – Yes

( disclaimer :- The above example is only to explain the basic table creation guidelines )


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: