Different table for every user?

[gapern]

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.

[trq]

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.

[roopurt18]

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.

[gapern]

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.

[trq]

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.

[gapern]

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.

[roopurt18]

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

[gapern]

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.

[trq]

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?

[roopurt18]

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.

[gapern]

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.