database design: what fields are must for a user table in database?

Question

I am trying to design a user table for MySQL.

for now, my user table looks like this

users (
BIGINT id,
VARCHAR(?) username,
VARCHAR(?) password,
VARCHAR(254) email,
DATETIME last_login,
DATETIME data_created
)

what other fields should I also include and why do I need them?

what fields should I exclude from above and why?

how many characters should I allocate for username and password, and why?

should I use BIGINT for id?

Thank you in advance for your helps.

ADDED I am going to use the table for social web site, so 'users' mean people around the world.

Solution 1

A few comments:

1. BIGINT is fine. I assume you're using it as a surrogate key. In that case, declare it as

   BIGINT id primary key auto_increment,

   Mysql will automatically allocate a unique int value to your id whenever you do an insert (don't specify any value for this field). Never try to implement this behaviour by selecting the max id and adding 1 to it (I've seen this so many times).

2. Length of username and password: this is no big deal really, just pick a length. I tend to standardise on 255 length varchars for these things but that's not based on anything empirical.

3. Call your table "user", not "users". Conceptually, a table implements an entity in the same way that a class implements an entity. You will likely create a class or data structure called "user" and the table name should correspond to this.

4. Every table that I create has two timestamps, "created" and "last_updated". You're halfway there:)

5. I don't think I would store last_login in the user table, this is likely to be something that you will want to log in a separate table. It's a good idea to store all login events (login, logout, failed attempt, account lock etc.) in a logging table. This will give you much better visibility of what the system has been doing.

Solution 2

1/ Username and password: decide for yourself how large you want these to be.

2/ BIGINT is fine, even though an integer probably suffices. But make it UNSIGNED and probably AUTO_INCREMENT, too.

3/Try keeping your Users table as small as possible:

users (
BIGINT id,
VARCHAR(?) username,
VARCHAR(?) password,
VARCHAR(254) email,
DATETIME data_created
)

The rest, you put in extra tables:

logins (
BIGINT loginid
BIGINT userid
DATETIME last_login,
VARCHAR(15) IP_ADRESS
...
)

This way, your users table will only change when a new user is added or deleted, or when someone changes his password, which is less frequently then when someone logs in. This allows for better table caching (MySQL clears the table cache when you write to the table).

Comments

[halfdan]

Why do you use BIGINT? I agree with your suggestions, but this applies only for systems with a huge amount of users. For a few thousand users the normal table schema is fine.

[Joon]

Thank you for your suggestions and educating me about caching policy in MySQL.

[MaXi32]

what about the person who created the user?

[DerpyNerd]

I find it bad practice to set varchar length to 255 for names. It all comes down to conventions of course but 255 tends to be misused or even abused by users and programmers alike. You're better off googling for standardized lengths. i.e. For names, the appropriate maximum length should be 35 or 70 for combined fields.