Database Reference
In-Depth Information
Implementing one-to-many relationship
It is recommended to have multiple tables to store items instead of only one. For example,
consider the blog table we discussed earlier. There, we might have the comments posted by
various readers. We have two choices: first, to store all comments for a related blog in the
same table, and second, we can have a separate table with all comments stored for various
blogs. Here, if we go with the first option, we might not be able to store all attributes con-
sidering the item read and write limitations.
Also, we might not know how many comments we are going to get and what its size would
be. So, here it is recommended that we have two separate tables to store the blog and the
respective comments on the blogs. Having two tables would also help in isolating faults.
Here, if comments tables are throttled, it would not harm the loading of the blog table,
hence the recommendation.
There are multiple benefits in having a one-to-many relationship instead of storing
everything in one table. The following are some of them:
• Having separate tables for different items helps in maintaining the data as some-
times you might want to retain one but delete the other. In this case, you don't need
to delete the complete record; you can simply delete the item you don't wish to see
anymore.
• Having multiple tables help us to cater to the size limitation enforced by Amazon.
Here, you can have as many comments for a certain blog post, and there would not
be any concern about the size limit.
• If we have data stored in separate tables, at the time of querying you can select
only the desired data from the desired table instead of fetching all the data for each
and every query. This way, you can save money as well as time.
• Also, if we store all comments for a blog post in a single item only, then to retrieve
a certain comment you would need to fetch all replies all the time, which is not ef-
ficient.
It is also recommended that we have a one-to-many relationship for varied access patterns.
This means that if you know that some attributes are required to be fetched more frequently
than others, then you should create a table of such items, and you should create another
table to store the less-frequently accessed attributes. The reason behind doing this is to
fetch data in an efficient manner. This means that if certain attributes are not required most
of the time, then if we keep all attributes in one table, we would be unnecessarily fetching
them.
