Database Design: Common Problems and How to Fix Them

A database is an organized collection of data. Database design is the process of structuring that data so it stays consistent, avoids duplication, and is easy to update.

Relational databases are built around relationships between tables, usually defined through primary keys and foreign keys. They shine in OLTP (online transaction processing) — systems like sales or purchasing where inserts, updates, and deletes happen constantly.

Normalization: the rules for good design

Normalization is a set of rules that cut redundancy and prevent update anomalies (changing a value in one place but not another). The first three handle almost every real case:

1. 1NF — No repeating groups; every value is atomic (one value per field).
2. 2NF — Every non-key field depends on the *whole* key.
3. 3NF — No redundant data; no field depends on another non-key field.

There are 7–9 normal forms in total, but 1NF–3NF cover nearly any situation.

The problems (poor design)

• 1NF violation: PhoneNumber1 and PhoneNumber2 are a repeating group. CustomerName also breaks 1NF — it isn't atomic, since it holds both a first and last name.
• 2NF violation: EmployerName describes the employer, not the customer, so it doesn't truly depend on CustomerID. It belongs in its own table.
• 3NF violation: ProductName lives in both the Order and Product tables — redundant data that can fall out of sync.

The solution (good design)

• Split the name into FName / LName, and split the phone into AreaCd / PhoneNbr → data is now atomic (1NF).
• Move phone numbers into a separate CustomerPhone table instead of numbered columns → no repeating groups (1NF).
• Move employer info to its own table so every field depends on the key (2NF).
• Store product data once in Products; the Order table no longer duplicates ProductName (3NF).
• An OrderProducts bridge table (keyed on OrderID + ProductID, a composite key) resolves the many-to-many relationship between orders and products.