How to design database tables (MySQL) for multiple users ?

Question

i would like to know the best way to design database tables for multiple users, like any website that requires a user log in before they can start posting anything.

Should my code create individual tables for every users or something different?

Answer 1

Usually, you'll just have a user_id column in any table that needs to reference users. You'll have to be more specific of the sort of problems you're having if you want more detail than that, though.

Answer 2

Normally all users are stored in one table, with each user on a separate row.

Basic columns would include: user_id, user_name, user_password, dob, user_ip, avatar, etc...

I would recommend looking at an existing database structure, either of a forum or CMS, and register a few test accounts and see how they get placed within the database.

Answer 3

Essentially you'll need to look at the entities you're using and how they're interacting.

If we consider a basic example, where we have simple site where registered users can post messages on a single board. Here our entities are Users and Messages, where each user can post many messages (1:M relationship). Therefore we will need a table for Users and a table for Messages.

A table structure for this example would be:

Users(PK user_id, forename, surname, ... )
Messages(PK message_id, FK user_id (references Users), timestamp, message_text, ... )

Then if we wanted to extend this so users can post comments on messages, we can't store the comments in the message records as this will present a many to many relationship (many Users can comment on many Messages) and would require serialisation or something, so we use another table Comments that references the User and Message.

Comments(PK comment_id, FK user_id (references Users), FK message_id (references Messages), timestamp, comment_text)

(PK - primary key, FK - foreign key, ... - anything else you want to store)

Above I've used surrogate primary keys for simplicity, although in the interests of normalisation you could get rid of these and use composite keys instead (say, message timestamp, user_id and text).