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 )