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 )

Note that above program reads all files from a particular folder and copy to another backup folder after zipping it. You can customize it by choosing only particular file types. This script can be automated by calling from a shell script or control-m job. it will create new folder every day.

Share this:

Like this:

How to create a analog clock in python ?. Need little bit of mathematical knowledge.
This is the place you can brush your knowledge in Sin and Cos(Mathematical functions)
Learn more on “Polar coordinate system” and understand the fundamentals.

Share this:

Like this:

This post we will examine how to get the parameter to the caller (UNIX). ie OUT Oracle parameter will show in UNIX shell script

For example how to find total number of employees and display in shell script

— Below stored procedure returns total number of employees
create or replace procedure p_get_count(p_emp_count out integer)
as
begin
select count(*) into p_emp_count from emp;
exception
when others then
dbms_output.put_line(sqlerrm);
end p_get_count;

Share this:

Like this:

Java developers always have tough time understanding SQL implementation. SQL is straight forward, simple and easy to write. Compared to SQL, java is very complex. Java developers are highly obsessed with objects, they think every thing should be object oriented. Seen many java developers design/create tables after designing/creating the UI screen, actually it should be other way around.

Let us examine some mistakes they make while developing data-centric applications

Best Practice :- Always apply rownum after order the rows. Note that do not use order by and rownum in the same where clause, use in each level of queries.

2. Pagination in Java memory

Java developers do pagination in Java instead in database side. Load all data into memory and paginating is a time-consuming process especially for large data sets. So if possible apply pagination before it loads into Java.

select * from
(
select empno,empname,salary,row_number() over ( order by salary desc ) rk from emp
)
where rk between 3 and 6;

Best Practice :- If possible use pagination in database level itself

3. Scare to use database procedures or packages from Java

Many Java developers scare to use database procedures or packages for processing the business logic. Instead they use standalone queries either directly in java code or in xml files. This is inefficient and use more network round trips. Some believe that stored procedures downgrade the performance which is not true.

Best Practice :- To write data centric business logic in database procedures rather than exposing direct queries in Java.

4. Loading all data into java memory

Seen in many java applications loading huge data into the memory (java cache) and process whenever require. This negatively impact the performance and increase the overhead of the applications.

Best Practice :- Load data into memory only when require.

5. Using separate INSERT and UPDATE instead of MERGE statement

Many java developers (who use Oracle as their database) does not know there is a MERGE SQL command which will combine INSERT and UPDATE into one single SQL