Structuring a MySQL database for user information

Question

I am quite new to MySQL, I know most of the basic functions and how to send queries etc. However, I am trying to learn about structuring it for optimal searches for user information and wanted to get some ideas.

Right now I just have one table (for functionality purposes and testing) called user_info which holds the users information and another table that stores photos linked to the user. Ideally id like most of this information to be as quickly as accessible as possible

In creating a database which is primarily used to store and retrieve user information (name, age, phone, messages, etc.) would it be a good idea to create a NEW TABLE for each new user that stores all the information so the one table user_info does not become bogged down by multiple queries, locking, etc. So for example user john smith would have his very own table in the database holding all his information including photos, messages etc.

OR

is it better to have just a few tables such as user_info, user_photos, user_messages,etc. and accessing data in this manner.

I am not concerned about redundancy in the tables such as the users email address being repeated multiple times.

Table per user? No no no. Consider what we all hope for, and your project becomes a huge hit and you have a million users. Just try and grasp what that would be like to try and maintain that. More importantly, try and figure out how you would get a list of, for instance, all of the users in your system who have more than five pictures. You really couldn't do that in any effective way if you had each user in their own table. — Marvo
– Marvo, Commented Jul 27, 2012 at 2:43
possible duplicate of Is it unreasonable to assign a MySQL database to each user on my site? — outis
– outis, Commented Nov 15, 2014 at 22:01

Carlos Vergara · Accepted Answer · 2012-07-27 02:42:20Z

1

The latter is the best way. You declare one table for users, and several columns with the data you want.

Now if you want users to have photos, you'd require a new table with photos and a Foreign Key attribute that links to the user table's Primary Key.

answered Jul 27, 2012 at 2:42

Carlos Vergara

3,6325 gold badges35 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jlofft Over a year ago

Ok thanks a lot guys, that's what I thinking. I was just toying around with the idea because it seemed like it would be really easy to at least organize the data. My main fear is having a massive table, like marvo said, of a million users (crosses fingers) with unorganized data. This may be a loaded question but how can MySQL handle lets say 50 queries at once. I have books but sometimes its just easier to get it in writing. Thanks, im going to leave this open for a bit.

Carlos Vergara Over a year ago

That's your least concern. Tables are made so you can have a massive table with a million users! Now if one user can have many of other same type data (like photos, a user can have many photos! or maybe comments), then you make another table for photos (and comments) and use foreign keys as I wrote in my answer.

Tim S · Accepted Answer · 2012-07-27 02:42:32Z

1

You should definitely NOT create a new table for each user. Create one table for user_info, one for photos if each user can have many photos. A messages table would probably contain two user_id columns (user_to, user_from) and a message column. Try to normalize the data as much as possible.

Users
====
id
email
etc

Photos
====
id
user_id
meta_data
etc

Messages
====
id
user_id_to
user_id_from
message
timestamp
etc

answered Jul 27, 2012 at 2:42

Tim S

5,1311 gold badge40 silver badges35 bronze badges

Comments

Fluffeh · Accepted Answer · 2012-07-27 03:43:53Z

I agree with both the answers supplied here, but one thing they haven't mentioned yet is lookup tables.

Going with the general examples here consider this: you have a users table, and a photos table. Now you want to introduce a featre on your site that allows users to "Favorite" photos from other users.

Rather than making a new table called "Favorites" and adding in all your data about the image (fiel location, metadata, score/whatever) all over again, have a table that effectively sits BETWEEN the other two.

+-----------------------+      +-------------------------------------+
| ++ users              |      | ++ photos                           |
| userID | email | name |      | photoID | ownerID | fileLo | etc... |
+--------+-------+------|      +---------+---------+--------+--------+
| 1      | ....  | Tom  |      | 35      | 1       | .....  | .......|
| 2      | ....  | Rob  |      | 36      | 2       | .....  | .......|
| 3      | ....  | Dan  |      | 37      | 1       | .....  | .......|
+--------+-------+------+      | 43      | 3       | .....  | .......|
                               | 48      | 2       | .....  | .......|
                               | 49      | 3       | .....  | .......|
                               | 53      | 2       | .....  | .......|
                               +---------+---------+--------+--------+

+------------------+
| ++ Favs          |
| userID | photoID |
+--------+---------+
| 1      | 37      |
| 1      | 48      |
| 2      | 37      |
+--------+---------+

With this approach, you link the data you have cleanly, efficiently and without too much data replication.

Collectives™ on Stack Overflow

Structuring a MySQL database for user information

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related