Back to articles list
A database diagram is essentially an abstract graphical representation of the structure of a database. It can be used both as a tool to assist in the design of a database and as a document that shows the structure of an existing database.
You can think of a database diagram as a poster that you stick on a wall for programmers to look at when they need to know the name of a table or column. That is certainly one of its functions. But the usefulness of a database diagram goes far beyond being a wall decoration or mere reference material. The following are some of the practical uses of a database diagram:
- Documenting database structure.
- Communicating database design decisions and providing a basis on which to discuss those decisions.
- Obtaining customer/stakeholder validation for a data model.
- Facilitating database version management and the generation of upgrade scripts.
- Smoothing out migration between model versions and even different database engines.
Database diagrams – also known as ERDs or Entity-Relationship Diagrams – can be created with any database modeling tool that allows drawing; even a pencil and paper will do. However, there’s a great advantage in using an intelligent database diagram tool like Vertabelo. These modeling tools not only facilitate drawing the ERD, they can also detect if there are flaws in the model that will eventually lead to problems when that ERD is transformed into an operational database.
You can follow these 5 Steps for an Effective Database Model or read about the steps of database design if you need a safe path to an effective database design.
Types of Database Diagrams
There are three types of database diagrams: conceptual, logical, and physical. All three represent the objects that make up a database but include different levels of detail and serve different purposes. A useful tip for better database design is to really understand the difference between ER diagram types.
The conceptual diagram is the most basic; its purpose is to give an overview of the data model to project stakeholders, who are not interested in implementation details. This diagram only needs to show the entities and the main relationships between them. It does not even need to fulfill the requirements of the entity-relationship model.
The logical diagram goes into detail about the definition of the entities and the relations between them; it must meet technical requirements to be valid. However, the logical diagram doesn’t show the implementation details for a particular database management system (DBMS).
Finally, the physical diagram does contain all the details necessary to become an operational database in a given DBMS. It includes the precise data types of each attribute. You can read about how to prepare a database model if you don’t know exactly where to start when creating an ER diagram.
The Logical Database Diagram
The logical database diagram serves as a working tool during the analysis and design stages of a software product. When working on the logical diagram, the database modeler does not need to know the specifics of the DBMS on which the software will operate. Moreover, by working exclusively on logical diagrams, the decision of which DBMS to use can be deferred. This decision can be made only when the software solution is being implemented; this allows the team to consider the performance, cost, and user convenience offered by each alternative.
A logical database diagram for a hotel booking system.
The Physical Database Diagram
The physical database diagram is constructed from the logical diagram. It adds all the information that will be needed to implement the database on a particular DBMS. This includes the data type of each column (chosen from the data types supported by the target DBMS) as well as its capacity and any constraints. In addition, the object names in the physical diagram must match the constraints of the DBMS and any applicable naming conventions in database modeling.
One of the most important advantages of an intelligent database modeling tool is being able to automatically derive a physical ERD from a logical diagram.
In Vertabelo, you can do this by simply choosing an existing logical diagram and telling the database diagram tool which DBMS to target. Vertabelo will automatically create the physical diagram, ensuring that the data types of each column and other ER diagram elements are 100% compatible with the chosen database. Vertabelo can also automatically generate the DDL SQL script that you can run on the DBMS to create your actual database.
Elements of a Database Diagram
Database diagrams graphically represent the objects that will later make up the structure of a database: tables, columns, relationships, keys, and so on.
As you move from the conceptual to the logical and then the physical diagram, the diversity of elements contained in the diagram increases.
In the conceptual diagram, the entities appear in rectangles with their names and simple lines that connect them, showing relationships. And usually there’s not much more detail than that.
In the logical ERD, the entities have attributes that define their structure. Each attribute has a generic data type (integer, decimal, varchar, date, etc.) and is database agnostic. One or more table attributes must be a unique identifier or a primary key, and these must be mandatory. Sounds confusing? Dig deeper into the difference between entities and attributes in a data model to get a clearer picture.
Relationships and Cardinality
The relationship between two entities is marked by cardinality – the minimum and maximum limits with which an instance of an entity can be associated with instances of the related entity. The maximum limit is also called multiplicity. This limit does not state the exact number of instances – it just states whether it is one or many.
The minimum limit indicates the mandatoriness of the relation. If this limit is zero, it means that the relationship is optional, i.e. instances of the entity don’t have to be associated with each instance of the related entity. If the minimum limit is one, then the relationship is mandatory. This means that there must necessarily be at least one instance of the entity that is associated with each instance of the related entity.
For example, in the diagram above the line between Rooms and RoomTypes indicates an optional many-to-one relationship. The line denotes the relationship that exists between the two tables. The vertical line by RoomTypes indicates one; the three lines and a ring by Room indicates many. Put together, this means that multiple rows in the Rooms table can be related to a row in the RoomTypes table.
The cardinality of relationships is another element that a database diagram should represent. And it is what gives rise to the different notations that exist for drawing database diagrams. Vertabelo supports the most popular notations, making it super easy to add references to ER diagrams.
Database Diagram Notations
In database diagrams, boxes commonly represent entities/tables and lines represent the relationships between them. Attributes (consisting of a name and data type) are written as a list inside the entity box.
ER diagram design becomes a little more complex when we have to represent the cardinality of the relationships between entities. And it is this complexity that led to the emergence of different notations to represent the structure of a database. Let’s look at some of the most popular notations:
Arrow Notation
Arrow notation uses rectangles to represent entities and arrows to represent relationships between entities. The type of relationship between entities, as well as its cardinality, is represented by the terminations of the arrows. These can be zero or one, one and only one, zero or many, and one or many.
The arrow notation does not establish criteria for defining other elements of an ER diagram – such as attribute data types, primary/foreign keys, etc. – so its usefulness is mainly limited to conceptual diagrams.
Barker Notation
The Barker ERD notation allows us to represent entities, their relationships, and other elements, such as:
- The defining characteristics of attributes, i.e. whether they are mandatory or optional and whether they are part of the entity’s unique identifier.
- The degree of relationships, e.g. one-to-one, one-to-many, or many-to-many.
- The optionality of relationships. Relationships are drawn with filled or dotted lines depending on whether each instance of an entity must (or may) be related to an instance of the other.
Crow's Foot Notation
Crow's Foot notation also represents entities as rectangles with a name and a list of attributes, and relationships as lines connecting entities. What distinguishes this notation is the way it denotes the cardinality of relationships.
In Crow’s Foot, each end of a relation has two indicators denoting cardinality. The first represents multiplicity. When this indicator is a short line placed across the line of the relation, it indicates that the multiplicity is one. And when it is in the form of a trident (or crow's foot; hence the name of the notation), it indicates that the multiplicity is many.
The second indicator denotes the mandatoriness of the relation. A circle indicates that the relation is not mandatory (the minimum limit is zero) while a short perpendicular line indicates that it is mandatory (the minimum limit is one).
Crow’s foot notation uses two indicators on each end of a relation to denote cardinality. The inner one indicates the lower limit or mandatoriness (zero or one), and the outer one indicates multiplicity (one or many).
UML Notation
Among the many different types of diagrams that comprise the UML (Unified Modeling Language) standard is one created specifically for entity-relationship models. As in all other notations, UML uses rectangles to represent entities and lines for relationships. Cardinality in relationships is explicitly indicated next to the connection between a line and an entity.
UML adds some logical types of relationships that are useful for particular data models, e.g. inheritance, generalization, aggregation, and composition. Vertabelo supports these types of relationships in its logical diagrams. Then, when deriving the logical diagram into a physical one, these relationships get translated into simple relations between tables.
A database diagram showing an inheritance relation.
Practical Uses of a Database Diagram
As I said before, a database diagram is more than just a poster to hang on the wall. Its usefulness goes beyond being a reference material so everyone knows the names of database objects. However, to get the most out of that usefulness, you need an intelligent tool capable of “understanding” the diagram and using it for different purposes.
Vertabelo offers the following functionalities to get the most out of database diagrams:
Collaboration
There are multiple ways that Vertabelo facilitates team collaboration. One way is for multiple team members to have a diagram open at the same time. One of them can edit it while the others can view it.
Sharing a diagram with a teammate in Vertabelo is as simple as telling the application a person’s email address, assigning them a level of access, and clicking a button. If you need to share it with clients or partners that don’t have a Vertabelo account, you can create a view-only public link to the diagram and send it by email.
Reverse Engineering
In database modeling, reverse engineering is the process of generating a diagram from an existing database.
With Vertabelo, you can reverse engineer virtually any database to generate a diagram of its structure. You can even reverse engineer databases with restricted access. To do this, Vertabelo provides a command line Java application that you run locally on your computer to generate the diagram; you do not need to expose your critical data to any risk.
Model Validation
A good database design must meet a number of requirements:
- Each table must have a primary key.
- The attributes of the primary key must be mandatory.
- Each attribute must have a valid data type, and so on.
If you design your database diagram with just any tool, it is very easy for the actual database to violate several requirements – especially if the diagram has a large number of entities.
On the other hand, if you use Vertabelo to create your database diagrams, the tool itself will keep an eye on model requirements as you work. It will give you hints on how to correct or improve your model.
Notes
It is essential to write notes that explain your design decisions or add useful information to your diagram. That’s one advantage of designing on paper: you can always stick post-its with any text you consider relevant.
Vertabelo also gives you that possibility, allowing you to add virtual sticky notes to the diagram where you can add supplementary information. Read more on how to make a better use of text notes in data modeling.
Subject Areas
Another useful Vertabelo feature is the ability to group objects into subject areas. You can use subject areas to group objects by any criteria you want, such as by functional areas.
Subject areas help you quickly identify groups of associated tables.
Model Versioning
When you first start working on the creation of database diagrams, you may not see the importance of maintaining versions of your diagrams. However, when you have already created hundreds of versions and each one has been modified hundreds of times, you will greatly appreciate being able to review old versions of a diagram and even compare it automatically with the latest version.
Vertabelo offers auto-save, versioning, and change control. These features allow you to put your full attention on the work you are doing without worrying about making an incorrect change that you can’t rollback. Another important Vertabelo feature involves organizing your ERD diagrams.
The auto-save function automatically generates a new version of your diagram every time you make a change. If you have made changes and are unsure if they are correct, you can always compare your current diagram with the different versions that have been generated throughout the day to decide if you need to undo any changes.
Vertabelo also allows you to assign tags to diagram versions, thus establishing clear milestones. For example, you can create a tag before refactoring a data model; if you need to see what the diagram looked like before the refactoring, you simply search for the tag and you can open the corresponding diagram.
Database Diagrams: Practical Tools, Not Just Drawings
When we think of database diagrams, we commonly imagine big posters with schematic drawings, to which we occasionally turn to clear up any doubts about a data model.
However, throughout this article we have seen that a database diagram is more than a static representation of a data model. It is an intelligent tool that we can use to increase the efficiency of our work as data modelers.
