The concept database model refers to the set of tables and columns of a relational database. This page will cover how ERDiagram converts the input entity-relationship model into a database model.
Each entity defined in the input entity-relationship model will be modeled as a table in the database model. In the same way, every entity property will be modeled as a column of that table.
ERDiagram defines all database columns as NOT NULL by default. However, properties defined using the optional
modifier will be modeled as nullable columns, so NOT NULL statement will not be used on its definition.
Properties defined using the unique modifier will be modeled by adding a UNIQUE constraint to that column.
The identifier property of the entity will be modeled as a NOT_NULL column. Moreover, it will be also defined as the
PRIMARY KEY (a.k.a. IDENTIFIER) of the table.
ERDiagram supports different types of relationships regarding the cardinality of its members. We recommend reading Cardinalities before continue for a better understanding.
Relationships whose cardinality is one-to-one are modeled just in the same way that many-to-one relationships. This means that a foreign column is added to the left table. See the following sections for more detail.
Relationships whose cardinality is one-to-many are modeled by adding a foreign column to the right table (the many side of the relationship). Let's see an example:
User <->* Address
The relationship above represents a User that may have many Addresses. On the other side, each Address belongs to
one (and only one) User. This is modeled by adding a usedId column and its corresponding FOREIGN KEY constraint
referencing the Address table.
You can learn how to customize the name of the foreign column in the Aliases section.
Relationships whose cardinality is many-to-one are just like mirrored one-to-many relationships. To be precise, the following relationships are equivalent:
# many-to-one
Address *<-> User
# one-to-many
User <->* Address
Relationships whose cardinality is many-to-many are modeled by creating an intermediate_table which 2 foreign columns, one for each entity. Let's see an example:
User *<->* Role
The relationship above represents a User that may have many Roles. At the same time, each Role is related to
many Users. This is modeled by creating a new table UserRole with the columns userId and roleId, including their
corresponding
FOREIGN KEY constraints referencing the User and Role tables respectively.
You can learn how to customize the name of the intermediate table and the foreign columns in the Relationship's name and Aliases sections.
Defining aliases for the members of a relationship is useful not only for semantic purposes but also for customizing the foreign columns names.
For example, imagine you want to model a Travel entity that has 2 relationships to the same City entity, one for
the origin city and the other for the destination city. If you define those relationships without specifying an
alias for the City member, you will end up with two identical cityId columns in your Travel table.
The way to handle this situation is by adding an alias to the City member of both relationships:
Travel *-> City originCity
Travel *-> City destinationCity
By doing this, ERDiagram will name the columns originCityId and destinationCityId.
You can also use aliases in self-referencing tables:
Employee subordinates *<-> Employee boss
This will be modeled by adding a bossId column to the Employee table. If you don't use aliases, the column would
be named employeeId, which is much less semantic.
When defining the name of a many-to-many relationship, ERDiagram will use it for naming the corresponding intermediate table. Let's see an example:
User *<->* Role
The relationship above will be modeled by creating the UserRole table. If we want to customize this name, we can
define a name for the relationship in this way:
User *<->* Role (UserRoleMapping)
By doing this, we are telling ERDiagram to use the name UserRoleMapping for the intermediate table.
Note: defining the name of any other kind of relationship (one-to-one, one-to-many, or many-to-one) does not affect the database model.
It's possible to configure ERDiagram to use plural table names, so the entity User will be modeled by creating a
Users table. However, if you have specified the relationship's name explicitly, it will be used without applying any
transformation, so you have to specify it in plural if you what that behavior.
The reason for this is that there are some scenarios where the pluralization of the relationship's name can lead to
unexpected behaviors. For example, the default name of a table representing the relationship between Users and Roles
tables should be UsersRoles. However. if you specify the relationship's name as UserAndRole, it will be pluralized
to UserAndRoles instead of UsersAndRoles. To prevent this, ERDiagram will use explicit relationship names without
applying any transformation, so you can manually pluralize them in the right way.
The direction of the relationships is intended for OOP classes code generation, and it's not used in database model generation.
The main reason for ignoring the direction of the relationship in the database model is because it's used to define how
the data can be accessed (i.e. it's possible to get the roles of a user, but it's not possible to get the users that
have a specific role), which is out of the scope of the database model, as database data can be queried in any
direction using JOIN statements.