What is a good practice when setting up a users table? Looking at some newbie tutorials, but not sure how to "really" do it right

Question

I'm tinkering around with building a rest API that connects to a database. I'm following tutorials, but the table set ups are all really basic and one of my issues has been that in the "real world" the way its done is a lot more complex and different :(

However, I'm wondering for my actual application (really small) how can I properly set up the User table?

For example, I have set the primary key to userid because that should never be changing. Is it fine to use long for the userid?

Also, is it fine to lump a bunch of things together that are related to the user.. in the User table? I know its a stupid question.. For example, I want to know if the user has signed up for the service, so isMember. Or, is the user signed up for fast service, so hasFastService. Or, should these things be put into a UserAttributes table by the userid?

Finally, I looked up UUIDs and I'm wondering where those fit in, in which scenarios, etc.

Thanks

Answer 1

I'll try to answer this based on my own experience of creating users table in a project recently. The things you have to take care are basically these:

1. Authentication: Determine your login process and things like credential fields, user types (admin/guest/normal), whether OAuth is required or not, etc. before creating the users table. For example, whether you need a username/password to authenticate or email/password or either "username or email" with a password. The modern practice is to do away with a "username" since its redundant - an email is unique and acts as a username for all intents.
2. OAuth: If you are giving facebook/google/twitter logins, make provisions for that in your users table. How will you determine whether the user was a normal signup or a social login signup? A field such as "login_method" or something is useful in this regard. A second field called "user_type" maybe created for identifying the type of user account: admin/guest/employee/etc.
3. Profile fields: Its upto you to determine profile variables. In my last project, I used a few fields like FirstName, LastName, Theme, Timezone, etc. for the profile, but your mileage may vary.

For user-ids, it is usually best to have an auto generated integer primary key which is available in all modern databases.

Needless to say, never store the actual password in the password field. Just has your password and store the has instead. When the user logs in, you can hash the user-input and compare with the user table value.

Finally, DON'T confuse the user table by including related data like clients, employees, etc. All clients are users, but all users are not clients! Keep your design flexible, so as to include all kinds of users like admins, employees, etc.