Back to articles list
Here are 15 simple tips that will guarantee the quality of your database model!
Prior to the emergence of NoSQL databases, data modeling had followed a standard norm. However, with the hybridization of storage models and especially with the integration among them, it has become necessary to consider NoSQL databases in systems that use relational databases. This article covers 15 tips for modeling databases in 2021 (the database modeling tool used in this article is Vertabelo).
1. Know Your Business Rules Well
To start, you have to pay close attention to the planning of your database. Having good knowledge of your business rules is essential for database modeling. There is no way you can model something you don't know; this is a common topic in other articles. You have to understand the primary needs of and the requirements for your system before starting your project.
However, this is often neglected, which could make your system a weak link in the enterprise ecosystem forever. Proper data modeling needs to come before the database development stage. Ignoring the planning phase will lead to a cumbersome need later to restructure, replace, or even scrap the database all together.
To emphasize: to model a database even minimally, know the business rules, or work with someone who does.
2. Consider Hybrid Models (NoSQL/Relational)
NoSQL databases have emerged to cover a market that relational databases cannot support – in particular, the Big Data needs. You should not discount the possibility that the systems may need to be hybrid (relational and NoSQL). Consider this when modeling your relational database.
Modeling non-relational databases is entirely different from modeling relational databases, but they can be integrated, usually through APIs. Your relational model should account for the possibility of having to communicate with a NoSQL database. For this, you can leverage JSON support, how semi-structured data is organized, and internal database procedures.
3. Model Documentation Is Not Optional
As mentioned in this article, good database documentation is crucial when generating a data model for your enterprise database. It is not enough to name the objects. If you do not document properly, their meaning may become unclear and incomprehensible later.
Documentation needs to describe the expectations of the database structure. This will help when new developers come onboard to existing projects or need to follow up on the work done by their predecessors.
Standardize entity names, attributes, and keys (many databases automatically name keys), document what each entity represents (primarily in the logical model), and maintain a description of how the business rules and the database structure you're modeling correspond with each other.
4. Share Your Database Models
Working with a team that knows the business rules and sharing the data model are great ways to reduce your chance of errors. The saying "two heads are better than one" exists for a reason. A shared model allows your co-workers to see changes in real time, make corrections, and track errors before sending the model for testing.
A shared model will speed up development and documentation, serve as a test during development through the division of tasks, and reduce errors. A tool like Vertabelo allows sharing and collaborating on the model in real time, saving time for the team.
5. Primary Keys Are Mandatory
A primary key is the identifier for each row. It is helpful to decide first which columns need to be part of the primary key and what values they need to include. Each table needs an identifier that respects the required uniqueness and makes it easier to specify and map relations between rows and tables. If you are not sure how to do this the right way, you may need assistance from a database design expert.
You should have primary keys for every table including support tables (N:N). Having a primary key for each table will make it much easier when there is a need to correlate its records with others or to perform indexing and searches on the data.
6. Wrong Cardinalities Can Slow Down Your Queries
Cardinality refers to the type of relationship between two tables in a database. The two tables can be related one to one, one to many, or many to many:
- 1:1. One row in table A relates to one row in table B. Using an entity-relationship (ER) model, 1:1 means that one occurrence of an entity relates to only one event in another entity.
- 1:N. One row in table A relates to many rows in table B. In ER modeling, 1:N means that one occurrence in an entity relates to many events in another entity.
- N:N. Many rows in table A relate to many rows in table B. In ER terms, many occurrences in one entity relate to many events in another entity. For example, a student (table A) may sign up for many classes (table B), and a class may have several students in it. Many-to-many relationships usually require a cross-reference table, say AB, with two one-to-many relationships A:AB and B:AB.
The relation may be optional; that is, it may be that a row in one table does not always relate to any row in the other table. For example, not all associates may have business credit cards, or a customer may have no calls associated with him/her.
Determining the cardinality of a database model correctly is essential in avoiding slow queries and queries that unnecessarily use too many tables. You should create a support table only for many-to-many (N:N) relationships.
7. Avoid Null Fields
Allowing nulls in tables may complicate the application programming required to access them. Null values in a model can indicate one of two problems: a rejection of the data inserted into the database or a normalization problem. In the first case, the model just leaves the fields null because it is "standard” to do so. In the latter case, you have data that does not have to be filled in (for example, additional phone fields), and that could quickly transform a table into one that has to be connected by an additional foreign key.
Furthermore, when a user realizes that a field can be null, he/she may stop filling in that information. This can cause consistency problems when the data needs to be analyzed.
Avoid allowing nulls unless it is absolutely necessary. For example, the table in the figure above shows that the identifier values and the start date are mandatory, not allowing them to be populated retrospectively later.
8. Versioning Will Save You (a lot of) Trouble
Versioning is the process of assigning unique version names or numbers to individual states of database models (or of software, more generally). Systems like GitHub and Bitbucket have taught us the importance of versioning. Database model versioning reduces errors in a project, allows a more efficient creation of branches, and increases the team’s productivity.
There are several reasons for controlling your database versions. They include easier collaboration across distributed teams, better visibility of the development pipeline, the ability to roll back or retrieve previous versions of the database, and the provision of audit trails for compliance. It helps you lay solid foundations for automating database deployments and synchronize database and application code changes. In addition, versioning helps coordinate between teams, increase efficiencies, and troubleshoot issues.
9. Create or Adopt a Naming Convention
Naming conventions offer insight into all that is involved in the table models and database fields. In the design phase, select a proper naming convention relevant to your business for your database objects. Then, stick to it throughout the implementation and maintenance phases.
Ensure it is logical and provides precise information for the users. With a good naming convention, all tables, constraints, columns, and other objects can be named and used consistently and predictably.
10. Consider DBaaS (Database as a Service) in Your Model
Owing to its numerous benefits, the use of cloud computing has become commonplace. For example, database-as-a-service (DBaaS) and other systems that work as services can help an organization with its processes.
DBaaS uses systems in the cloud and can offer flexible, scaled, and on-demand platforms to its users. The service provider responsible for physically allocating the data center delivers the database software. This type of service has become increasingly essential for businesses; since most companies have already migrated to the cloud, having an online database has become more important.
When modeling your database, keep in mind that you may need access to a cloud structure rather than a physical form on a server, which may make some optimizations more difficult or more manageable. For cloud systems, prioritize indexing and normalization to save processing resources.
11. Never Skip Normalizations
Data normalization is a fundamental performance tuning approach and a robust mechanism for database integrity (take a look at this article for more information). While there are problems related to overnormalization, developers often undernormalize, which may further cause data replication, leading to insufficient storage and compromised performance.
As you normalize more in a database, it becomes less consistent and loses speed, so you have to balance the needs. When in doubt, keep normalization to a minimum, with 3FN as a rule of thumb.
12. Your Entities Should Be Simple
Entities that are too large or too complex can make your model confusing with the large number of attributes and keys. While it is often difficult to avoid a "central table” with all the primary data for the business rules, it is important to name and design your entities as precisely as possible.
You can see an example of a good practice in the Users table above. The name itself indicates its role. The fields are directly related to the role, clearly indicating what your organization does with this and following a pre-established naming convention.
13. Always Check Your Foreign Keys
Foreign keys are part of a family of constraints that ensure database integrity. They provide relationship integrity between parent tables and the respective child tables. Experts point out that referential integrity constraints like foreign keys boost the overall performance of a database. An optimizer can recognize enforced constraints easily and make better choices for joins and other actions related to data access.
When checking your foreign keys, pay attention to whether the connections are correct or should be modified (this impacts your queries), whether the cardinality is adequate, whether the way you modeled it will maintain consistencies correctly, and whether there is a need for indexing (probably yes). A well-constructed set of foreign keys will make it a lot easier when you need to connect your tables via joins.
14. Keep Possible Integrations in Mind
Most organizations have several systems, often containing redundant information across systems (customer registrations, for example). Have the structures of other relational databases if possible, and consider using data from these systems to avoid replication.
In addition, some systems need to integrate with others at some point, even if they were initially designed to work alone. It is important that you think about integration issues when modeling your database.
Furthermore, there are cases in which systems need to communicate with each other because the business rules require it. Unless it is small, it is difficult for a system to operate alone forever.
Make sure that your model accounts for this eventuality. Build in compatibility with other existing systems, such as similar data types for the same fields, for example.
15. Choose a Single Modeling Tool That Meets Your Needs
Navigating between tools to use different resources from each can be an arduous task when modeling databases. To make your work easier, use a single tool to handle all (or the vast majority) of your needs.
I recommend Vertabelo as the modeling tool. It supports logical and physical ER diagrams, you can share models with your team for smooth collaboration, and it can generate SQL scripts.
This tip will increase your productivity and help you avoid errors from having to transition between different tools.
Keep These Database Modeling Tips in Mind
With these 15 database modeling tips, it will be much easier to maintain the consistency and the integrity of the database created from your model, as well as to prepare it for future integrations with other systems and non-relational databases. As data manipulation technologies advance, it is essential to keep relational databases integrated.
