Book summary “Database Design for Mere Mortals”: summary

Introduction

Poor database design leads to inaccurate information, data corruption, and wasted hours trying to fix broken spreadsheets or hastily built tables. You face the challenge of storing and retrieving complex data without a clear blueprint, which results in erratic system behavior and unreliable reports. Michael J. Hernandez addresses this problem in Database Design for Mere Mortals by teaching you how to build a rock-solid database structure from the ground up.

A sound logical database design built on set theory and practical guidelines ensures data integrity and eliminates redundancy before you write any application code.

The book focuses on software-agnostic design techniques that apply to any database management system you choose. You learn a step-by-step methodology that separates logical planning from physical implementation. The chapters guide you through analyzing information needs, defining tables, establishing keys, and mapping relationships.

• How do you translate real-world subjects into reliable database tables?
• What separates a primary key from a foreign key?
• How do you prevent data redundancy and update anomalies?

1. The Relational Database Model

The relational database model relies on set theory and first-order predicate logic to guarantee structure and accuracy.

Foundations of the Model

The relational model stores data in tables composed of records and fields. You utilize matching values in shared fields to establish implicit relationships between these tables. Dr. Edgar F. Codd conceived this architecture in 1969 and published his foundational paper in 1970.

• Relations represent individual tables.
• Tuples correspond to records.
• Attributes describe fields.

Operational versus Analytical Databases

You encounter different system types depending on your business requirements. Operational databases handle dynamic data in daily scenarios. Analytical databases store static information for historical reporting and forecasting.

• Operational systems use online transaction processing.
• Analytical systems use online analytical processing.

Relational databases provide distinct structural advantages for your work. You gain multilevel integrity and robust data consistency across your applications.

• Multilevel integrity operates at the field, table, relationship, and business levels.
• Logical and physical data independence protects your programs from underlying storage changes.
• Flexible data retrieval allows you to query information without knowing its physical location.

2. Database Design Methodology and Objectives

A sound database design must fulfill specific objectives to ensure information retrieval, efficiency, data integrity, and future growth.

Following a structured design methodology minimizes costly missteps and ensures your database fulfills its information requirements.

Learning a structured database-design methodology equips you with necessary skills, minimizes missteps, and saves time.

• Support both required and ad hoc information retrieval
• Construct tables properly and efficiently so each represents a single subject with unique values
• Impose data integrity at the field, table, and relationship levels
• Support business rules relevant to the organization
• Lend itself to future growth and easy modification

The Three Development Phases

The overall database development process consists of three distinct phases: logical design, physical implementation, and application development. The author argues that developers should always execute the logical design phase completely first.

• Perform logical design: determine and define tables and their fields, establish primary and foreign keys, establish table relationships, and determine data integrity levels.
• Perform physical implementation: create tables, establish key fields, table relationships, and data integrity tools in the chosen software.
• Perform application development: create an application for users to interact with data, determining user tasks, report output requirements, and menu systems.

Fields containing multiple distinct data items must be decomposed into smaller, separate fields to be usable for sorting and retrieval.

An Address field containing 7402 Kingman Dr., Seattle, WA 98012 is decomposed into Street Address, City, State, and ZIP Code.

3. Analyzing the Current Database and Conducting Interviews

Thorough analysis and structured interviews uncover accurate characteristics and subjects essential for your database structure.

You must always analyze the current database to understand existing data usage and structural deficiencies. Examining the current database helps you determine if it supports current requirements and uncovers structural flaws.

The database analysis process consists of three core steps:

• reviewing the way data is collected;
• reviewing the manner in which information is presented;
• conducting interviews with users and management.

Mike's Bike Shop interviews involve reviewing staff data collection sheets, examining word-processed reports like the supplier phone list, and discussing future reports such as total bike sales by manufacturer.

Interview Guidelines

Maintaining strict control and structure during interviews prevents derailed conversations and ensures complete information gathering.

You should interview users first because they represent the front lines of daily operations. Management interviews focus on current reports, missing information, overall organizational requirements, and future evolution.

When you conduct interviews, apply these practices:

