Relationships between tables are grouped into 4 groups:
- One-to-one (1:1) - each record in Table A can have only one matching record in
Table B, and each record in Table B can have only one matching record in Table A.
This type of relationship is not common and it is very rarely used in database systems,
but it can be very useful way to link two tables together. The information related
in this way could be in one table. However, you might use a one-to-one relationship
to divide a table with many fields in order to isolate part of a table for security
reasons, or to store information that applies only to a subset of the main table,
or for efficient use of space. A one-to-one relationship is created if both of the
related fields are primary keys or have unique indexes.
- One-to-many (1:M) - is the most common type of relationship and it is used to
relate one record in a table with many records in another table. In a one-to-many
relationship, a record (parent) in Table A can have many matching records (children)
in Table B, but a record (child) in Table B has only one matching record (parent)
in Table A. This kind of relationship is created if only one of the related fields
is a primary key or has a unique index.
- Many-to-one (M:1) - is used to relate many records in a table with only one (single)
record in another table. It is often called the lookup table relationship. Normally,
this kind of relationship is not based on a primary key field in either table. Although
in theory this relationship is one-to-one, it is known as many-to-one because it
does not use a primary key field for the link, and many records from the primary
table link to a single record in another table.
- Many-to-many (M:M) - is used to relate many records in a table with many records
in another table. A record (parent) in Table A can have many matching records (children)
in Table B, and a record (child) in Table B can have many matching records (parents)
in Table A. It is the hardest relationship to understand and it is not correct.
By breaking it into two one-to-many relationships and creating a new (junction/link)
table to stand between the two existing tables (e.g. whose primary key may consist
of two fields - the foreign keys from both tables A and B) will enable correct and
appropriate relationship setting. A many-to-many relationship is really two one-to-many
relationships with a junction/link table.
Normalization
- Microsoft's definition
- 1 - No repeating rows or groups
- 2 - #1 and Eliminate Redundant Data
- 3 - #1 and #2 and Eliminate Data Not Dependent On Key
- Another good definition
- 1 - No repeating rows or groups
- 2 - Each column must depend on the *entire* primary key.
- 3 - Each column must depend on *directly* on the primary key.