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.
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.
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.