• Choose an appropriate meeting platform to conduct your meeting such as Skype, Zoom, WebEx, Microsoft Teams, or Google Meet.
• Set a reasonable, practical limit for the number of people participating in each interview.
• Conduct separate interviews for users and management.
• Prepare your questions prior to the interview using open-ended questions.
• Assign a dependable transcriber or record the interview for reference purposes after notifying participants.
• Give everyone your equal and undivided attention.
• Keep the pace of the interview moving by tabling belabored points.
• Always maintain control of the interview by redirecting irrelevant discussions.

Mission Statement and Objectives

A well-defined purpose and clear objectives ensure your database remains focused on organizational needs without unnecessary complexity.

The mission statement acts as a guiding light throughout the design process, preventing the database structure from becoming unnecessarily large.

For Mike's Bikes, the interview with owner Mike revealed a need to track inventory and sales, leading to the mission statement: The purpose of the Mike's Bikes database is to maintain the data we need to support our retail sales business and our customer service operations.

Mission objectives are declarative sentences representing the general tasks supported by the data maintained in the database. For Mike's Bikes, interviews with Mike produced mission objectives such as: Maintain complete inventory information, Maintain complete customer information, Track all customer sales, Maintain complete supplier information, and Maintain complete employee information.

4. Establishing Table Structures and Refining Fields

Ideal tables represent a single subject, and ideal fields represent distinct characteristics without calculated values or anomalies.

You must compile a Preliminary Field List by reviewing characteristics and resolving generic names. You need to examine all samples to capture missing items.

• Review and refine the list of characteristics.
• Refine items with the same generic name by adding a subject prefix.
• Refine items representing the same characteristic by keeping only one clear version.
• Ensure each item on the list represents a characteristic rather than a subject.
• Examine all samples to determine if there are characteristics that need to be added.
• Highlight characteristics on samples, cross out those already on the list, and add the rest.

At Mike's Bikes, a partial Preliminary Field List of 12 fields is created, including birth date, employee name, customer name, office phone, product name, category, unit price, invoice number, and invoice date. Michael J. Hernandez notes that you must separate calculations and enumerated sets from standard data fields.

• Examine the Preliminary Field List for fields containing words like amount, total, sum, average, minimum, maximum, and count.
• Remove calculated fields from the Preliminary Field List and place them on the Calculated Field List.
• Record the name of each characteristic that incorporates a value list on a separate sheet of paper.
• Record the specific values within the list or write a brief description of the type of values with minimum and maximum bounds.

Mike's Bikes Calculated Field List includes discount amount, grand total, item total, and subtotal. Value lists include categories like accessories, bikes, clothing, components, maintenance, racks, and wheels.

Table and Field Guidelines

You define the preliminary table list by using implied subjects from your fields and merging them with interview lists. You apply the Subject-Identification Technique to ensure zero omissions.

• Use the Preliminary Field List to identify implied subjects.
• Create a second version of the Preliminary Table List by merging the list of subjects with the first version.
• Step 1: Resolve duplicate items by identifying items on the list of subjects that already appear on the Preliminary Table List.
• Step 2: Resolve items that represent the same subject but have different names by selecting the best name and crossing out or replacing the counterpart.
• Step 3: Combine the remaining items from the list of subjects and throw away the old list of subjects.
• Use the mission objectives to determine whether any subjects were overlooked by applying the Subject-Identification Technique.

In developing an equipment rental business database, matching lists revealed duplicate items like Equipment and Rental Agreements, leading to the creation of a Vehicles table and a revised list of subjects and preliminary tables. You must ensure your table and field names follow strict organizational rules.

• Create a unique, descriptive name that is meaningful to the entire organization.
• Create a name that accurately, clearly, and unambiguously identifies the subject of the table.
• Use the minimum number of words necessary to convey the subject of the table.
• Do not use words that convey physical characteristics such as file, record, and table.
• Do not use acronyms and abbreviations.
• Do not use proper names or other words that will unduly restrict the data.
• Do not use a name that implicitly or explicitly identifies more than one subject.
• Do use the plural form of the name.

