A simple database schema for your next user system

Walter G.

6 minute read

3,992 devs read this

database

The User table
The password column
The UserInfo table
The UserAddress table
The UserPhone table

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

erDiagram
    User {
        int ID
        string FirstName
        string LastName
        string Email
        binary Password
        datetime DateCreated
        bit IsActive
    }
    
    UserInfo {
        int UserInfoID
        int UserID
        varchar Bio
        varchar ProfilePicture
        datetime DateModified
        datetime DateCreated
    }
    
    UserAddress {
        int UserAddressId
        varchar Address1
        varchar Address2
        varchar City
        varchar State
        varchar Zipcode
        varchar Country
        int UserID
    }
    
    UserPhone {
        int UserPhoneID
        varchar Type
        varchar Number
        datetime DateCreated
        bit IsActive
        int UserID
    }
    
    User ||--o| UserInfo : has
    User ||--o{ UserAddress : has
    User ||--o{ UserPhone : has

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.

classDiagram
    class User {
        +int ID
        +string FirstName
        +string LastName
        +string Email
        +binary Password
        +datetime DateCreated
        +bit IsActive
    }

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/hashing 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 in your database. When you hash 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.

Whether you add a column such as 'Bio' to the actual User table or the UserInfo table is entirely up to you however, as there is no standard for such things.

classDiagram
    class UserInfo {
        +int UserInfoID
        +int UserID
        +varchar Bio
        +varchar ProfilePicture
        +datetime DateModified
        +datetime DateCreated
    }

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.

classDiagram
    class UserAddress {
        +int UserAddressId
        +varchar Address1
        +varchar Address2
        +varchar City
        +varchar State
        +varchar Zipcode
        +varchar Country
        +int UserID
    }

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.

classDiagram
    class UserPhone {
        +int UserPhoneID
        +varchar Type
        +varchar Number
        +datetime DateCreated
        +bit IsActive
        +int UserID
    }

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.

Walter Guevara is a Computer Scientist, software engineer, startup founder and previous mentor for a coding bootcamp. He has been creating software for the past 20 years.

Last updated on: August 29 2024