Chapter 2: Database Design and Normalization

Designing a relational database

The relational model

The relational model organizes data in tables of rows and columns. It is based on the relation, a mathematical term for a set of values that share the same attributes.

In this model you create tables for different types of data and link them together with keys. That lets you query and manipulate the data in a flexible and efficient way.

The relational model is flexible, scales well, and is easy to use. Adding or removing data is simple, and you can run complex queries on large datasets.

The entity-relationship (ER) model

The ER model represents data as entities (objects) and the relationships between them. It’s often used in database design to model complex relationships between different types of data.

You create entities for the different types of data and connect them with relationships. This shows complex data structures in a clear and intuitive way.

The ER model can capture complex relationships between data, and it presents the data in a form that is easy to understand and modify.

Creating a data model

A data model describes the structure of the data in a database and the relationships within it. It can be a diagram or a written description, and it guides the design and development of the database.

To create one, identify the types of data you want to store and map out how they relate to each other. This can mean creating tables and columns, defining relationships between tables, and specifying data types and constraints.

When you build the model, use clear and consistent naming conventions, avoid redundant or duplicated data, and follow normalization principles.

Defining tables and columns

To define a table, you specify its name, the name and data type of each column, and any constraints or rules that apply to the data.

A good table definition uses clear, concise names for the table and its columns and an appropriate data type for each column. It also defines constraints and rules that protect data integrity.

Relationships between tables

A relationship links one table in a database to another. Relationships let you model complex connections between different types of data.

You define relationships by creating keys that link one table to another. This can mean creating primary keys and foreign keys, which let you join tables together in meaningful ways.

Give keys clear and concise names and appropriate data types, and avoid circular or redundant relationships.

Normalization

Normalization is the process of organizing and structuring the data in a database to reduce redundancy and improve data integrity. Its goal is to keep redundant and duplicated data to a minimum, because that kind of data leads to inconsistencies and errors. Removing those problems makes the stored data more accurate, consistent, and reliable.

Normalization breaks a database down into smaller, more manageable parts and organizes them in a logical and efficient way. To do that, you identify the functional dependencies between data and make sure each piece of data is stored in only one place.

The benefits are better data integrity, less redundancy and duplication, and a more flexible and scalable database.

There are several normal forms, including First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form (BCNF). Each normal form is a higher level of normalization and removes more redundancy.

First normal form (1NF)

First Normal Form (1NF) is the first level of normalization. It requires all data in a table to be atomic and indivisible: each column in a table holds only one piece of data, and there are no repeating groups of data.

To get a table into 1NF, move any repeating groups of data into separate tables and make sure each column holds only one piece of data.

1NF brings the same benefits as normalization in general.

Second normal form (2NF)

Second Normal Form (2NF) is the second level of normalization. It requires a table to be in 1NF and every non-key attribute to be fully functionally dependent on the primary key: it must depend on the whole key, not on just part of it. This only matters when the primary key has more than one column. In a table keyed on order_id and product_id, for example, a product_name column depends on product_id alone, so the table isn’t in 2NF.

To get a table into 2NF, identify all of its functional dependencies and move any columns that depend on only part of the primary key into separate tables.

Third normal form (3NF)

Third Normal Form (3NF) is the third level of normalization. It requires the non-key attributes in a table to be independent of each other, so each column relates to the primary key and not to any other non-key attribute.

To get a table into 3NF, identify its transitive dependencies and move any columns that depend on other non-key attributes into separate tables.

3NF brings those same benefits.

Boyce-Codd normal form (BCNF)

Boyce-Codd Normal Form (BCNF) is a slightly stricter version of 3NF. It requires that in every functional dependency in a table, the column or columns that determine the others form a candidate key. Most tables in 3NF are already in BCNF. The two differ only in tables with more than one candidate key, where the keys share columns.

To get a table into BCNF, identify all of its functional dependencies and make sure the candidate keys determine them. You may need to break the table into smaller tables that meet the BCNF requirements.

BCNF has those benefits too. Because only candidate keys determine other columns, BCNF rules out update anomalies and other inconsistencies that can occur in less normalized databases. The data is more accurate and reliable as a result, which is essential when you use it to make business decisions. More normalization doesn’t automatically make queries faster, though. Splitting data into more tables means a query may need more joins to put it back together.