A simple database schema for your next user system

Written by

Walter G.

Published on

August 20 2020

Modified on

November 13 2023

Filed under

database

The following is a relatively standard schema for a User table in a database. You can use this as a guide for when creating your own cutom authentication system. I will also include several other related tables that are typically needed as well in your standard user management system and will point out the relationships accordingly.

If you are relatively new to working with databases or creating schemas, then I suggest you check out my database fundamentals article.

The User table

It's always best to include more columns initailly in your database tables even if you don't plan on using them just yet. It is normally better to have too much data, then to have too little data. Mainly because the sooner you begin to collect data, the more of it you will end up having.

The following is a good baseline that you can use for just the User table.

To follow proper normalization guidelines, the User table is typically kept on the simpler side and other tables are added in which data can be related to a particular user using a foreign key (fk).

Note: A foreign key is any column that houses the primary key of another table and which is used to create a relationship between two tables.

The password column

Notice that the password column is set to have a binary data type in the schema declaration above. That's typically done mainly because you do NOT want to store plain-text passwords in your database.

If for example a user signs up on your site with the following credentials:

Email: support@thatsoftwaredude.com
Password: hithere

You definitely want to store the email in order to communicate with the user and send them emails and such, but you don't want to store that exact password.

This is typically where encryption comes into play. You can check out my article on encryption in order to get a better understanding of the concept.

But essentially, you want to encrypt whatever the user types in as their password, using a secure protocol, and then store that encrypted value. This encrypted value is what you will be checking for when a user tries to login. Picture the following in order to break it down a little further.

Imagine that the encrypted result of the password: helloworld turns out to be:

k2kSK$%k

You would then store that encrypted value into the password field. When you encrypt a word using a particular algorithm, you will always get back the same result. And this is the trick to the process.

The next time that user attempts to login, you will first encrypt the users input and then compare it to whatever you have stored in the database. If the values match, it is safe to assume that this is indeed the correct user.

The UserInfo table

Depending on the website, you normally have extra information relating to a particular user that you will need to capture. Having a separate table, while not required, can help in data organization and it's typically a good normalization tactic.

Notice the UserID foreign key (fk) linking to the appropriate User record. Again, this field can not be left as null, as it would be considered an orphaned record not belonging to anyone.

UserInfo can include any columns that you feel belong to a user, whether it be birthday or display name or even whether a user has accepted the cookie policy or not.

The UserAddress table

Typically a user only has a single address, or they might have zero as well. Having that information in its own table again helps to create a more normalized schema with less redundancy. In this case if a user has no addresses, then there would simply be no UserAddress record found for that user. And if a user has 20 different addresses, then they would have 20 different records in the table.

Standard typical Address fields look something like the following, if you live in the US.

Again, notice the foreign key UserID at the bottom of the table which we will use to link to the appropriate user record.

Depending on how you are keeping the States in your project, you have a few options here. The easiest method would be to simply just store the state as a string, such as "Caliornia", "New York", etc.

But you can also keep states separated in their own lookup table and siimply store the state ID in the UserAddress table. This approach is more scalable and further makes your overall schema even more normalized, at the cost of having to keep track of yet another table.

Also notice how I mentioned earlier that this is a good schema for a US based address system. This is mainly because different countries have their own methods of address management. Some countries for example don't have states and might instead have provinces. Others might have different formats for zipcodes and some might even include more fields that are not present above.

Design your address schema accordingly based on where you live.

The UserPhone table

The same pattern that was used above for UserAddress can be used for storing phone numbers as well.

A quick note about normalization in this case. The Type column, because it is a list of given values, can also be put into its own table. Potential values for this table could look like the following:

- Business
- Home
- Mobile

This is typically where normalization can be taken too far and the benefits might begin to diminish the further you break down the structure.

You can read more about normalization and the pros and cons over at this article.

If you are relatively new at working with databases and need a user system, this would be the most fundamental structure to have regardless of which type of database you are using.

Comments

No messages posted yet

Add comment