The Hermit Hermit's Free Library  Database

Intro to Relational Database Design

Presents terminology and concepts of DBMS and introduces relational database design.

Database Management Systems

Database management systems (DBMS) are collections of tools used to manage databases. Four basic functions performed by all DBMS are:

A short list of database applications would include:

Database Components

Looking from the bottom up, a database is composed of fields, records, and related tables.

Field

A field is an area (within a record) reserved for a specific piece of data. Examples: customer number, customer name, street address, city, state, phone, current balance.

Fields are defined by the following information

Record

A record is the collection of the data contained by all fields which pertains to one entity: eg. a person, product, company, transaction, etc.

Table

A table is a collection of related records. Examples: employee table, product table, customer table, orders table.

In a table, records are represented in rows and fields are represented in columns.

Table structure is determined by the characteristics of its fields.

Database

A database is a collection of related tables, such as a company's sales force, customers, and sales orders.

Database structure is determined by the relationships between its tables.

A database can also include other objects, such as queries, forms, and reports.

Relationships

There are three possible relationships between two or more tables:

An example of a One-to-Many relationship would be a Customer table and an Orders table: each order has only one customer, but a customer can make many orders.

One-to-Many relationships consist of two tables, the "one" table, and the "many" table.

An example of a Many-to-Many relationship would be an Orders table and an Inventory table: an order can contain many inventory items, and each inventory item can appear on many orders.

A Many-to-Many relationship consists of three tables: two "one" tables, both in a One-to-Many relationship with the third table. The third table is sometimes referred to as a lien because it joins the two "one" tables by combining the foreign keys from both. The lien table between an Inventory and an Orders table would represent the line items on an invoice.

Diagram of a many-to-many database relationship

Key Fields

For two tables to be related, they must share a common field, called a key field. The key field in the "one" table of a One-to- Many relationship is referred to as a primary key. The same key field is called the foreign key when it appears in the "many" table.

Primary key

A Primary key is a field or a combination of two or more fields whose value uniquely identifies one record in the "one" table.

Customer ID numbers would be the primary key for a Customer table because a customer id number identifies one and only one customer.

By the same logic, the primary key for an Orders table would be the Order ID numbers. One order id number = one specific order.

Foreign key

When a "one" table's primary key field is added to a "many" table in order to establish a common field to relate the two tables, it is called a foreign key in the "many" table.

The primary key in a Customers table, Customer ID number, becomes a foreign key in Orders, the "many" table.

The foreign keys in Orders relate each record uniquely to one record in the Customer table.

Diagram of a many-to-many database relationship, including primary and foreign keys

Rationalization and Redundancy

Grouping logically-related fields into distinct tables, determining key fields, and then relating tables through their common key fields is called rationalizing a database.

There are two major reasons for designing a database this way:

For example, in the Customers/Orders database, we want to be able to identify the customer name, address, and phone number for each order, but we want to avoid repeating that information for each order. Repeating customer information over and over would take up storage space needlessly and make the job of updating customer addresses difficult, time-consuming, and likely introduce errors in the data.

To avoid redundancy:

  1. Place all the fields related to customers (name, address, etc.) into a Customer table and create a Primary key field which uniquely identifies each customer: Customer ID.
  2. Put all the fields related to orders (date, salesperson (key), total, etc.) into the Orders table.
  3. Include the Primary key field (Customer ID) from the Customer table in the table for Orders.

The One-to-Many relationship between Customer and Orders is defined by the common field Customer ID. In the table for Customers (the "one" table) Customer ID is a primary key, while in the Orders table (the "many" table) it is a foreign key.

Also see:
Mail Order Database Design Case
and
Rationalizing Database Structures