Why you never should use an ID in a connection table

Today we as project group had a problem that was difficult to solve. In the end Gerard Lolkema and Ruben Swieringa also tried to fix it and finally we solved it as a group.

Introduction:

I use an example to explain this problem. I start with 2 tables and 1 connection table to connect them.

tables_problem

Movie and user has a “has_and_belongs_to_many” relation with each other because a movie can be a favourite of multiple users and a user can have multiple favourite movies.

A movie page contains all the details of a movie and shows a list of users that have that movie as there favourite. To get that list of all the users you can just say:

movie.users

Of course you now only have the objects so you have to put it in a for loop to get any useful data like the username:

for user in movie.users
  user.username
end

The problem:

tags_problem

If you now want all the tags (tags table looks like the image above) of a user the way to do this is this:

for user in movie.users

	user.username

	for tag in user.tags

		tag.text

	end

end

This is giving the wrong result. Tags are display at the wrong users and some users has no tags. I try to explain the problem with some actual data.

users_datausers_movies_datamovies_data

if you have the data in the tables like above and you try to get all usernames that have Ratatouille as favourite movie and you are using this for loop:

for user in movie.users

	user.username

end

You get the names “Gerard” and “Arno” back. That is what I expected so that is correct. Now we try to show all tags of that users in the movie page. This is the tags table
tags_data

We are using this for loop:

for user in movie.users

	user.username

	for tag in user.tags

		tag.text

	end

end

For the user “Gerard” I get the next tag back “User 3” and for “Arno” I get nothing back.

So because the connection table users_movies has a id the id of the user is overwritten by that one. Because the id of the connection between that movie with the user “Gerard” is 3 Ruby takes that id to find the tags. And that results in the tags of user 3 “Ruben”.

Conclusion:

We learned our lesson the hard why for us it took a 1/2 day to solve this problem because our test database is already a lot complexer than this example. Someone already told us that we not really needed a id in the connection table but now we know why.

Author Bio

Leave a reply