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

How to call stored procedure with argument(OUT parameter) from Unix shell script

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;

Next constructing the shell script

!/bin/ksh
#Purpose : Get employee count
#Name : get_count.sh
#Argument : p_emp_count(integer) as return variable

export DB_USERID=”SCOTT”
export DB_PWD=”TIGER”
export DB_SID=”ORCL”
ORACONN=”$DB_USERID/$DB_PWD@$DB_SID”

# Main Section
echo “program started…”
db_output=`sqlplus -s $ORACONN<<END
SET SERVEROUTPUT ON
declare
n_count integer;
begin
p_get_count(n_count);
dbms_output.put_line(n_count);
end;
/
exit;
END`
echo “Employee count=$db_output”
echo “program ended…”

Run the shell script get_count.sh

sqlandplsql$ sh get_count.sh
program started…
Employee count=8

PL/SQL procedure successfully completed.
program ended…
sqlandplsql$

Total there are 8 employees

Things to remember

  1. Always use error handling in stored procedures
  2. Add appropriate logs both in PL/SQL and Shell Script
  3. Add proper comments

Note:- Very much simple example have used to explain the fundamentals

Also read

How to call stored procedure with argument(IN parameter) from Unix shell script

 

 

How to call stored procedure with argument(IN parameter) from Unix shell script

In DEPT table we have 5 records and we need to add a new Traveling department based out in Boston

dept

Let us see how we can achieve this through unix shell prompt.

First create a stored procedure to accept two IN arguments and a return flag as OUT parameter

— Below procedure insert new record into dept table
— returns Y is success and N for failure
create or replace procedure p_set_dept
(
deptname in varchar2,
deptlocation in varchar2,
success_flag out varchar2
)
as
n_deptno integer;
begin
— finds max deptno
select nvl(max(deptno),0)+1 into n_deptno from dept;
— insert script
insert into dept values(n_deptno,deptname,deptlocation);
commit;
success_flag := ‘Y’;
exception
when others then
success_flag := ‘N’;
dbms_output.put_line(sqlerrm);
end p_set_dept;

Next to create a shell script to call above procedure

#!/bin/ksh

#Purpose : Add new department
#Name : set_dept.sh
#Argument : p_dname(string) and p_dloc(string)

export DB_USERID=”SCOTT”
export DB_PWD=”TIGER”
export DB_SID=”ORCL”
ORACONN=”$DB_USERID/$DB_PWD@$DB_SID”

p_dname=$1
p_dloc=$2

function set_dept
{
p_deptname=$1
p_deptloc=$2
db_output=`sqlplus -s $ORACONN<<END
SET SERVEROUTPUT ON
declare
v_success_flag varchar2(1);
begin
p_set_dept(‘$p_deptname’,’$p_deptloc’,v_success_flag);
dbms_output.put_line(v_success_flag);
end;
/
exit;
END`
db_status=`echo $db_output | cut -b 1`
}

# Main Section
echo “program set_dept.sh started…”
set_dept $p_dname $p_dloc
if [ $db_status = “Y” ];
then
echo “Successfully ran set_dept.sh”
else
echo “Error in set_dept.sh –>$db_output”
exit 1
fi
echo “program set_dept.sh ended…”

Now we have both stored procedure and shell script. Let us execute and see

sqlandplsql$ sh set_dept.sh “Travel” “Boston”
program set_dept.sh started…
Successfully ran set_dept.sh
program set_dept.sh ended…

One record got inserted.

dept_after_insert

Trying to add same department again( Note that deptname column in DEPT table has a unique constraint).
Program correctly captures the error and displays in the console.

sqlandplsql$ sh set_dept.sh “Travel” “Boston”
program set_dept.sh started…
Error in set_dept.sh
Error=ORA-00001: unique constraint (CUPID_DEV.UQ_DEPT) violated
N
PL/SQL procedure successfully completed

Set_dept_shell

Similarly developers can call any type of procedures with different argument types.

Things to remember:-
1. Catch the return type from Oracle to Unix
2. Print the error if any
3. Capture all logs ( note that above program does not have logs )

 

Did find this article useful ? Please comment.

 

5 common mistakes Java Developers make when writing SQL

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

1. Using ROWNUM and ORDER BY CLAUSE together 

Example:-

orderby1

To find lowest salary employee, some java developers write like below

