The following is an ordered list of steps you can follow to create a good database design for your web application.
Step 1- Create a charter for the database
First keep in your mind why you need to create a database and what you are expecting it to do. Database designers sometimes fall into trouble when.
1. They assume that the data exists for its own sake
2. They assume that the database exists for the sake of the Information Technology department.
Both the above are wrong, and the database exists for its users. Before you design your database, find out why the users of your application need the database and what the users expect to accomplish from it. You can think of this purpose statement as a mission statement or a charter for the database.
The more complicated and complex your application is, the more detailed your Charter will be. But always limit your charter to the key points that identify the unique capabilities that the user expects from the database.
The most vital part of this step is to examine how the data is currently being stored and to uncover the weaknesses. If the data is currently being stored in an Excel spreadsheet or is stored in paper form in file cabinets, carefully examine them to see what kinds of data are included in them.
Step 2 – Make a list and double check it
Once you finish Step 1, and you are done with your Charter, start listing the major tables of the database you are going to create.
When you have come up with a rough list of tables and the data items, always keep in mind them as entities that are like real-world objects that the database needs to keep track of.
Step 3 – Add Keys
For every database you create, there should be a column or combination of columns that uniquely identifies each row in a table. This column or combination of columns is also known as the Primary Key of the table.
In this step you will have to figure out the primary and foreign keys for each table design and add appropriate ID fields to overcome the problem stated above.
Step 4 – Normalize the Database
The next step is the Normalization process. Normalization refers to the process of eliminating redundant information and other problems in the database design. In order to normalize the current database design, you need to identify the problems in the design and correct them, often by creating additional tables.
Five different levels of normalization exist, known as the five normal forms.
1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Fourth Normal Form (4NF)
5. Fifth Normal Form (5NF)
The following are the corrections that need to be handled:
1. Maintain Consistency – use same field names for fields used as primary keys and foreign keys, and change all tables to plural names.
2. Data Redundancy
First Normal Form – 1NF
A table is in the 1NF when each table is free of repeating data.
Second Normal Form – 2NF
The 2NF only applies to composite a key table, that is if the table contains a primary key made up of two or more table columns. In order to be in the 2NF, every column in the table must depend on the entire primary key, not only a part of it.
Third Normal Form – 3NF
A table is in the 3NF form when the table is in 2NF and every column in the table depends on the entire primary key and none of the other fields depend on each other.
Step 5 – Denormalize the Database
When you come across a situation where you ben the normalization rules a bit, the database will operate more efficiently. For instance having data redundancy in the database can increase the performance of your database. This process of adding data redundancy to increase the efficiency and performance of your database is called denormalization.
Step 6 – Use legal SQL names
If you look back at the data-design process, all the names used were self-descriptive an it was easy to remember what it does exactly and what table and column it represents. It is a good database design practice to avoid SQL dialects with spaces between words like “Product ID” where you can either name it as “ProductID” or “Product_ID”.
Always follow these rules when you choose names for your database:
1. No special characters, other than $, #, and _.
2. No spaces.
3. No more than 128 characters – Keep the names as short and descriptive as possible. Even though 128 character are allowed choose names that are 15 characters or lesser.
Step 7 – Draw an ERD
The best way to represent your data model is by drawing an Entity-Relationship Diagram also known as an ERD. The following is a sample ERD for the database.