3
0
One of the most basic skill a backend engineer need is the ability to design a database table. It is your responsibility, as backend engineer to design the database table that fits the business requirement. Some thought it is database administration (DBA) responsibility to design it, they are wrong. DBA responsibility is to install, configure, and maintain the database software. DBA will ensure the database is properly configured to deliver the performance required for the application, and data is backup properly to another location for disaster recovery process.
There are two main approach when designing a database.
- Online Transaction Processing (OLTP) database
- Online Analytics Processing (OLAP) database
OLTP Database
OLTP database is designed for transactional operations. Such as, create a user account, adding items into shopping cart, checkout shopping cart, etc. The typical operations you will find on a website.
OLTP database has the following attribute:
- Performance is balanced between insert, read, update, and delete.
- Reduce data duplication
- Read operation is slower than OLAP database due to table joins.
- Data insertion and updates happen very often
- Database serves an online website. Thus every operation must be completed fast.
OLAP Database
On the other hand, OLAP database is designed for reporting and analytical operations. You would use such a database to churn out monthly sales reports, find which product has the best sales, find where most of your sales came from which customer group. The commonality here is the requirement to read a vast amount of data and aggregate it to form useful information.
OLAP database has the following attribute:
- Performance is optimized for read operation
- Data might be aggregated before storing to cut down storage space
- Insertion and update is usually horribly slow
- Data is inserted happen infrequently. Usually once a day, for T-3 data. Data is almost never updated.
- It serves a batch job. Every operation is usually slow.
Designing OLTP Database
Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies. Normalization rules divides larger tables into smaller tables and links them using relationships. The purpose of Normalization in SQL is to eliminate redundant (repetitive) data and ensure data is stored logically.
Designing OLTP database starts from identifying the data you would like to save.
Let’s do a case study.
Introducing… Felix Movie Rental
Felix has recently established a rental company and is in dire need of a system to track all the DVD rents in the business. Staff is currently tracking with excel. Here’s a copy of the data they are tracking.
This is what we call a zero normal form (0NF) table. Let’s go ahead and normalize this table.
First Normal Form (1NF) Rules
- Each cell should contain only 1 value
- Each record need to be unique
Below is 1NF form of the table.
Notice that [Movies Rented] column has more than one value before, it only has one value now for each row. But on the other hand, other columns now have repeated values. E.g, we repeated John’s name and physical address 3 times.
Second Normal Form (2NF) Rules
- Be in 1NF
- Single Column Primary Key
- Must not contain any partial dependency
A relation that is in First Normal Form and every non-primary-key attribute is fully functionally dependent on the primary key, then the relation is in Second Normal Form (2NF).
Primary Key uniquely identifies a row in the table. There are few
- Natural Key. A key that exists in the real world. E.g. NRIC number.
- Surrogate Key. A key with no business meaning.
- Composite Key. A key composed of two or more attributes.
- Foreign Key. A primary key of another table.
Partial dependency occurs when a non-prime attribute is functionally dependent on part of a candidate key. See sample below.
Student ID and project ID is composite key for the table. The table has partial dependency because the student name is only dependent on student ID, and not project ID. Same thing for the project name.
Let’s go back to our case study.
We split the table into 2: member and movie rented. Primary keys are highlighted in yellow.
Third Normal Form (3NF) Rules
- Be in 2NF
- Has no transitive functional dependencies
When an indirect relationship causes functional dependency it is called Transitive Dependency.
If P -> Q and Q -> R is true, then P-> R is a transitive dependency. See sample below.
P = Movie
Q = Author
R = Author Age
- Given movie “No Game No Life Zero”, you can tell the author is “Yu Kamiya”
- Given author “Yu Kamiya”, you can tell his age is 36
- Therefore, given movie “No Game No Life Zero”, you can tell his age is 36
- There is transitive dependency between Movie and Author Age
- Another way you can tell transitive dependency is when an update of a column will require another column to update as well.
Going back to our case study, we can tell there is transitive dependency between name and salutation. A change in name, such as when member finished PhD, will cause salutation to change as well.
We added a new table Salutation to remove the transitive dependency in Member table. Our table is now in 3NF!
Conclusion
We learned the two types of database. OLTP for transactional application, and OLTP for analytical application. Both has its own advantages and disadvantages. Designed for different purposes. Going further, we learned how to design 3NF OLTP database with a case study.
What’s next
We covered some of the basic keys here. You can read more about other key types here https://www.guru99.com/dbms-keys.html#4.
Most of the time you will only need 2 to 3NF database structure when building OLTP database, it’s the perfect balance between performance, storage, and complexity. It is possible to normalize further to reduce storage space, at the cost of performance. You can read more from https://www.guru99.com/database-normalization.html.
Would you like to learn more about database? Let me know your thoughts below.
