Table of Contents
Database designing rules which I follow:
Analyze the nature of the application you are designing for
Depending on the type of application you can go to the normalization of the database or a de-normalized structure.
CRUD applications are called On-line Transaction Processing (OLTP) applications, while analytics applications are usually called On-line Analytical Processing OLAP applications.
For OLTP Applications, when performing CRUD operations, normalization is important; while in OLAP Applications, a de-normalized structure is generally preferred.
About OLTP Applications
Are the type of applications for which you will need CRUD type operations(INSERT, READ, UPDATE and DELETE records)..
OLTP deals with large numbers of transactions by a large number of users, being characterized by a very large number of insert/update operations.
The key goals of OLTP applications are availability, speed, concurrency, and recoverability.
Examples of OLTP Transactions
- Online banking
- Booking an airline ticket
OLTP mainly deals with large numbers of transactions by a large number of users.
About OLAP Transactions
Are the type of application when You are more interested in analysis, forecasting, reporting, etc which usually involves querying many records. These kinds of databases have a less number of inserts and updates. The main intention here is to fetch and analyze data as fast as possible. This kind of databases are referred as .
OLAP applications are widely used in Data Mining.
OLTP vs OLAP: What’s the Difference?
OLTP applications have the following characteristics:
- Are used by a large number of users.
- Have fast response times.
- Have a large number of insert/update operations.
OLAP applications have the following characteristics:
- Are characterized by low volume of transactions.
- Queries are often very complex and involve aggregations.
What is Database Normalization?
Database normalization is a process used to organize a database into tables and columns and reduce the number of duplicate data contained within your database. Read more here about Database normalization
There are three common forms of database normalization: 1st, 2nd, and 3rd normal form, each increasing levels of normalization.
First Normal Form (1NF)
It contains two-dimensional tables with rows and columns and the values in each column of a table must be atomic.
First normal form means to:
- Eliminate repeating groups in individual tables.
- Create a separate table for each set of related data.
- Identify each set of related data with a primary key.
Do not use multiple fields in a single table to store similar data.
Second Normal Form (2NF)
Second normal form means to:
- Create separate tables for sets of values that apply to multiple records.
- Relate these tables with a foreign key.
Records should not depend on anything other than a table’s primary key (a compound key, if necessary).
Third Normal Form (3NF)
In the Third Normal Form no duplicate information is permitted.
Second normal form means to:
- Eliminate fields that do not depend on the key.
Values in a record that are not part of that record’s key do not belong in the table.
In general, anytime the contents of a group of fields may apply to more than a single record in the table, consider placing those fields in a separate table.
Free Open Source tool for Data Modeling
DB Designer is a free tool for data modeling that works with the logical model.
WIth DB Designer You can design database model online & generate SQL script instantly. It supports MySql, Oracle, SQL Server, SQLite, and others.
Is very useful for teamwork because it offers the opportunity to Work on the model at the same time. Invite and share access (Owner, Editors, Viewers) by simply providing member’s email.
Hello there!
I hope you find this post useful!I'm Mihai, a programmer and online marketing specialist, very passionate about everything that means online marketing, focused on eCommerce.
If you have a collaboration proposal or need helps with your projects feel free to contact me. I will always be glad to help you!
