gapern Posted December 5, 2009 Share Posted December 5, 2009 Hi there. So my users are going to be able to add "favorites" to a list so they can access them again easily. Think of the favorite videos page on youtube if you need a good example. I want to make one database for this dynamic information, and make tables in that database that are named with the user's id number. I want to then make a new row in a user's table when they favorite something, and that row will of course store all the info I need about that specific favorite. My main question here is, is it a good idea to make a new table for every user? This is the only way I can think of to accomplish this, just not sure if that's horribly inefficient or if there's a much better way to do this. I don't really have any code written yet to show you or even a database set up yet, as I don't want to go through with this and then find out it was a terrible idea to do it this way. Apologies if I didn't explain well, thanks in advance for any answers. Quote Link to comment https://forums.phpfreaks.com/topic/184047-different-table-for-every-user/ Share on other sites More sharing options...
trq Posted December 5, 2009 Share Posted December 5, 2009 A different database for each user is not required and in fact probably a poor design choice. All you need to do is store a users id along with each record created in the one table. Quote Link to comment https://forums.phpfreaks.com/topic/184047-different-table-for-every-user/#findComment-971689 Share on other sites More sharing options...
roopurt18 Posted December 5, 2009 Share Posted December 5, 2009 You may want to do some reading on database normalization. Most articles / tutorials on the subject will contain examples that might point in the right direction as far as these types of design choices are concerned. Quote Link to comment https://forums.phpfreaks.com/topic/184047-different-table-for-every-user/#findComment-971692 Share on other sites More sharing options...
gapern Posted December 5, 2009 Author Share Posted December 5, 2009 A different database for each user I never said I was gonna make a separate database for every user. I said I was gonna make separate tables for every user. You may want to do some reading on database normalization. Most articles / tutorials on the subject will contain examples that might point in the right direction as far as these types of design choices are concerned. I'll take a look. Thanks. Quote Link to comment https://forums.phpfreaks.com/topic/184047-different-table-for-every-user/#findComment-971695 Share on other sites More sharing options...
trq Posted December 5, 2009 Share Posted December 5, 2009 A different database for each user I never said I was gonna make a separate database for every user. I said I was gonna make separate tables for every user. You may want to do some reading on database normalization. Most articles / tutorials on the subject will contain examples that might point in the right direction as far as these types of design choices are concerned. I'll take a look. Thanks. Sorry, I meant tables. Different tables for each user is in no way required. Quote Link to comment https://forums.phpfreaks.com/topic/184047-different-table-for-every-user/#findComment-971696 Share on other sites More sharing options...
gapern Posted December 5, 2009 Author Share Posted December 5, 2009 The only other way I could do it (that I see anyway) is to just put everything into one table, but that would be redundant and would slow down the search when trying to retrieve information. I skimmed through a couple normalization articles and it looks like they focus on reducing data inconsistencies and duplication. I would most certainly be duplicating data if I were to store every users favorite in one table, rather than tables dedicated to only one user. I could be misunderstanding something of course, I'm still very new to mysql. I'd love to hear your solution, thorpe. It sounds like you have a different idea that would work. I'll of course go over many more normalization articles to get a better idea of what they're saying, but any feedback here is still welcome. Thanks. Quote Link to comment https://forums.phpfreaks.com/topic/184047-different-table-for-every-user/#findComment-971702 Share on other sites More sharing options...
roopurt18 Posted December 5, 2009 Share Posted December 5, 2009 If users can potentially be adding the same resources to their favorites, then what you have is called a many-to-many relationship. In many-to-many relationships, you typically have two tables. In your case, users and favorites. users id, name, password, ... favorites id, url, img, whatever_is_appropriate_to_describe_a_favorite Then you have a third table that joins the two together: users_to_favorites user_id, favorite_id Quote Link to comment https://forums.phpfreaks.com/topic/184047-different-table-for-every-user/#findComment-971705 Share on other sites More sharing options...
gapern Posted December 5, 2009 Author Share Posted December 5, 2009 Ok I think I get what you're saying. If I do it that way though, I'll have to have the same user id in the table over and over again. Doesn't mysql have to loop through an entire table to make sure it gets all the results it's looking for? I don't want to be looping through different user's favorites when I'm only searching for a specific user's data. Say I have 1000 users and they all have 50 things favorited. That's one massive table that mysql has to go through every time I'm searching for a specific user's data. Wouldn't that cause a lot of load on the server? I could loop through 50k favorites from all users and pluck the ones I need out, or I could make a table for each user and loop through only 50 things. Am I making sense? There's gotta be something I'm missing here. I just don't see how your suggestion is more efficient than mine. Quote Link to comment https://forums.phpfreaks.com/topic/184047-different-table-for-every-user/#findComment-971727 Share on other sites More sharing options...
trq Posted December 5, 2009 Share Posted December 5, 2009 Databases are designed to search for data, its what they are optimized to do. Straight up, the one table method may seem less efficient, but what happens if at some point in time you want to start comparing data between users? Quote Link to comment https://forums.phpfreaks.com/topic/184047-different-table-for-every-user/#findComment-971728 Share on other sites More sharing options...
roopurt18 Posted December 5, 2009 Share Posted December 5, 2009 By creating a proper index on the table, it'll find the records for a single user just as fast as if they were in a table all their own. You should really start understanding databases before you start designing database applications or you're going to create quite a mess for someone else to maintain. Quote Link to comment https://forums.phpfreaks.com/topic/184047-different-table-for-every-user/#findComment-971738 Share on other sites More sharing options...
gapern Posted December 5, 2009 Author Share Posted December 5, 2009 I was actually looking at indexes a little earlier. I don't understand those just yet, I'll have to read up on those. Thanks for the input guys. Quote Link to comment https://forums.phpfreaks.com/topic/184047-different-table-for-every-user/#findComment-971754 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.