Back to articles list
Almost all applications allow users to log in and create a customized profile. So when you build an application, you should spend some time thinking about where to store the data and how to design the user profile database. In this article, we’ll design an efficient database model for user profiles.
In this article, we’ll take you step-by-step through a database model for storing user profile data. We’ll start with the thought process and lead you from formalizing the idea to the final data model.
No matter if you are a database designer at the beginning of your professional journey or an experienced full-stack software developer who builds applications and databases through an ORM, it’s important to know how to build the right data model for your application.
Once the application is in production, it’s rarely a good idea to make large modifications to the database schema or to live tables. Most large-scale modifications in databases with live tables also imply thinking about migrating the data, which is an operation we usually want to avoid. This is why it’s important to always take your time when building a data model. This is especially true when you’re doing a user profile database model; users are central to the application, so their data model needs to be correct from the beginning.
Creating a Data Model for User Profiles with Vertabelo
Before we start designing our user profile database, we need to first define the scenario we want to model. We need to identify all the details of the application, especially with respect to how the user will create their profile.
For our scenario, let’s assume we have to build a social media application where the user can share their thoughts and post status updates about their day. The main storage requirements that involve user data are:
- Store the user’s personal identifiable information, e.g. first name, last name, primary and secondary email addresses, home or work address, date of birth, etc.
- Store the user’s activity within the application, e.g. logging in, pages visited, logging out.
- Store the status updates as text messages posted by the user.
The first step in building any new data model for an application is to follow data modeling best practices, such as starting from a general model (conceptual or logical model) and moving into the database-specific physical model.
Creating the Logical Data Model
Many data models start at the conceptual model, which is a high-level model often used to demonstrate key concepts. We’ll begin with the logical data model because it’s the starting point of describing technical information. The conceptual model just lays down the main entities and basic relationships.
Modeling the User
When starting the logical model for our user profile, we will begin by modeling the central piece of our data model: the user’s personal identifiable information.
We will start off by modeling the User entity, which contains all of the information each user supplies during signup, i.e. UserName, Password and PrimaryEmail, etc. This is all mandatory information. Because users typically only have a few active email addresses, we will store the email fields in this entity.
We also need to add information about the user’s physical location. To do this, we need to model an entity that stores user Addresses split into multiple fields (e.g. StreetNumber, Street, City, etc.).
In order to keep our data model clean, we will apply normalization, hence our Addresses entity will be a separate table and not a set of address fields in the User entity.
Because a user can have multiple addresses, we will represent this as a separate table and link the two entities with a 1 to many (1:N) relationship. In the screenshot above, we can see the configuration for the Addresses entity; it contains a list of mandatory fields for defining a valid address. These include the country, county, city, street name, street number, and an indicator of the type of address (e.g. work or home).
Because we can have multiple types of addresses, we need this field in the Addresses entity. If we anticipated having more than a couple of address types, it would be best to store the AddressType as an integer and link it with a different table storing AddressType information. But in this case, a single text field in the Addresses table is sufficient.
Modeling User Activity and User Preferences
The second requirement was to store information about user preferences and activity within the application. Here, ‘activity’ is when the user logged in, what pages they visited, what pages they closed, and when they logged out. Additionally, users can save pages to their Preferences so they can easily find those pages later.
To solve this requirement, we will add three new entities to our data model: Activity, ActivityType, and Webpage. Each of these entities will have relationships to the others and a corresponding cardinality.
The Activity entity stores the basic information about what a user does inside the application. Every time the user interacts with the application by opening, navigating to, closing, or navigating from a webpage, this activity is logged with a timestamp. It is linked to the User entity by a many-to-many (N:M) relationship because each user can have multiple activities and each activity can be performed by many different users.
The ActivityType entity is linked with the Activity entity and contains the name of that activity. ActivityType links back to the Activity table through a unique identifier called a primary key (ActivityTypeID, a text column containing values like “Log in”, “Log out”, “Open page”, “Close page” or “Mark page as preferred”.)
The Activity and ActivityType entities are linked through a many-to-one (N:1) relationship. It is similar to the one-to-many relationship except for the direction of cardinality. In our case, there will be many activities that have the same activity type and hence point to the same activity type ID.
Each user activity causes a new page to be loaded. The information about which pages the user visits is stored inside the Webpage entity. It contains a unique identifier for each page, plus the webpage URL and title (which is displayed in the title bar of the browser). Webpage is linked to the Activity entity by a many-to-one relationship; each webpage can be loaded multiple times by different activities.
Modeling Status Updates
We’ve mentioned earlier that this database will support a simple social media application where the user can post status updates (i.e. messages). So, next we will need to add the Messages entity and the appropriate relations.
The Messages entity contains only two fields: MessageID (the unique primary identifier for each message in the database) and MessageText (which stores the actual text posted by the user as a status update).
The Messages entity is linked to the User entity, since a status update is posted and associated with a user. Additionally, Messages is linked with Activity; each interaction that a user has with the application (including posting a message) causes a new activity record to be generated, with the appropriate link to the ActivityType table.
Creating the Physical Data Model
Once we are done with creating the logical database model for user profile data, we can now generate the physical model, which is used in creating the database itself. In the Vertabelo Modeler, creating the physical data model is a simple two-step process. We can generate the physical model right from the logical model.
Once we’ve selected the target database engine, Vertabelo Modeler will adjust the logical model to the syntax of the target database, replacing the data types from the logical model with the appropriate data types for the selected database. It will also convert primary identifiers in the table to primary keys and add the appropriate foreign key columns and relationships between the linked tables.
In the end, our final physical data model will look like the one shown below. We can also see a new table being created in the middle, between User and Activity. This is a junction table created when entities have a many-to-many relationship.
Now that we have our physical data model, we only need to check that the data types that were automatically selected are correct and change any other fine-detail settings on the database before creating it. To create the actual database, Vertabelo Modeler has a quick tool which allows you to generate all the necessary SQL code specific for your target database. You don’t even have to write the SQL code – you just need to do the modeling.
All we need to do is press the Generate SQL button on the top menu (shown in the screenshot below) and follow the steps for generating the file with the desired settings, behavior, and objects:
You can download the SQL file or you can save it in Vertabelo Modeler for later use.
Database Model Design Is a Superpower
Now that you’ve learned how to design a user profile database model, don’t stop there. Keep looking into how you can model a database for different scenarios. There are numerous database design articles on the Vertabelo blog that help you develop this skill into a superpower. Check them out!
