Table associations in relational databases
When creating a large express application with a back end database, understanding how these databases are associated with one another will be crucial to ensuring that the application functions correctly.
If this is your first time setting up a database, there are some great resources out there to help you get started. Here’s a link to a previous blog post that may help you get started, in case you need a refresher:
Today, I’ll again be using postgres and sequelize. Now, you may be asking yourself, what’s the point of table associations, and why would it be important? Well, let’s imagine that you’ve grown tired of social networking. It’s cumbersome, and filled with tons of people that you’ve forgotten who they are. I don’t want to use a specific example, so we’ll make up a completely hypothetical example that we’ll call “bookFace.” You’ve decided to leave bookFace in the past, and you’re creating your own social networking platform so that you can add and keep in contact with only your closest friends. As you begin the project, you may quickly realize that a back end database will be necessary to store data, such as users, posts, and comments. Briefly, let’s take a look at what those tables might look like.
How would these tables be associated with each other? Well, one user might have many posts, and many comments. A post could also have many comments, but would be associated with both the user who made the post, and the user on whose profile the post belongs. A comment could belong to only one post, and be associated with the user who created it. Here’s a visualization of how those associations might look.
Understanding these associations beforehand will help your organize your database, and ensure that as users input information into the database, that comments are correctly associated with the right users, and users are associated with the correct posts. In order to do that, we may need to add new columns to our tables to give them the correct associations. Let’s see an visualization of what that might look like:
As you can see, simply adding a user_id to a comments or post table would allow us to associate a comment or post with another user. To better understand this concept, let’s go over a few terms.
Associations: relationships between models.
Source model: the model defining an association.
Target model: the model to which an association is being defined.
Foreign key: a database column that contains references to another table.
Target key: a database column that a foreign key references.
If you’ve set up your databases on and then use sequelize to migrate your databases, you can create these associations there. For example, in sequelize, you might call a method such as user.hasone(comment). This is the same as saying that the user model (the model that the function is being invoked on) is the source and that the comment model (the model being passed as an argument) is the target.
One to one associations
The first type of association is one to one associations. Here, two models are connected by a single foreign key.
BelongsTo
The BelongsTo associations are ones where the foreign key for the one to one relation exists on the source model. An example of this would be our comment being part of a post, where the foreign key would be on the comment.
By default the foreign key for a belongsTo relation will be generated from the target model name and the target primary key name.
Target keys
A target key is the column on the target model that the foreign key column on the source model points to. By default, the target key for a belongsTo relation will be the target model’s primary key.
Has one
The second type of association is the has one association. This creates an association in which the foreign key for the relationship exists on the target model, thus establishing a one-to-one relationship.
What’s the difference between Has One and Belongs To? Let’s use our User and Post example from earlier to see the difference. When we link to models in sequelize, they are paris of source and target models.
If we define the Post as the source and the User as the target, it would look like this:
post.belongsTo(User);
or
post.hasOne(User);
Or if we define the User as the source and the Post as the target, it would look like this:
User.belongsTo(Post);
or
User.hasOne(Post);
One To Many Associations:
Another type of association is One to many. In our social networking site, this may be the association that we would most often need to use, as users would have many posts, and posts may have many comments. In sequelize, there are two ways we could define this:
hasMany or belongsToMany
hasMany creates an association in which the foreign key for the relationship exists on the target model. Establishes a one-to-many relationship.
belongsToMany creates an association in which there are two foreign keys on a third table. Establishes a many-to-many relationship.
As we begin building our site, making sure we are using the right associations between tables will ensure that our content updates and is displayed correctly.
To see how these associations look in your text editor, and in a program like postico, I’ve uploaded a couple of screenshots for reference.
On the first image, you can see both the structure of my express application, and in the comments model, I’ve defined the association. Then, in postico on the right, the column has been added on the right for the postId. I will continute to update this as I work on the project, but, if you have any questions or feedback, please feel free to reach out. Thanks!
