Home Logo
Show Full Menu
Hide Full Menu
 

Data Relationships

Created: 22/10/10 Views: 2322 myFave Image


In this introduction we will learn about data relationships and how this is connected with MySQL
Data Relationships basically determine how two tables are linked together and by which entities. If you have studied entity relationship diagrams before then you should already know most of what you are about to learn.

    To begin with I will list the relationship types
  • One to One
  • One to Many
  • Many to Many

We use entity relationship diagrams so that we can organise our table structure correctly and in the most efficient way as well as removing redundant data, so if you are doing a real job and are in the planning stage, coming up with the entity relationship diagram is one of the first things you do so that you can make sure your database will function correctly before you start writing any script, the last thing you want is to create a database where the tables don't connect together as you wish after you have written out the code!!

So now lets explain these relationship types in a bit more detail
One to One: This basically means that one primary key can only be linked to the one foreign key, and that foreign key can only be linked to that one primary key. So for an example, if we were to link 'Ticket' table with 'Customer' table, and each ticket could only belong to the one customer, and that customer could have one ticket, that that means it is a One to One relationship.

Customer
customer_id [Primary key for Customer table]
firstname
lastname
date_of_birth

Ticket
ticket_id [Primary key for Ticket table]
customer_id [Foreign key for Customer table]
ticket_details

These two tables would be linked together using customer_id as it is the primary key in the customer table, and the foreign key in the ticket table.
This would allow us to display the customers details, and then search in the ticket table for the customer_id and show the ticket details that are linked.

One to Many: This means that the primary key can be linked to many foreign keys, but the table where the foreign key is held can only link to one primary key. This is definitely the mostly used relationship type when creating a database structure that requires tables to link together. For example, one customer can buy many products, but that product can only belong to one customer.

Customer
customer_id [Primary key for Customer table]
firstname
lastname
date_of_birth

Product
product_id [Primary key for Product table]
customer_id [Foreign key for Customer table]
name
description
price

So these two tables would be linked together using the primary key from the customer table and the foreign key in the product table. To show the related data we would be able to display the customer's details, and then search in the product table for the customer_id and display all the related products. Alternatively, we could display a product and use that forgeign key of customer_id to find the customers details with the primary key of that customer_id.

Many to Many: This basically means that one primary key can be linked to the multiple foreign keys, and that foreign key can be be linked to multiple primary keys. So for an example, if we were to link 'Tracher' table with 'Student' table, and each teacher can have many differnet students, and the student can have many different teachers, that means it is a Many to Many relationship.

Teacher
teacher_id [Primary key for Teacher table]
firstname
lastname
subject

Student
student_id [Primary key for Student table]
firstname
lastname
date_of_birth

Now as you should be able to tell right away that there has been no foreign keys defined, and that is because it is not practicle to link a Many to Many relationship like this. If we were to link these two tables as they are now we would have to include multiple entities for primary keys, as shown below,
Teachers
student_ida
student_idb
student_idc
student_idd
student_ide

Student
teacher_ida
teacher_idb
teacher_idc
teacher_idd
teacher_ide
It should be obvious how impraticle this method is, and also if you don't know the definite amount of times these tables link then when do you know where to stop adding entities.
To solve this problem you basically turn it into a One to Many relationship again by creating a link table inbetween them.

Teacher
teacher_id [Primary key for Teacher table]
firstname
lastname
subject

LinkTable
teacher_id [Foreign key for Teacher table]
student_id [Foreign key for Student table]

Student
student_id [Primary key for Student table]
firstname
lastname
date_of_birth

This way we can link each the Teacher and Student table with the LinkTable table in a One to Many relationship.
We can display the teachers details and search for the teacher_id in the link table, for each of the foreign keys found to match, we can then grap the student_id that is associated to it, and by grabbing this studen_id foreign key, we can search the Students table for the student's details.


next tutorial: Creating a Database

There are no comments