Designing a Software Comes with 2 phases Front-end and Back-end. Front-end is nothing but the area where through user interacts with data. While Back-end is responsible to store and organize data. In real-time it was observed Compare to Front-end development Back-end development is more sensitive. This is the cause while dealing with database we required experienced professionals. From the chapter of System designing well structured database makes job easier. Planned Database performs better with Accuracy. Always it’s wise to plan database before designing a Software. This guide is Specially designed for Database Developers to train them in Database design. Here assume that we are going to design database for an Employee Management System (EMS).
Database Terms
Table in a database used to store records. Table operates in fashion like a grid. Which Stores data using row and column. Each row of a table is Called record. An individual column in each row is called field.
In a table Primary key is the unique id for each record. Primary key never carry a null value. In Database design it helps to establish relationship among tables. Let’s talk about Employee Details table. Here emp_id is the primary key. While establishing relationship with Manager Details table here emp_id works like a Foreign key. Foreign key is also called Referral key. Using Foreign key relationship in Manager Details table you can insert only those records who are having emp_id. Its mean “A Manager must be an Employee”.
Purpose of Database design
During we design a POC kind of application sometimes we may required 1 or 2 SQL tables. But while designing a System there we required many tables. Let’s talk about an Employee Management System, here depending upon the functionalities we required various kind of tables. For an example to Store Employee Details you can Create “EMS_EmployeeDetails” table or to Store Manager Details you can Create “EMS_ManagerDetails” table.
The main idea behind the Database design is to reduce redundancy of data. Duplicate data take unwanted space as well it increases more chance of errors and inconsistencies. In real-time while designing a database always try to avoid same data in two columns. Similarly if “EMS_EmployeeDetails” table contains employee name no where else you required to store employee name. It is a good practice to use data centrally.
Naming Standard
Naming Standard is the basic stage of Database design. Before plan a Database decide which naming culture you will follow. For an example in our Employee Management System I decided to keep Database name as EMS and table names are EMS_*. Here EMP_ is my prefix. In case I am going to write a stored procedure I can use EMP_SP_*. Here EMP_SP_ is the prefix for Stored procedures. Keep remember once the database is used with application it is very time consuming job to update a table name. So decide naming pattern well before into real-time.
Analyze the System and Data Behavior
After Successfully Create a Database the next step is to Create Tables. Divides your information into subject-based tables. This practice help to reduce redundancy of data. Before start designing tables in real-time first draw data diagrams. Where represent the type of tables you required for your System. For an example is a employee management system we required few tables like EMS_EmployeeDetails, EMS_Login, EMS_Feedback, EMS_Managers, EMS_Country or EMS_Salary. Draw this tables with field names. While Creating a field name decide its datatype depending upon the nature of form filed data.
Understood the data behaviors and relationship. While Creating data diagrams establish relationship to check whether there is any conflicts. If every thing goes fine Create tables. While planning Columns for a table choose information nodes. For an example in a registered user table you can have columns like First Name, Last Name, Mobile Number, Email ID or DOB.
Don’t include Calculated data in Columns. I mean to say if you want to know the total duration an Employee work with your Company you can take the difference of “Date of Joining” and “Current Date”. In this case there is no need to store job duration in a separate Column. In real-time data with similar behavior creates confusion.
Defined Primary key and Relationship
Once you are ready with Tables now it the time to decide primary key for your tables. Primary key defines uniqueness of each records. For an example in “EMS_EmployeeDetails” table you can define “Emp_id” as the Primary key. While defining Primary key its a good practice to set it as an auto increment field.
Establish relationship among tables. Refer to relational database management you can establish 4 types of relationship among tables. One-to-Many, Many-to-Many, One-to-One or Many-to-One. Let’s take an real-time example after employee registration to verify his/her email I have an another table EMS_VerifyEmployee. In this table Emp_id is the identification for Employee. Depending upon the verification status I am updating an another boolean filed Emp_Verification.
Common Database design Rules
- At the end of Database design check whether you miss out any column or not. If you found there is a missing column update the required table. Before into real-time verify columns datatype again.
- Check is there any unwanted column you added in your database. If so remove it. In-case you are able to get an information by calculating base data don’t create a separate column for calculated data.
- If you are entering duplicate data to one table repeatedly split the table in to 2 tables. Use relationship to mapping data.
- Maintain Similar Datatypes for all date fields.
- During development sample data works great. Before complete the database design Create few Stored procedures like “To Truncate all Tables” or “To insert demo Data”. For an example if I have a Country list table in my EMS Database then I can create list of insert queries for each country details. While installing database in a new system at that time to re-load list of countries these queries helps.
Apply the rules of Normalization
First normal form says at each row and column intersection in the table there, exists a single value, and never a list of values.
Second normal form says that each non-key column be fully dependent on the entire primary key, not on just part of the key. This rule is applicable for primary key that consists of more than one column.
Third normal form says that not only every non-key column be dependent on the entire primary key, but that non-key columns be independent of each other.
