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.

Answer 1

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).

Answer 2

All that just depends on your own specs. For username you could take 100 if you like, for password take the length of the hashing function you want to use (32 for MD5).

It's more common to use INTEGER(10) with AUTO_INCREMENT on the primary key of a table.

You might want to ask for a name, surname, birth date, place of living, etc. Think that all the data you ask the user for should be somehow important to the platform that you are building.