What is Database Design?
Database design is the process of structuring how data will be stored, organised, and related in a relational database system. Good design prevents data duplication, inconsistency, and errors , and makes querying data much more efficient.
Before exploring relational databases, it helps to understand why they exist. A flat-file database stores all data in a single table , like a large spreadsheet. While simple to create, flat-file databases quickly develop problems: data is repeated across many rows, updates must be made in multiple places, and deleting one record can accidentally destroy other important information. A relational database solves these problems by splitting data into multiple related tables.
The key building blocks of any relational database are:
- Entities , the objects or concepts you want to store data about
- Attributes , the properties that describe each entity
- Relationships , the connections between entities
- Keys , identifiers that ensure each record is unique and linkable
Database design typically moves through three levels of abstraction:
- Conceptual Schema , a high-level overview showing entities and relationships, with no technical detail
- Logical Schema , a detailed structure including all attributes, keys, and relationships; technology-independent
- Physical Schema , the actual implementation, including data types, storage structures, and database-specific features
For IB CS assessments, you will almost always be working at the logical schema level , showing entities, attributes, keys, and relationships. The conceptual level is used for early planning discussions, while the physical level is implementation detail beyond SL scope.
Think of these three levels like designing a house. The conceptual schema is the rough sketch showing rooms and layout. The logical schema is the detailed architectural blueprint. The physical schema is the actual construction plan with materials and measurements specified.
Flat-File vs Relational Databases
Understanding the difference between flat-file and relational databases explains why good database design matters.
Flat-File Database:
All data is stored in a single table. Easy to set up, but causes serious problems at scale.
| StudentID | StudentName | CourseName | TeacherName | TeacherEmail |
|---|---|---|---|---|
| 101 | Alice Smith | Mathematics | Mr. Khan | khan@school.com |
| 101 | Alice Smith | Physics | Ms. Patel | patel@school.com |
| 102 | Ben Wong | Mathematics | Mr. Khan | khan@school.com |
Problems visible immediately:
- Alice's name appears twice , if it needs updating, both rows must be changed
- Mr. Khan's email appears twice , one missed update creates inconsistency
- You cannot add a new course until a student enrols in it
Relational Database:
Data is split into separate tables connected by keys. Each piece of information is stored once.
- Student table: StudentID, StudentName
- Course table: CourseID, CourseName, TeacherID
- Teacher table: TeacherID, TeacherName, TeacherEmail
- Enrolment table: StudentID, CourseID (junction table)
The core principle of relational design is: store each fact in exactly one place. If you find yourself writing the same value in multiple rows, that is a signal the design needs improvement.
| Feature | Flat-File | Relational |
|---|---|---|
| Data storage | Single table | Multiple linked tables |
| Redundancy | High , data repeated | Low , each fact stored once |
| Update risk | High , must update many rows | Low , update in one place |
| Scalability | Poor | Good |
| Query complexity | Simple | More powerful with JOINs |