How can a database be optimized through normalization?
sobes.tech AI
Answer from AI
Normalization is the process of structuring a relational database to reduce redundancy and improve data integrity. The main normal forms (1NF, 2NF, 3NF) aim to eliminate insertion, update, and deletion anomalies.
1NF (First Normal Form):
- Each column must contain only atomic (indivisible) values.
- Each row must be unique.
2NF (Second Normal Form):
- Meets 1NF.
- All non-key attributes must depend entirely on the full primary key (for composite keys).
3NF (Third Normal Form):
- Meets 2NF.
- All non-key attributes should not have transitive functional dependencies on the primary key (a field should not depend on another non-key field).
Example: An order table without normalization.
| OrderID | OrderDate | ClientID | ClientName | ClientAddress | ProductName | ProductPrice | Quantity |
|---|---|---|---|---|---|---|---|
| 1 | 2023-10-27 | 101 | Ivanov | Moscow | Laptop | 50000 | 1 |
| 2 | 2023-10-27 | 102 | Petrov | Saint Petersburg | Mouse | 1000 | 2 |
| 1 | 2023-10-27 | 101 | Ivanov | Moscow | Keyboard | 3000 | 1 |
Here, there is data redundancy for the client and update anomalies (changing the client's address requires updating multiple rows).
After 3NF normalization:
"Orders" table:
| OrderID | OrderDate | ClientID |
|---|---|---|
| 1 | 2023-10-27 | 101 |
| 2 | 2023-10-27 | 102 |
"Clients" table:
| ClientID | ClientName | ClientAddress |
|---|---|---|
| 101 | Ivanov | Moscow |
| 102 | Petrov | Saint Petersburg |
"OrderDetails" table:
| DetailID | OrderID | ProductID | Quantity |
|---|---|---|---|
| 1 | 1 | 201 | 1 |
| 2 | 2 | 202 | 2 |
| 3 | 1 | 203 | 1 |
"Products" table:
| ProductID | ProductName | ProductPrice |
|---|---|---|
| 201 | Laptop | 50000 |
| 202 | Mouse | 1000 |
| 203 | Keyboard | 3000 |
This reduces redundancy, simplifies updates and inserts, and improves data integrity.
Disadvantages of normalization:
- Increased query complexity (requires more JOINs).
- Possible decrease in performance for certain queries (requiring JOINs).
Optimization in C# when working with a normalized database includes using ORM (Entity Framework, Dapper) for efficient data mapping between C# objects and database tables, query optimization with LINQ, using eager/lazy loading in ORM, and caching data on the application side to reduce database access.