asked on
Database Design (Performance Choice)
I am building an app with another developer at the moment, and we are struggling to agree on the design of a particular function :-)
One part of our app allows users to comment and like posts (similar to facebook)
So we have the following tables (in relation to this part of the app) in our Parse.com database:
- users (id, fullname, email, username, etc)
- posts (id, user_id, message, etc)
- replies (id, user_id, message, etc)
- likes (id, user_id, post_id, etc)
However, the other developer feels that in order to load the posts quicker, we should also store the username and number of likes in the posts table too (in addition to the other tables), so that the app loads the data faster, as it wouldn’t have to query all of the tables for the all the data for a post, so it could get the username, no. of likes from the 'posts' table alone.
I firmly believe that you should never store the same data twice in a database, and that you should use an id to link all of the pieces of data together from different tables.
What do you think is the best approach?
One part of our app allows users to comment and like posts (similar to facebook)
So we have the following tables (in relation to this part of the app) in our Parse.com database:
- users (id, fullname, email, username, etc)
- posts (id, user_id, message, etc)
- replies (id, user_id, message, etc)
- likes (id, user_id, post_id, etc)
However, the other developer feels that in order to load the posts quicker, we should also store the username and number of likes in the posts table too (in addition to the other tables), so that the app loads the data faster, as it wouldn’t have to query all of the tables for the all the data for a post, so it could get the username, no. of likes from the 'posts' table alone.
I firmly believe that you should never store the same data twice in a database, and that you should use an id to link all of the pieces of data together from different tables.
What do you think is the best approach?
DatabasesCell PhonesWeb Development
Last Comment
Jason C. Levine
I'm on your side, but also I think it is better to store (posts, replies, likes) in one table with a record flag and parent id to point replies and likes to the posts or replies, this case with proper indexes there will be no much performance differences, plus maintaining correct count in the table will be extra overhead
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks guys.
@farwest: can you elaborate on your thinking please.
Do you mean have one table called 'content' and with several fields to facilitate for posts, replies and likes?
@farwest: can you elaborate on your thinking please.
Do you mean have one table called 'content' and with several fields to facilitate for posts, replies and likes?
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks.
But then a like would end up having several extra fields that it does not need, but posts needs them?
But then a like would end up having several extra fields that it does not need, but posts needs them?
Already starting to get away from a normalised database here - duplicating data / using magic numbers / multi-use tables etc. An hour working with that and you'd run into problems!!
I think this apply only on message text field
using Varchar types only consume space when needed,
no noticeable impact on DB is expected
using Varchar types only consume space when needed,
no noticeable impact on DB is expected
@Chris in theory what you are saying is correct, but in my opinion for this specific case the is is the best normalization level I can think of to balance with performance and complicity
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.