----------------------------------- nonamedude Thu Apr 14, 2011 6:52 pm 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[/code] I am just wondering, what the most efficient method of storing this type of info would be ----------------------------------- Tony Thu Apr 14, 2011 7:24 pm 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? ----------------------------------- nonamedude Thu Apr 14, 2011 7:31 pm 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. ----------------------------------- Tony Thu Apr 14, 2011 7:39 pm 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? ----------------------------------- nonamedude Thu Apr 14, 2011 7:41 pm 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 :| ----------------------------------- Tony Thu Apr 14, 2011 7:53 pm 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) [/code] 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) [/code] 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) [/code] 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 Thu Apr 14, 2011 8:02 pm 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 ----------------------------------- Tony Thu Apr 14, 2011 8:09 pm 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. ----------------------------------- nonamedude Thu Apr 14, 2011 8:10 pm Re: Best way to store stuff in mysql ----------------------------------- Oh i see, THANKS a lot for your help, helps me a ton.