A very simple computer system may be able to be supported by a very simple database design that only
includes a single table. However, if the database design needs to be enhanced to support more complex
requirements, the single table design would almost always end up being normalized into multiple
tabled linked together through relationships. This is required to reduce data redundancy and to
improve efficiency.
There are 3 types of table relationships:
1. One-to-one relationships
2. One-to-many relationships
3. Many-to-many relationships
One-to-One Relationships
In a one-to-one relationship, each row in one database table is linked to one and only one other row in
another table. In a one-to-one relationship between Table A and Table B, each row in Table A is linked
to another row in Table B. The number of rows in Table A must equal the number of rows in Table B.
It would be apparent that one-to-one relationships are not very useful since the database designer
might as well simply merge both tables into a single table. This is true in general. However, there are
some situations in which the one-to-one relationship may improve performance. For example, if a
database table contains a few columns of data that is frequently used and the remaining columns being
infrequently used, the database designer may split the single table into 2 tables linked through a one-
to-one relationship. Such a design would reduce the overhead needed to retrieve the infrequently used
columns whenever query is performed on the contents of the database table.
In a one-to-one relationship, each row in one database table is linked to 1 and only 1 other row in
another table. In a one-to-one relationship between Table A and Table B, each row in Table A is linked
to another row in Table B. The number of rows in Table A must equal the number of rows in Table B.
To illustrate the one-to-one relationship consider the sample table design and data below:
customer name table
column
cust_id primary key, link to cust_id of customer details table
lastname
firstnam
e
customer details table
column
cust_id primary key, link to cust_id of customer name table
height
weight
dateofbirth
cust_i
lastname firstname
d
0001 henry john
0002 smith adam
0003 johnson mary
0004 bailey harry
0005 morgan alex
cust_i
height weight dateofbirth
d
0001 181 75 03/05/1960
0002 179 82 06/08/1974
0003 171 65 04/01/1955
0004 185 93 05/05/1980
0005 168 71 09/09/1975
Notice that each row in the customer name table is related to 1 and only 1 other row in the customer
details table. Similarly, each row in the customer details table is related to 1 and only 1 other row in the
customer name table.
However, if you think about it carefully, the above relationship does not really bring any design
benefits. In fact, it would cause performance overheads to the database engine for having to link the
table rows together to service user queries related to customers. The 2 tables can actually be combined
into a single table as illustrated below:
customer table
column
cust_id primary key
lastname
firstname
height
weight
dateofbirth
cust_i heigh
lastname firstname weight dateofbirth
d t
0001 henry lohn 181 75 03/05/1960
0002 smith adam 179 82 06/08/1974
0003 johnson mary 171 65 04/01/1955
0004 bailey harry 185 93 05/05/1980
0005 morgan alex 168 71 09/09/1975
However, there are some situations in which the one-to-one relationship may improve performance.
For our example above, if the height, weight and dateofbirth columns are rarely used, it may make
sense to separate them out into a separate database table that is linked to the original table using a
one-to-one relationship. This would reduce the overhead needed to retrieve the height, weight and
dateofbirth columns whenever a query is performed on the lastname and firstname fields.
One-to-Many Relationships
In a one-to-many relationship, each row in the related to table can be related to many rows in the
relating table. This effectively save storage as the related record does not need to be stored multiple
times in the relating table.
For example, all the customers belonging to a business is stored in a customer table while all the
customer invoices are stored in an invoice table. Each customer can have many invoices but each
invoice can only be generated for a single customer.
In a one-to-many relationship, each row in the related to table can be related to many rows in the
relating table. This allows frequently used information to be saved only once in a table and referenced
many times in all other tables. In a one-to-many relationship between Table A and Table B, each row in
Table A is linked to 0, 1 or many rows in Table B. The number of rows in Table A is almost always less
than the number of rows in Table B.
To illustrate the one-to-many relationship consider the sample table design and data below:
authors table
column
author_id primary key
lastname
firstname
books table
column
book_id primary key
title
author_id foreign key - link to author_id of authors table
author_id lastname firstname
0001 henry john
0002 smith adam
0003 johnson mary
0004 bailey harry
book_i
title author_id
d
0001 A database primer 0001
0002 Building a datawarehouse 0001
0003 Teach yourself SQL 0001
0004 101 exotic recipes 0002
0005 Visiting europe 0004
Notice that each row in the authors table is related to 0, 1 or many rows in the books table. This makes
intuitive sense because an author can write 0, 1 or more than 1 books. In our example above, John
Henry has written 3 books, Adam Smith has written 1 book, Mary Johnson has not written any book
and Harry Bailey has written 1 book.
If you notice carefully, the above relationship between the authors table and the books table is a one-
to-many relationship. Turning around, the relationship between the books table and the authors table
is a many-to-one relationship.
Many-to-Many Relationships
In a many-to-many relationship, one or more rows in a table can be related to 0, 1 or many rows in
another table. A mapping table is required in order to implement such a relationship.
For example, all the customers belonging to a bank is stored in a customer table while all the bank's
products are stored in a product table. Each customer can have many products and each product can
be assigned to many customers.
In a many-to-many relationship, one or more rows in a table can be related to 0, 1 or many rows in
another table. In a many-to-many relationship between Table A and Table B, each row in Table A is
linked to 0, 1 or many rows in Table B and vice versa. A 3rd table called a mapping table is required in
order to implement such a relationship.
To illustrate the many-to-many relationship consider the sample table design for a bank below:
customers table
column
cust_id primary key
lastname
firstnam
e
products table
column
product_id primary key
name
mapping table
column
cust_id
product_id
Assume that the bank has only 2 customers and 2 products:
cust_i
lastname firstname
d
0001 henry john
0002 smith adam
product_i name
d
0001 savings
0002 credit card
cust_i
product_id
d
0001 0001
0001 0002
0002 0002
Notice from the mapping table, John Henry has 2 facilities with the bank - a Savings account and a
Credit Card. Also, notice that both the customers own Credit Cards issued by the bank. This means that
with the way the database tables are designed:
1. One customer can have 0, 1 or many products
2. One product can be owned by 0, 1 or many customers
