11

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.

Joon
  • 9,346
  • 8
  • 48
  • 75
  • The fields you need depend heavily on the purpose of the table which you didn't provide. Please update your question, thanks. – janhink Apr 11 '11 at 12:48

3 Answers3

23

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.

PhilDin
  • 2,802
  • 4
  • 23
  • 38
  • 1
    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. – DerpyNerd Sep 24 '16 at 19:04
  • A max length (less then 255) should be only considered for usernames, not `first_name`, `last_name` or `full_name`, just wanted to add this, am not implying that you mean differently. good read on names https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ – Can Rau Feb 21 '23 at 16:13
6

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

Konerak
  • 39,272
  • 12
  • 98
  • 118
  • 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. – halfdan Apr 11 '11 at 12:52
  • 1
    Thank you for your suggestions and educating me about caching policy in MySQL. – Joon Apr 11 '11 at 13:50
  • what about the person who created the user? – MaXi32 Sep 11 '15 at 15:39
0

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.

halfdan
  • 33,545
  • 8
  • 78
  • 87