Database Components  «Prev  Next»

Lesson 4 Designing Relational Databases
Objective Describe how to design tables in a relational database by splitting data into multiple tables.

Designing Relational Databases using Access

Designing tables is the first step in creating a database. You will need to figure out:
  1. What data you want to store
  2. How many tables to store it in, and
  3. How the tables will relate to each other

Before you begin figuring out how many tables you need, and what data will be stored in each table, you may want to consider the analysis you will be doing on your data in order to make sure that the fields you need are included. Remember that the relationships between tables will allow you to use related data from multiple tables in your analyses. Sometimes the easiest way to figure out how to store your data is to find an example of similarly structured data.

Splitting Data into Related Tables

Most of the time, we have our data distributed across multiple tables, and those tables are "related" to each other in some way. For example, let's say we have a table for logging how well students do on their tests, and we include emails in case we need to email parents about slipping grades: There is one big thing to realize about these tables: they are describing relational data. They are describing data that relates to each other. When the same data is replicated across multiple tables, there can be interesting consequences. It is often preferable to make sure that a particular column of data is only stored in a single location, so there are less places to update and less risk of having different data in different places. If we do that, we need to make sure we have a way to relate the data across the tables. The following series of images presents three different databases designed for different purposes.

A simple database to display catalog sales
1) A simple database to store information about catalog sales might contain the following tables.

A list of customers with their contact information
2) A list of customers with their contact information.

A list of orders using the Customer ID number and the date.
The database diagram shows "two related tables": `Customers` and `Order Summary`.
Below is the analysis of the attributes of each table and their relationship:
  1. Table: Customers
    • Attributes (Fields):
      • Customer ID (Primary Key)
      • Last Name
      • Street
      • City
      • State
      • ZIP
  2. Table: Order Summary
    • Attributes (Fields):
      • Customer ID (Foreign Key referencing Customers.Customer ID)
      • Order ID
      • Order Date
  3. Relationship Between the Tables
    • Type: One-to-Many (1:N)
    • Key Field: Customer ID
    • Direction: One customer in the Customers table can have many orders in the Order Summary table.
    • Enforced By: A foreign key constraint from Order Summary.Customer ID to Customers.Customer ID.

Summary:
This diagram models a typical customer-orders relationship:
  • The Customers table holds personal and location data.
  • The Order Summary table logs each order tied to a specific customer.
  • The red line represents a referential integrity constraint ensuring all orders map to valid customers.

A list of items ordered (order details), and
4) A list of items ordered (order details), and

A table of items available to be ordered
5) A table of items available to be ordered
The diagram shows a "four-table relational database schema" with clearly defined relationships. Here's the breakdown:
  1. Table: Customers
    • Attributes:
      • Customer ID (Primary Key)
      • Last Name
      • Street
      • City
      • State
      • ZIP
  2. Table: Order Summary
    • Attributes:
      • Customer ID (Foreign Key referencing Customers)
      • Order ID (Primary Key)
      • Order Date
    • Relationship:
      • One-to-Many: One customer can place many orders.
      • Link: Customers.Customer ID → Order Summary.Customer ID
  3. Table: Order Details
    • Attributes:
      • Details ID (Primary Key)
      • Order ID (Foreign Key referencing Order Summary)
      • Item ID (Foreign Key referencing Catalog Items)
      • Quantity
    • Relationships:
      • One-to-Many from Order Summary to Order Details: Order Summary.Order ID → Order Details.Order ID
      • Many-to-One to Catalog Items: Order Details.Item ID → Catalog Items.Item ID
  4. Table: Catalog Items
    • Attributes:
      • Item ID (Primary Key)
      • Name
      • Cost
      • Description

Summary of Relationships
From Table To Table Key Field Relationship Type
Customers Order Summary Customer ID One-to-Many
Order Summary Order Details Order ID One-to-Many
Catalog Items Order Details Item ID One-to-Many

This structure models a real-world sales database:
  • Customers place orders.
  • Each order has one or more items.
  • Items are listed in a catalog with names, prices, and descriptions.

The database might also include reports listing top-selling items or sales by month.
Stacked database reports generated from a Microsoft Access database, with structured summaries and tabular data. 🔍 Analysis: Database Reports
The reports appear to summarize and analyze data stored in the relational tables seen earlier. Based on the image structure and typical database use cases, these reports may include:
📋 Types of Reports Inferred
  1. Top-Selling Items Report
    • Grouped By: Item name or category
    • Metrics:
      • Total Quantity Sold
      • Total Revenue
      • Average Price
    • Source Tables: Order Details, Catalog Items
  2. Sales by Month Report
    • Grouped By: Month or Date
    • Metrics:
      • Number of Orders
      • Sales Revenue
      • Customer Count
    • Source Tables: Order Summary, possibly Customers
🧩 Underlying Data Sources
  • Order Summary: For `Order Date`
  • Order Details: For item-level sales quantities
  • Catalog Items: For item descriptions and pricing

🧾 Key Report Features in Access 2024
  • Group headers (e.g., month, product)
  • Subtotals and grand totals
  • Sortable columns
  • Filters (e.g., date ranges)
  • Export to PDF, Excel, or email
6) The database might also include reports listing top-selling items or sales by month.

Excourse on Generating reports from tables
The above diagram shows how these reports are generated from the tables and queries.
The above diagram shows how these reports are generated from the tables and queries.

An IS manager once asked me if I could create a database to track the computers at his company. This would be one way to do that:
7) An IS manager once asked me if I could create a database to track the computers at his company. This would be one way to do that:

A table listing each computer in the company with all hardware details
8) A table listing each computer in the company with all hardware details

A table listing the person who uses the computer, and where they sit,
9) A table listing the person who uses the computer, and where they sit,

A table listing all the software used at the company, and
10) A table listing all the software used at the company, and

A table listing which software can be found on which computer
11) A table listing which software can be found on which computer

Reports in the database might list all the people with each software package, or list computers by make or CPU speed.
12) Reports in the database might list all the people with each software package, or list computers by make or CPU speed.

Human resources department usually has plenty of information to keep track of about the employees of a company. This database design might give them a good start.
13) Human resources department usually has plenty of information to keep track of about the employees of a company. This database design might give them a good start.

It has a table listing employees by their ID number
14) It has a table listing employees by their ID number

A separate table listing confidential benefits information
15) A separate table listing confidential benefits information

A list of departments and department heads
16) A list of departments and department heads, and

A list of employees by department
17) A list of employees by department.

Some of the information in separate tables in this database could be stored in one database
18) Some of the information in separate tables in this database could be stored in one database.
However, it may be more convenient to separate the data if the different types of data will be updated separately.

The next lesson will explain how Access stores data in atable--the most basic database object.

Database Concepts - Quiz

Click the link below to take a short quiz on the database concepts.
Database Concepts - Quiz

SEMrush Software 3 SEMrush Banner 3