Process of designing a database’s structure.
Physical design
Deciding on the physical layout of the database (storage structures, access paths, file organization).
Logical design
Deciding on the database schema. Affected by business decisions and computer science considerations. This module focuses on the logical design.
Design Approaches
Entity-Relationship Model is used widely. The ER diagram is translated into a relational schema.
Normalization
A technique to ensure quality of a relational schema by eliminating redundancy and anomalies. Explained in Normalization.
Design Anti-patterns
- Storing multiple values in 1 column (comma-separated lists)
Violates 1NF. Makes querying and indexing impossible. - Using generic columns (
col1,col2,col3)
Schema has no semantic meaning; impossible to enforce integrity constraints. - Redundant data across tables without a foreign key
Update anomaly: changing a value in 1 place leaves stale copies elsewhere. - Using application-layer IDs as primary keys when natural keys exist
Introduces unnecessary surrogate keys when a stable natural key is available. - Overusing NULL
NULLs indicate missing or unknown data; overuse signals schema design problems.
Architecture
Architecture of a database system is influenced by the underlying computer system.
Examples:
- Centralized
- Client-server
- Parallel
- Distributed