i’m working on a personal social media platform for a university which implements a star like system and i’m just counting votes and sum of stars on each request so i’m looking for a better more scalable option. should i use the counter in database like (total views + 1) each time someone votes ? or there are other more scalable methods ? this well help me also build "followers counter, following counter, number of posts a user has…)
I would personally use a small table with two foreign keys to your user table.
Table: Followers
-------
UserID - PK
FollowedByUserID
Then to get your followers, you simply count the rows
SELECT COUNT(*) FROM Followers WHERE UserID = 12345
If you want a count of the people he follows, you switch the query
SELECT COUNT(*) FROM Followers WHERE FollowedByUserID = 12345
Now if the site gets REALLY busy, you could consider adding a counter field to the user table which contains those metrics, but that also means you have two places to maintain when a follower is added/removed. That could be done via a trigger but something that would need to be considered that there are two maintenance points.
there’s an old saying in IT – premature optimization is the root of all evil
don’t go looking for tricks to outsmart the database engine until you’ve got maybe a million users
until then, obtain your counts by running SELECT COUNT(*)
queries from the basic tables
proper indexing will ensure excellent performance
yes but ofc i have to be aware of everything first to avoid future problems just for the sake of learning
there should be reasonable limits, though
you do ~not~ need to know how big sites like Facebook and Twitter handle their database design and optimize their queries
start with the basics, and remember that old IT saying
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.