Best way to store stuff in mysql

[nonamedude]

Hello guys,

I am currently working on a website, and it has a similar structure to reddit/forums/ and news site.

The problem that I have is that, for every user that registers, I am making 6 tables. Apparently this is overkill, but I cant seem to think of a better method.

So right now I have

code:

userName_comments // stores wherever the user has commented userName_friends// stores the users friends userName_following // stores who the user is following as in, maybe an editor he likes so he follows his posts userName_info// all the information pertaining to the user userName_story// all the stories that the user posted with all its details userName_rating// all the ratings that the user made, so that duplicate ratings cannot be made

I am just wondering, what the most efficient method of storing this type of info would be

[Tony]

You are making new tables per users? That required a triple-take, but that appears to be indeed what you are doing. Why?

E.g. why would you want to have a table for a particular user's comments, instead of having a single table for all of the comments?

If all of the ratings are in different tables, how do you go about finding the summative/average rating for any one particular post?

[nonamedude]

Ok, why you ask,

First of all, there is a table with all the comments and stories and this is where the summative rating is calculated from. Secondly, I have a separate table for votes of the user because if this is not here, how would I know which articles the user voted up/down so that they cannot change it again. If I were to have only one table containing all the votes, I dont know how I would know which user upvoted what and where.

[Tony]

wait, but you don't have a separate table for each username_articleid_rating, just username_rating, right?

So if username_rating has more than one entry with ratings, how do you know which articles are upvoted?

[nonamedude]

Yes I only have username_rating
and

"So if username_rating has more than one entry with ratings, how do you know which articles are upvoted?"

Yea, this is what I am trying to achieve without so many tables

[Tony]

so if you had to rate a bunch of articles and record that information on a piece of paper, how would you do it? Probably some sort of an article_id => rating type of records

code:

mysql> select * from ratings; +------------+--------+ | article_id | rating | +------------+--------+ |          1 |      5 | |          2 |      4 | +------------+--------+ 2 rows in set (0.00 sec)

So we can look up what rating we gave to article that has ID of 1 (we rated it 5).

What if you were keeping track of ratings made by multiple of your friends? Sure, you could have a separate piece of paper for each friend (new tables), or you could write who rated what on the same row.

code:

mysql> select * from ratings; +------------+--------+------------+ | article_id | rating | username   | +------------+--------+------------+ |          1 |      5 | Tony       | |          2 |      4 | Tony       | |          1 |      4 | nonamedude | +------------+--------+------------+ 3 rows in set (0.00 sec)

Here we both rated article 1, and can easily look this up:

code:

mysql> select avg(rating) from ratings where article_id = 1; +-------------+ | avg(rating) | +-------------+ |      4.5000 | +-------------+ 1 row in set (0.07 sec)

Of course instead of a username, that column typically would record a user id, so that it could be linked to information elsewhere (such as user_info contents).

[nonamedude]

Oh i see, yea, that works perfectly but would the length of the table matter in its efficiency?,

I could see how we can get rid of these tables

userName_story// by just adding the person who posted it to the article table
userName_rating


But what about tables such as the ones below

userName_comments // stores wherever the user has commented
userName_friends// stores the users friends
userName_following // stores who the user is following as in, maybe an editor he likes so he follows his posts
userName_info// all the information pertaining to the user

[Tony]

You can essentially collapse all of the userName_whatever tables into a single "whatever" table by adding a user_id column to it.

The length of the table begins to matter when it starts having millions of rows. Once you have properly indexed, it can still reasonably go for a while more.

Looking up something in a single table with 10,000 rows is incredibly faster than looking for same information in 1000 different tables, with 10 rows each.

[nonamedude]

Oh i see, THANKS a lot for your help, helps me a ton.