An entertainment agency database design initially listed Dates, which was ambiguous because it meant client meetings for one person and booking dates for entertainers, so it was replaced with Client Meetings and Entertainer Schedules. You apply matching constraints when naming fields.

• Create a unique, descriptive name that is meaningful to the entire organization.
• Create a name that accurately, clearly, and unambiguously identifies the characteristic a field represents.
• Use the minimum number of words necessary to convey the meaning of the characteristic.
• Do not use acronyms and use abbreviations judiciously.
• Do not use words that could confuse the meaning of the field name.
• Do not use names that implicitly or explicitly identify more than one characteristic.
• Use the singular form of the name.

In the school database example, Classes was shortened to Cls, Subjects to Subj, Instructors to Inst, and Student to Std, and SSN was replaced with Social Security Number to ensure zero ambiguity. You finalize table definitions by composing clear descriptions.

• Include a statement that accurately defines the table.
• Include a statement that explains why this table is important to the organization.
• Compose a description that is clear and succinct.
• Do not include implementation-specific information in your table description.
• Do not make the table description for one table dependent upon the table description of another table.
• Do not use examples in a table description.

In a bakery database, the description for Suppliers was improved from a vague company list to specify people and organizations supplying ingredients and equipment, emphasizing that supplier information is vital to maintain a constant supply.

Resolving Anomalies and Subset Tables

An ideal field represents a distinct characteristic without multipart values or calculations, and an ideal table represents a single subject with minimum redundancy.

You test your tables on paper to uncover structural anomalies before you build code. You resolve multivalued attributes by extracting them into dedicated child tables.

• Remove the field from the table and use it as the basis for a new table.
• Use a field or set of fields from the original table to relate the original table to the new table.
• Assign an appropriate name, type, and description to the new table and add it to the Final Table List.

The CATEGORIES TAUGHT field in the INSTRUCTORS table, which contained comma-separated values like DTP, SS, WP, was removed and made into a new INSTRUCTOR CATEGORIES table linked by instructor first and last names. You establish subset tables when records exhibit specialized attributes with blank values in the main data table.

• Determine whether the table represents more than one subject due to blank values.
• Remove the specialized fields and use them as the basis for new subset tables.
• Add the connecting field to the subset tables to relate each subset table to the data table.
• Compose a suitable description for the subset tables and add them to the Final Table List with type set to Subset.

An office inventory table containing both equipment fields and book fields was split into an EQUIPMENT subset table and a BOOKS subset table, both related back to the inventory item name.

5. Establishing Keys and Field Specifications

Primary keys uniquely identify records, while detailed field specifications enforce field-level integrity and data consistency.

Candidate and Primary Keys

Keys uniquely identify records and establish relationships across tables. Candidate keys uniquely identify table records without multipart fields or Nulls. The primary key is selected from available candidate keys to exclusively represent records throughout the entire database.

Every candidate key must conform to a strict set of elements guaranteeing uniqueness, non-nullability, and stability. You must ensure that each candidate key satisfies these specific criteria:

• It cannot be a multipart field.
• It must contain unique values.
• It cannot contain Null values.
• Its value cannot cause a breach of the organization's security or privacy rules.
• Its value is not optional in whole or in part.
• It comprises a minimum number of fields necessary to define uniqueness.
• Its values must uniquely and exclusively identify each record in the table.
• Its value must exclusively identify the value of each field within a given record.
• Its value can be modified only in rare or extreme cases.

In an employee table containing nine columns, Employee ID and the combination of First Name and Last Name are identified as potential candidate keys, while Social Security Number, Last Name alone, Zip Code, and Home Phone are disqualified.

When a table lacks a natural candidate key, an artificial candidate key must be created from scratch. In a PARTS table containing Part Name, Model Number, Manufacturer Name, and Retail Price, no natural candidate key exists because part names duplicate across models, model numbers and manufacturer names can be null. The solution is to create an artificial candidate key called PART NUMBER.

A field specification incorporates general, physical, and logical elements that define every attribute and behavior of a field.

Anatomy of a Field Specification

Defining field specifications for every field in a database is an essential investment that ensures data consistency and quality information. Neglecting field specifications leads to inconsistent, erroneous data and inaccurate information. People who skip this design step frequently spend up to three times as long fixing their improperly designed databases later.

