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.

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

state_table

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 )

 

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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