Different Types of Database Relationships
One question that you can expect to have when interviewing for a developer role, is database building. I recently interviewed for a position that gave me a scenario of building a database system for a doctor’s office. How would I keep track of Appointments, patients, doctors, billing, and lab results? Coming from a background in healthcare and having knowledge of the record system that we used in healthcare helped me understand the question and how to build this type of system. Technical interviews can be very nerve-racking. Making sure that I know the basics of relationships and how to connect the different tables of my relational database, was a key factor in doing well in the interview as well. With that, I wanted to continue writing a series of blogs about different aspects of databases to continue to prepare for these kinds of questions.
Any Association between two entity types is called a relationship. In the doctor’s office example, A doctor entity has a relationship with a patient entity. In a database, you can look up an appointment, but a patient’s name and also see that it’s connected to the doctor who saw the patient. But a doctor has many patients, and appointments to keep. The patients would also have many of their doctors in their care team as well as appoints to keep.
There are three main types of relationships between two tables
- One-to-One Relationship
- One-to-Many (or Many-to-One) Relationship
- Many to Many Relationship
One-to-One Relationship
This relationship exists when each record of one table is related to only one record of the other table.
In the hospital system, patients have an identification number, a CSN number. That one patient has one CSN number. That would be a One-to-One Relationship. Or a different example above, a person has one passport.
One-to-Many or Many-to-One Relationship
This relationship exists when each record of one table can be related to more than one record of the other table. From my own experience in writing databases, this is the most common relationship that is used.
There are a few examples of this when applying to the doctor’s office example. A Doctor or a Patient has many Appointments. A Patient has many Payments. In the image, the “many” relationship is represented by the “crow’s feet” and vertical bars. The Patient Payment relationship reads, “One Patient makes zero to many Payments”. Inspect the diagram and see if you can find the other one-to-many relationship.
Many-to-Many Relationship
This relationship exists when each record of the first table can be related to one or more than one record of the second table and a single record of the second table can be related to one or more than one record of the first table. It can be seen as two one-to-many relationships which are linked by a ‘linking table’ or ‘associate table’.
If we look at our hospital example a little deeper. We can see this relationship is present when linking patients and doctors using the appointment table. Using the appointment table as the join table. We create an indirect relationship between doctors and patients.
Now that we know the different types of relationships used in building a database, challenge yourself to come up with everyday scenarios to prepare yourself for these kinds of questions and how to apply them to the real world. Practice makes perfect!
