Q: Data Relationships, what does that mean?

A: It means data, usually in a database, can be related to other data in the database.

Q: Really? How can data in a database be related to other data in a database? Are you saying one piece of data can begat another piece of data, or that two pieces of data can get married?

A: As a matter of fact, that is kinda what is being said.

There are three types of data relationships in a database. They are…

• One-to-one
• One-to-many
• Many-to-many

One-to-one

A one-to-one relationship is kinda like marriage. I have one wife. She has one husband.

Similarly in a database you may have a record in one table that has only one record in a corresponding table. For example, suppose you have a database that tracks employee information. In that table you may have fields for employee ID, first name, middle name, and last name, office phone number, title / position, etc. You could have another table for private employee information such as social security number, birthdate, street address, city, state, zip, etc. This second table would also have the employee number so each record in the employee table can be related to one, and only one, record in the employee table. Thus, the two tables have a one-to-one relationship.

One-to-many relationship

I am a father. I have seven children. Thus, one might say the relationship between me, and my children is one-to-many.

In the human resource database, you may have a responsibilities table that tracks the many responsibilities assigned to each employee. Thus, the relationship between the employee table and the responsibilities table is one to many. To make this relationship work, the responsibilities table will also need the employee id, so that each responsibility can be connected back to the assigned employee.

Many-to-many relationship

As you know, human families can be complicated. Due to death, divorce, or other circumstances a man may father children with multiple women. Similarly, a woman may give birth to children from multiple fathers. Thus, the relationship between partners can be many-to-many.

The same is true in a database. In our human resources database, for consistency’s sake, we may want to have a master list of responsibilities such as Sales, Operations, Accounting, etc. This will only have one field, responsibility name. Some employees may be assigned one responsibility, and others may be assigned many responsibilities. To accommodate this, notice the third table, named Employee Responsibilities, has three fields:  Employee ID tracks the employee, Responsibility Name  tracks the responsibility, and Hours Spent Per Week tracks the hours they are expected to spend each week on that responsibility.

Notice, the Employee Responsibilities table is a child of the two parent tables. This shows that a many-to-many relationship is actually two separate one-to-many relationships.

This blog post has shown that relationships can and do exist in the data in a database. We have seen three types of relationships: one-to-one, one-to-many, and many-to-many. We have also see that common fields are needed in both tables in order for them to be related.

If you would like some help designing the relationships of your database please schedule a meeting with a TechMentor.