select * from emp where rownum = 1 order by salary asc ;  — wrong query

Surrely above query will attract logical bugs.

See the result

rownum_orderby_wrong

The result is wrong. Please use the correct query as given below

select * from (select * from emp order by salary asc) where rownum = 1 — correct query 

rownum_orderby_correct

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.

Example :- Top 5 salaried employees

select * from
(
select empno,empname,salary,row_number() over ( order by salary desc ) rk from emp
)
where rk <= 5;

Example :- 3rd to 6th top salaried employees

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

a) INSERT statement

insert into emp (empno,empname,salary,deptno) values(19,’JOHN’,7800,5);

b) UPDATE statement

update emp set empname = ‘JOHNSON’where empno  = 19;

c) MERGE statement

MERGE INTO emp e
USING (select 19 as empno,’JOHNSON’ as empname,7800 salary,5 as deptno from dual) a
ON (e.empno = a.empno)
WHEN MATCHED THEN
UPDATE SET e.empname = a.empname,e.salary = a.salary, e.deptno = a.deptno
WHEN NOT MATCHED THEN
INSERT (empno, empname,salary, deptno)
VALUES (a.empno, a.empname,a.salary, a.deptno);

see the result.

merge_statement

get and set method can be combined if properly coded

Best Practice :- Try to combine insert and update SQL statements using merge SQL statement (Only in Oracle )

 

Some more common mistakes…………

a) SQL NULL and Java NULL implementation is not same.

b) Unnecessary usage of multiple distinct clases in each union query

c) UNION and UNION ALL confusions

d) Write multiple SQL even though they can be written into one single SQL

 

 

 

5 different ways to find TOP N queries

5 different ways to find TOP N queries

Let us write five different queries to list the top 4 highest paid employees from emp table.

1) Using ROWNUM
2) Using ROW_NUMBER()
3) Using RANK()
4) Using DENSE_RANK()
5) Using MINUS

rownum1

1) Using ROWNUM

select * from (select empno,salary,rownum as rk from emp order by salary desc) where rk < 5;

rownum_topN

2) Using ROW_NUMBER()

select * from (select empno,salary,row_number() over(order by salary desc) as rk from emp) where rk < 5;

rownumber_topN

3) Using RANK()

select * from (select empno,salary,rank() over(order by salary desc) as rk from emp) where rk < 5;

rank_topN

4) Using DENSE_RANK()

select * from (select empno,salary,dense_rank() over(order by salary desc) as rk from emp) where rk < 5;

denserank_topN

Note that dense_rank will return multiple records if there is a tie.

5) Using MINUS

select empno,salary from emp
minus
select empno,salary from (select empno,salary,rownum as rk from emp order by salary desc) where rk > 4;

minus_topN

You can also read – Nth highest salary 

Rownum and order by in oracle

ROWNUM

ROWNUM is a psuedocolumn to return the row number of the row in a table. The order will be based on how oracle selects a row from table.

rownum1

Examples with rownum

rownum2

rownum3

Note that rownum will be decided by Oracle itself ( Oracle will assign a number based on how it retrieves from storage/memory)

Order by

Order by is a sql clause which used to order a selected column

Example :-

orderby1

Order by and Rownum together

Why we should use rownum and Order by clause together ? Many experienced developers ( Mainly java developers ) think they can write rownum and order by in same where clause to get the top N query.

For example – To find lowest salary employee developers write like below

select * from emp where rownum = 1 order by salary asc ; —  wrong query

see the result

rownum_orderby_wrong.PNG

Result is wrong.  It should be salary 7500 ( Empno – 14 )

Why ? Oracle applies the ROWNUM first and then applies the order by clause.

See the correct query below

rownum_orderby_correct

So always apply the order by and in next level apply the rownum.

To find a lowest salary employee :-

select * from emp where rownum = 1 order by salary asc ; —  wrong query

select * from (select * from emp order by salary asc) where rownum = 1 — correct query 

Few more tips about rownum

  1. Do not write ROWNUM = 0 ( There is no record having ROWNUM = 0)
  2. Do not write ROWNUM <= 1 instead write ROWNUM = 1
  3. In general Oracle assigns ROWNUM while retrieving the records, it is not based on Primary key column
  4. Do not write ROWNUM > 1, which will no yield any results
  5. ROWNUM and ROW_NUMBER() is not same.

Please comment.