The right way to plan my database

Question

I'm creating a music sharing site, so each user can set up his account, add songs etc..

I would like to add the ability for users to give points to one another based on whether they like the song.

For example user1 has some songs in his collection, user2 likes a song so he clicks "I like" resulting in giving a point to user1.

Now I would like to know if my idea of creating the "Points table" in my database is somewhat right and correct.

I decided to create a separate table to hold data about points, this table would have id column, who gave the point to who, song id column, date column etc. My concern is that in my table I will have a row for every single point that has been given.

Of course it's nice to have all this specific info, but i'm not sure if this is the right way to go, or perhaps i'm wasting reasources, space.. and so on.

Maybe I could redesign my songs Table to have additional column points, and I would just count how many points each song has.

I need some advice on this, maybe I shouldn't really worry about my design, optimalization and scalibility, since todays technology is so fast and powerful and database queries are instant quick..

Answer 1

Part of the issue is really simple. If you need to know

• who gave a point
• to whom
• for which song
• on which date

then you need to record all that information.

Wasn't that simple?

If you only need to know the totals, then you can just store the totals.

As for scale, say you have 20,000 users, each with an average of 200 songs. Let's say 1 in 10 gets any up votes, averaging 30 per song. That's 4 million user-songs; 400,000 that get up votes, at 30 per song you have 12 million rows. That's not that many. If the table gets too many rows, partitioning on "to whom" would speed things up a lot.

Answer 2

IMO, it's better to use a transactional table to track the points given to a user based on their song-lists. Consider how Stackoverflow (SO) works, if you up-vote a question or solution, you can remove your vote at a later time, if SO used a summation column, it would be impossible to support this type of functionality.

I wouldn't worry too much about the number of rows in your points table, as it will probably be pretty narrow, generously; 10 columns at the most. Not to mention the table would be a pivot table between users, so would comprised mostly of int values.