What Basic Points Should Be Remember Before Designing A Database

So you have got a new project in your company or you got a deal with a new client and you acquired all the requirements of the client that what he wants in his software, like what functionality, process, data entry and reports etc.

Now finally when you got the project the first thing is came into mind is the design of the database, which is very important for every software. Below I am describing some tips for a better database design.

Database Design

First thing, do not try to make it complex design, do not think that the requirement is very typical then database would also be typical, no think that database can be a very simple with good efficient design and for this plan the database, its normalization form and the result will come.

Tables Creation

Tables design is also very important, plan what table size could be in future, if table data would be very huge or table is used to import external data then table should be partitioned for better performance, as I discussed in my post Determining if a Table Should be Partitioned in Oracle.

Fields Creation

Plan field length properly means field length should be the maximum length required for a particular field. Plan indexes properly what field or fields should use index unique or non-unique. Try to plan summary column fields to put in tables and process them for each entry and also try to keep formula column field in a table for not to computing formula in queries but directly into the table, specially for complex formulas for which you are planing to use functions in query.

Packages, Procedures and Functions

In an application there is a particular task is there to perform and for that particular task there is requirement to write many procedures and functions for that. In that case you should write a package to accumulate all the procedures and functions and global variable to put in that, so that the calling would be very logical and meaningful. But do not try to write package for the procedures or functions called rarely, because for a single procedure or function the whole package will load in memory and decrease the performance, so for those procedure and functions you can write separately.

1 thought on “What Basic Points Should Be Remember Before Designing A Database”

Javed Akram
July 24, 2016 at 2:23 pm
Hi Friends
I want to validate the entered date from another table through oracle forms.
For Example
I have a table holidays (transaction_no,holiday_name,from_date,to_date)
and i am entering the date in employee_attendance.... where user entered date should be verify that is not exist between holidays.from_date and holidays.to_date at run time
If user entered Employee_Attendance.attendance_date which is exist between holidays.from_date and holidays.to_date then show message "This is holiday, please another date"
Please help me or resolve my issue

Comments are closed.