Tuning starts at design phase. If the design is not proper, it leads to redundancy, updation problems and deletion problems. During design phase, database architect will follow a technique calles Normalization.
Normalization is nothing but spliting a huge table into multiple smaller independant segments. Various normal forms are 1NF, 2NF, 3NF, BCNF, 4NF etc. As the degree of NF increases, we get n number of tables.
If n number of tables are involved, we get n-1 joining conditions. As the no of joining conditions are more, it degrades the performance.
In the design phase, database architect will use some third party data modeling tools like ERWIN to create a logical model(ER Diagrams). Once the logical model is ready, it will be converted into physical model(actual tables).
In data modeling terminology, the process of converting logical model into physical model is called forward engineering.
The process of converting physical model into logical model is called reverse engineering.
De Normalization is a technique generally we follow in OLAP systems to minimize the no of tables in the select statement.
