CCNC/CCNC Module 5/The database application/Database Design/Tables and Relationships
 |
Database Design |
Database Keys | Database Indexes | Tables and Relationships | Self Assessment | Summary & FAQs |
Tables and Relationships
Upon completion of this tutorial the learner will:
|
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).
- It is sometime useful to separate out large field types from the parent record to speed query performance on the parents core data. Once the desired record is fetched from the database than the larger photo and large text fields can be retrieved asynchronously.
ER diagram
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.
| person_id | first_name | last_name | birth_date | gender |
|---|---|---|---|---|
| 001 | Bill | Smith | 28/12/1963 | M |
| 005 | Fiona | Jones | 26/10/1961 | F |
| 012 | Lisa | Ballard | 11/03/1965 | F |
| person_id | photo_jpg | resume_txt | bio_txt |
|---|---|---|---|
| 001 | BINARY | LONGTEXT | LONGTEXT |
| 012 | BINARY | LONGTEXT | LONGTEXT |
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.
ER diagram
Sample data
| person_id | first_name | last_name | birth_date | gender |
|---|---|---|---|---|
| 001 | Bill | Smith | 28/12/1963 | M |
| 005 | Fiona | Jones | 26/10/1961 | F |
| 012 | Lisa | Ballard | 11/03/1965 | F |
| phone_type | phone_number | person_id |
|---|---|---|
| home | (604) 555-1234 | 001 |
| fax | (604) 555-2345 | 001 |
| cell | (778) 555-2333 | 001 |
| home | (604) 555-4455 | 005 |
| pager | (604) 555-9988 | 005 |
| home | (604) 555-6543 | 012 |
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.
ER diagram
Sample data
| person_id | first_name | last_name | birth_date | gender |
|---|---|---|---|---|
| 001 | Bill | Smith | 28/12/1963 | M |
| 005 | Fiona | Jones | 26/10/1961 | F |
| 012 | Lisa | Ballard | 11/03/1965 | F |
| address_id | address_line1 | address_line2 | city_name | region | country | post_code |
|---|---|---|---|---|---|---|
| 001 | 1234 Main Street | PO Box 1340 | Bowen Island | BC | CANADA | V0N1G0 |
| 002 | #302 1200 Water Street | Vancouver | BC | CANADA | V7G1N4 | |
| 003 | 450 Gower Street | St. John's | NL | CANADA | A1C2H7 | |
| 004 | 230 Commerce Way | Richmond | BC | CANADA | V6E1H7 |
| xref_id | address_type | person_id | address_id |
|---|---|---|---|
| 001 | Home | 001 | 002 |
| 002 | Work | 001 | 004 |
| 003 | Home | 012 | 003 |
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;
- cascade rules where a parent record can not be deleted without the child records also being deleted. For this could leave orphaned data.
- foreign key rules where a child record cannot exist in a related table without a parent record existing.
- zero or one rules where it is valid for a parent record to have no child records in a related table.
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.
Test your knowledge
|
|
