How to Use Associative Entities in Relational Databases

When you associate with someone or something you are connected in some way, this concept can be applied to relational databases through the use of associative entities. We will work through an example that shows how associative entities work. Hopefully, that will shed some light on different ways of thinking about associative entities and the many solutions they can provide in a business or in an academic environment.

An Example of When to Use Associative Entities

Say you own a movie rental service and your business is doing poorly. You want to track inventory using databases because you haven?t been doing this at all. So you create a database and a model where you can keep track of what movies your customers have rented out at any given moment.

You initially decide to use a one to many relationship (1:M). Where many movies can be attached to a single customer. The tables for that model would look something like this.

The customers and movies tables.

In this model whenever a movie is rented out to a customer, the customer_id column is set to that customer?s primary key. You know a movie is rented when that column is set, and you know a movie is available if that column is null.

A query to check if Fight Club is available would look like this:

select * from movieswhere customer_id is null and name = ‘Fight Club’

Mr. Bernard is a regular customer at your store. In fact he is your number 1 customer ? literally his id is 1. He usually rents about 2 movies a week. If at any given moment you want to know what Bernard is watching you can write the following query.

select * from movies where customer_id = 1

Boom, you just got all the movies he has currently rented out.

However, one glaring problem with this model is that if you?re owning a movie store it is very likely that you have multiple copies of a movie. This model would force you to create a record for each movie. So say you have 10 copies of The Matrix, your database would look something like this.

Oh look, Bernard is watching The Matrix this week.

I think you can see why this is a problem. You may have to represent each movie you own with a table record anyways, but the worse part of this model is the amount of repetitive data you are saving to the database every time. Also, you are very likely going to have more than 3 columns with movie data and other meta data. Just as an example there are movie titles, descriptions, versions, dates released, etc. etc. etc. Would you really want to save that for each movie record even if it?s the same? Hopefully the answer is no.

This is where associative entities come in handy.

Solution By Association

An associative table will allow us to represent each rental as a record. At the same time we can avoid storing repetitive data. As with anything there are pros and cons, but depending on the use case and data involved this is can be a more viable solution.

So here is what our new model would look like.

The table lk_customer_movies is our associative entity. The ?lk? part is so that you can quickly recognize that this table is a link table.

This model is a many to many relationship (M:M) between movies and customers. Whenever a movie is rented out to a customer, a lk_customer_movies record will be created. This record can hold additional information relevant to the business such as the date rented, rental length, rental price, etc. Better yet! If you wanted you could add an additional column to lk_customer_movies called transaction_id which would be the foreign key to the rental transaction that took place. The transaction_id column could point to a record stored in a table called transactions. Inside of that rental transaction you could store the details relevant to the business transaction that took place. Here is an example of the pliancy of this model to further extend our database.

It wouldn?t be difficult to add transactions table into the mix to keep track of customer transactions. Since a customer may rent multiple movies in one transaction, multiple lk_customer_movies can share the same transaction_id.

Here are some pros and cons!

Pros

1. less prone to human errors ? you can mess things up when entering information that is the same multiple times. What if you mistype ?Fight Club? as ?Fight Cub?, now you?re looking for a totally different movie about animals.
2. more organized ? there is a movies table which has data relevant to the movie and there is a link table (associative entity) which connects that movie to other tables. Additionally, the link table can also have its own relevant data like what time was the movie rented? When is it due?
3. less likely to quit of extreme boredom ? ah yes, you don?t want to make the data entry guy or gal want to quit with your new order of 1000 copies of ?The Avengers?. (You can also just write some code to do this)

Cons

1. More complex queries ? your queries will get more complex because you will most likely want to fetch data from various tables.
2. More complex data structure ? at first you might say WHAT DOES ALL THIS EVEN MEAN when you stare at it.

Alright, so now let?s say that Bernard rented out two movies this week. The Matrix and No Country For Old Men (Bernard has impeccable taste in movies). What would that look like?

With this structure, we only need to set our movie data once in the movies table. Our customers are connected to the movies via our bridge or link table, the lk_customer_movies table. Whenever we rent out a movie, a lk_customer_movies record is created.

Note: Notice how the movies table has a column called quantity. This column is used to indicate total copies of that movie. An alternative method of knowing how many copies you have could be to create a lk_customer_movies record for each movie copy right away.

So what would some of the queries look like?

Query to fetch movies rented by Bernard:

Query to fetch specific movie rentals and check availability:

Since we create a record in lk_customer_movies when a movie is rented. For this one we will have to add a new column to lk_customer_movies, we can add a returned_at date column to indicate the movie is no longer being rented and has been returned. Furthermore, this allows us to keep a record that the movie was once rented.

This would tell you how many copies of the movie are rented out and the max copies your store has, so you could compare the number of records returned to max copies and see if you have any left in stock.

Query to check how many movies are currently rented out:

Associative entities are not always helpful, it is important that you learn to distinguish when it is needed, otherwise you will add unnecessary complexity to your project. Here are a couple of thoughts that indicate an associative entity might help your data structure.

1. ?I have to insert repetitive data? ? this may be an indication that you need to separate the tables which represent your data. All tables represent data, but data can represent different things. Some data can represent information (like a movie title) whereas other data can represent something tangible or distinct (in this case a physical movie copy).
2. ?My relationship needs a relationship? ? This one sounds a little weird but if you find yourself saying this you might need an associative entity in your database.

There you have it! You should take the time to really understand the needs of your application before developing the architecture. As you ask yourself questions about data requirements you will probably come across a situation where you think ?an associative entity is perfect for this?.