3 Overview of database design

This chapter transitions from basic SQL to end-to-end relational database design, using The Sci-Fi Collective’s online store as a running example. It positions the goal of design as delivering a well-structured, efficient database that truly fits user and organizational needs, and it sets expectations for the journey ahead. You begin translating objectives into concrete design choices by kicking off requirement analysis for the project.

The chapter distills five guiding aims of good database design: data consistency and integrity (appropriate types, keys, and constraints to minimize redundancy and avoid insert, update, and delete anomalies); maintainability and ease of use (clear naming and conventions that make schemas intuitive); performance and optimization (for example, indexing frequently sorted columns like review dates); data security (preventing unauthorized actions and protecting sensitive data through measures such as encryption); and scalability and flexibility (schema separation and caching to handle growth without sacrificing speed). These aims define what “good” looks like and anchor trade-offs throughout the work.

It then surveys the process shared across methodologies: requirement gathering, analysis and design, and implementation/integration with testing. Requirement gathering emphasizes interviewing stakeholders, subject-matter experts, and technical staff; studying legacy systems and informal tools (spreadsheets, forms); requesting demonstrations and data samples; and crystallizing goals—for The Sci-Fi Collective, managing products (inventory, reviews), users (accounts, payment methods, purchases), and the transactions linking them. Analysis and design convert findings into models (E-R diagrams) and apply normalization to reduce redundancy (e.g., splitting a combined product_review table into product and review), while implementation creates tables, data types, relationships, constraints, and indexes, followed by tests of functionality, performance, and security; along the way you identify subjects, their characteristics, and relationships such as one-to-many between users and orders and many-to-many between orders and products.

• The overall goal of database design is to deliver a well-structured and efficient database.
• A series of key subgoals of database design include (1) data consistency and integrity, (2) maintainability and ease of use, (3) performance and optimization, (4) data security, and (5) scalability and flexibility.
• All database design approaches share the same key phases, including requirement gathering, analysis and design, and implementation/integration and testing.
• Requirement gathering is the phase in which you gather information about the database via different ways, such as talking to all the people involved in using the database, studying existing databases, or examining other relevant aspects of information management.
• The analysis and design phase involves carefully thinking through all the requirements and coming up with a solid plan for how the database will be structured and how it will work.
• The implementation/integration and testing phase is about building and validating the database based on the blueprint you made in the design and analysis phase.
• Interviews are the most important task in requirement gathering. Before interviews, you need to carefully plan the interview questions. During and after the interviews, you need to identify subjects, characteristics, and relationships among subjects.

FAQ