Computer Science Canada

Best way to store stuff in mysql
Author:  nonamedude [ Thu Apr 14, 2011 6:52 pm ]
Post subject:  Best way to store stuff in mysql
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
Author:  Tony [ Thu Apr 14, 2011 7:24 pm ]
Post subject:  RE:Best way to store stuff in mysql
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?
Author:  nonamedude [ Thu Apr 14, 2011 7:31 pm ]
Post subject:  Re: Best way to store stuff in mysql
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.
Author:  Tony [ Thu Apr 14, 2011 7:39 pm ]
Post subject:  RE:Best way to store stuff in mysql
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?
Author:  nonamedude [ Thu Apr 14, 2011 7:41 pm ]
Post subject:  Re: Best way to store stuff in mysql
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 Neutral
Author:  Tony [ Thu Apr 14, 2011 7:53 pm ]
Post subject:  RE:Best way to store stuff in mysql
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).
Author:  nonamedude [ Thu Apr 14, 2011 8:02 pm ]
Post subject:  Re: Best way to store stuff in mysql
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
Author:  Tony [ Thu Apr 14, 2011 8:09 pm ]
Post subject:  RE:Best way to store stuff in mysql
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.
Author:  nonamedude [ Thu Apr 14, 2011 8:10 pm ]
Post subject:  Re: Best way to store stuff in mysql
Oh i see, THANKS a lot for your help, helps me a ton.

:


You can syndicate this boards posts using the file backend.php or view the topic map using sitemap.php.

Terms of Use | Privacy Policy