Relational Database Design Stages

Relational Database Design (RDD) is the process of organizing data into structured tables that relate to one another. This process ensures that data is stored efficiently, avoids redundancy, and enables seamless queries and updates. In this lesson, you’ll learn the key stages of RDD and how to design databases with one-to-many relationships while applying normalization rules.

Stages of Relational Database Design

RDD can be broken into four stages:

1. Define the purpose and entities of the database.
2. Identify primary keys.
3. Establish table relationships.
4. Apply normalization rules.

1. Define Database Purpose and Entities

The first stage is to identify the purpose of the database:

• What problem is the database solving?
• What data will it store?
• How will the data be used?

Next, identify the main entities (tables) needed for the database and their attributes (columns). For example, an e-commerce application might require:

• products table: Contains product IDs, names, and prices.
• orders table: Stores order details and links to products and customers.
• users table: Stores customer information like IDs and names.

2. Identify Primary Keys

Each table must have a primary key (PK)—a unique identifier for each record. In our e-commerce example:

• products: Primary key is product_id.
• orders: Primary key is order_id.
• users: Primary key is user_id.

Primary keys ensure each record is uniquely identifiable, making relationships between tables possible.

3. Establish Table Relationships

Relationships define how tables are connected. There are three types:

• One-to-One: Each record in Table A relates to one record in Table B.
• One-to-Many: Each record in Table A relates to multiple records in Table B.
• Many-to-Many: Each record in Table A relates to multiple records in Table B and vice versa.

Example: One-to-Many Relationship

In a one-to-many relationship, one record in Table A is associated with multiple records in Table B. This is achieved using a foreign key (FK) in Table B that references the primary key in Table A.

Consider the following example:

users table:

id   name
1    Alice
2    Bob
    

orders table:

id   purchaser_id
10   1
11   1
12   2
    

Here, purchaser_id in the orders table is a foreign key referencing the id in the users table. This relationship indicates:

• Alice (User ID 1) made two orders (Order IDs 10 and 11).
• Bob (User ID 2) made one order (Order ID 12).

4. Apply Normalization Rules

Normalization is the process of optimizing the database structure to minimize redundancy and ensure data integrity. The most commonly used normalization rules are:

First Normal Form (1NF):

• Eliminate repeating groups within tables.
• Each column should hold atomic (indivisible) values.
• Each table should have a primary key.

Second Normal Form (2NF):

• Ensure all non-primary key attributes are fully dependent on the primary key.
• Create separate tables for sets of related data and use foreign keys to link them.

Third Normal Form (3NF):

• Eliminate fields that do not depend on the primary key.

These rules reduce redundancy, improve data consistency, and make the database easier to maintain.

SQL Commands

Here’s how to create tables with a one-to-many relationship in SQLite3:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  purchaser_id INTEGER,
  FOREIGN KEY (purchaser_id) REFERENCES users(id)
);
    

This schema ensures each order is linked to a specific user while maintaining data integrity.

What You've Learned

In this lesson, you explored the stages of Relational Database Design:

• Define the purpose: Identify why the database is being created and what entities it will include.
• Identify primary keys: Ensure each table has a unique identifier for its records.
• Establish relationships: Define how tables are connected using primary and foreign keys.
• Apply normalization: Optimize the database structure to reduce redundancy and improve efficiency.

Real-World Applications

Relational database design is critical in many systems, including:

• E-commerce platforms: Connecting customers, orders, and products.
• Content management systems: Linking users, articles, and comments.
• Event management: Organizing attendees, events, and venues.

By following the RDD process and applying normalization rules, you can design efficient and reliable databases for a variety of applications.