CCNC/CCNC Module 5/The database application/Database Design/Tables and Relationships

Relating Tables
Designing database tables and database normalization becomes an exercise in understanding the data which resides in all the tables of the whole database. Developing an excellent understanding of how each row of data in one table relates to a row of data in another table becomes the 'art' of database normalization. It is the relationships between the tables which will provide accuracy, integrity and performance to database design. There are three types of relationships (or cardinality) in database design, they are; one-to-one, one-to-many and many-to-many. When thinking about the relationship between tables think about parent child relationships; i.e. one cannot exist without the other, a child cannot exist without a parent.

One-to-One Relationships
One-to-One table relationships exist when a two tables are related by a single row. For every row in one table there can be either zero or one row in the other table. The One-to-One relationships often store the optional data for the parent record.

In the following example the 'People' table has a related (child) table named 'People_Attributes'. This table contains the optional information of a persons photo, their resume and a biography. As is shown in the diagram the person_id field is the primary key (PK) for the 'People' table and also the primary key (PK) for the 'People_Attributes' table. Given the 'People_Attributes' person_id field relates back to the 'People' table it is also a foreign key (FK).

Sample data
''Note: the 'People_Attributes' table contains references to BINARY and LONGTEXT. In an actual database the fields would contain large files of binary or text data.''

One-to-Many Relationships
One-to-Many table relationships exist when many records in one table relate back to a single row in another table (the parent). For every row in one table there can be either zero or many rows in the other table. The One-to-Many relationships are the most common type of relationships.

In the following example the 'People' table has a related (child) table named 'PhoneNumbers'. This table contains all the phone numbers for each person in the 'People' table. As is shown in the diagram the person_id field is the primary key (PK) for the 'People' table and the foreign key (FK) for the 'PhoneNumbers' table.

Many-to-Many Relationships
Many-to-Many table relationships exist when many records in one table relate many records in another table. For every row in one table there can be either zero or many rows in the other table and visa versa. The Many-to-Many relationships cannot be physically implemented as a Many-to-Many relationships in the database. This type of relationship is a logical relationship. It is implemented at two One-to-Many relationships. See the ER diagram below for an example of this.

In the following example the 'People' table has a related 'Addresses' table and the 'Addresses' table has a related 'People' table. This table relationship implies that people can reside at MANY addresses (home and business), and an address can contain MANY people. As is shown in the diagram a cross reference (xref) table exists that provides the two way One-to-Many relationships that forms the Many-to-Many relationship.

Validating Relationships
When defining the table relationships rules can be applied to the relationship. These rules ensure that data in the database keeps its integrity. The types of rules include; The topic of validation rules can continue into an more advanced topic. What is important is to understand that the rules for valid data can be enforced within the database.
 * 1) cascade rules where a parent record can not be deleted without the child records also being deleted. For this could leave orphaned data.
 * 2) foreign key rules where a child record cannot exist in a related table without a parent record existing.
 * 3) zero or one rules where it is valid for a parent record to have no child records in a related table.