General elements cover fundamental attributes like field name and description; physical elements handle structure like data type and length; logical elements govern values, keys, null support, and edit rules. Field specifications can be defined as Unique, Generic, or Replica to handle different structural roles and template requirements. A generic STATE specification with alphanumeric length 2 and letter character support serves as the template for the VENDSTATE field.

6. Establishing Relationships and Business Rules

Table relationships and business rules impose necessary constraints and maintain referential and data integrity across the database.

You determine table relationships using a table matrix and specific relationship formulas. By noting entries in a table matrix such as 1:1 or 1:N and applying combination formulas, you identify the official relationship type between tables.

• Select a pair of tables and note the entry at the junction of the first table and the second table.
• Locate the second table on the same side of the matrix you're working on and note the entry and the junction between it and the first table on the opposite side of the matrix.
• Apply the appropriate formula to the two entries and identify the official relationship between the tables.
• Diagram the relationship in the appropriate manner.
• Cross out both entries on the matrix.

One-to-one and one-to-many relationships are established using foreign keys, while many-to-many relationships require a linking table. For one-to-many relationships, a copy of the primary key from the parent table is placed into the child table as a foreign key. Many-to-many relationships are resolved by creating a new linking table containing a composite primary key formed from the primary keys of both parent tables.

The many-to-many relationship between STUDENTS and CLASSES is established using a linking table called STUDENT CLASSES.

Relationship Types and Cardinality

Deletion rules dictate how the relational database management system handles related child records when a parent record is deleted. The five types of deletion rules are Deny, Restrict, Cascade, Nullify, and Set Default. Restrict is commonly used as a default rule to prevent orphaned records.

Business rules are divided into database-oriented rules, which can be established within the logical database design, and application-oriented rules, which must be handled in the physical design or application code. Database-oriented rules modify field specifications or relationship characteristics. Application-oriented rules involve calculations or criteria that cannot be represented in a simple table structure, such as discount percentages based on customer status.

A customer with a Preferred status receives a 15% discount on all purchases.

Validation Tables and Business Rules

Validation tables store relatively fixed sets of valid values to enforce field-specific business rules that limit a range of values. Instead of enumerating numerous values inside a field specification, a validation table acts as a parent table in a one-to-many relationship with the affected table, using its primary key as a foreign key in the child table.

Creating a STATES validation table to restrict supplier states to the 11 contiguous western states, Alaska, or Hawaii.

Establishing field-specific business rules follows a six-step procedure involving user collaboration, constraint identification, and field specification modifications. Working with users and management ensures constraints are meaningful before documenting them on a standardized specification sheet.

• Select a table.
• Review each field and determine whether it requires any constraints.
• Define the necessary business rules for the field.
• Establish the rules by modifying the appropriate field specification elements.
• Determine what actions test the rule.
• Record the rule on a Business Rule Specifications sheet.

10 Key Ideas

1. The relational database model relies on set theory and first-order predicate logic to guarantee structure and accuracy across operational and analytical systems.
2. Following a structured database-design methodology divided into logical design, physical implementation, and application development minimizes costly missteps.
3. Thorough analysis and structured interviews with users and management uncover accurate characteristics, mission statements, and subjects essential for your database structure.
4. Ideal tables represent a single subject, and ideal fields represent distinct characteristics without multipart values, calculated values, or update anomalies.
5. Candidate keys uniquely identify table records without multipart fields or Nulls, and primary keys are selected from them to exclusively represent records.
6. A field specification incorporates general, physical, and logical elements that define every attribute and behavior of a field to enforce data consistency.
7. Table relationships and business rules impose necessary constraints and maintain referential and data integrity using foreign keys and linking tables.
8. Deletion rules like restrict and cascade dictate how the relational database management system handles related child records when a parent record is deleted.
9. Validation tables store relatively fixed sets of valid values to enforce field-specific business rules that limit data entry ranges.
10. Establishing field-specific business rules follows a six-step procedure involving user collaboration, constraint identification, and field specification modifications.