Table Relationship: One-To-Many
Before we jump right into building a project using ActiveRecord, we should take a moment to review relationships between database tables. One-to-many, many-to-many, one-to-one. Those terms are very important, but sometimes we still get confused when thinking about relationships between tables.
For example, I want to build a project for stock trading. I need a stocks table, a users table, and a transaction table. The transactions table will be a join table that connects the users table and stocks table. One user can have many transactions. One stock can also have many transactions. But one transaction can only have one user and one stock. Then we will have following relationship among those three tables:
- users table to transactions table is one-to-many (1:M)
- stocks table to transactions table is one-to-many (1:M)
I also want a table to store each users individual stock holdings, I’ll call it the portfolios table. It will also be a join table between users table and stocks table.
This is where I got confused. I was thinking: “Hmm… one person can only have one portfolio, and one stock can only be in one portfolio. One portfolio can only have one person. But one portfolio can have many stocks. Wait… many people can have the same style of portfolio as well…”
Do you see where I went wrong ? I was not thinking from a table and records perspective. “ In a one-to-many relationship, one record in a table can be associated with one or more records in another table. For example, each customer can have many sales orders.” In my case, my portfolios table should store all the users stock holdings. Like this:
As you can see, in the portfolios table, it can has one or more records associated with the users table and the stocks table. So the relationship between users table and portfolios table should be one-to-many (one user has many portfolios and one portfolio belongs to one user). The same for the stocks table and the portfolios table.
- users table to portfolios table is one-to-many (1:M)
- stocks table to portfolios table is one-to-many (1:M)
Draw the table out like the picture above. You can use a couple of fake records to populate the table which is also a good way to help us figure out the relationships among tables. It can help us visualize what kind of data we want to store in this table and how many records can be associated with the record on other tables.
This is the final relations among all the tables:
Helpful resources:
Table relationships overview:
Database design tool I used: