Lesson 2: Logical Design, Normalization, and Metadata Control

Good administration starts before performance tuning. It starts with a schema that reflects business rules clearly.

Suppose a school says:

• one student can enroll in many subjects
• one subject can have many students
• each enrollment has a grade
• each student belongs to one program

These statements become part of the logical design. In a relational database, we usually represent them with tables, keys, and constraints.

A simple design may look like this:

students(student_id, student_name, program_code)
subjects(subject_code, subject_title, units)
enrollments(student_id, subject_code, term_code, grade)

The enrollments table resolves the many-to-many relationship between students and subjects.

A DBA does not always create the business model from scratch, but the DBA must be able to read and evaluate the model. Weak design creates long-term operational problems such as:

• duplicate data
• inconsistent updates
• difficult reporting
• wasted storage
• unclear constraints

That is why logical design and administration are connected. A badly designed table may still work at first, but it becomes harder to secure, index, and maintain as the database grows.

Normalization is the process of organizing tables so that data is stored with fewer unnecessary duplicates and clearer dependencies.

The core idea is this: a fact should be stored in the table where it naturally belongs.

Look at this bad table:

student_id  student_name  program_name  subject_code  subject_title  instructor_name

This mixes student facts, subject facts, and teaching facts in one place. The result is repetition. If subject_title changes, many rows must be updated. If one update is missed, the database becomes inconsistent.

Normalization addresses this using forms such as:

• First Normal Form (1NF) — values are atomic; no repeating groups
• Second Normal Form (2NF) — non-key attributes depend on the whole key
• Third Normal Form (3NF) — non-key attributes do not depend on other non-key attributes

A practical DBA should know the symptoms of poor normalization:

• same descriptive values repeated in many rows
• update anomalies
• insert anomalies
• delete anomalies

Normalization is not about making unlimited small tables. It is about choosing a structure that protects integrity while still supporting performance and reporting.