Diagram maker for developers

Designing your database using the entity-relationship model

The entity-relationship (ER) model is a way to design databases at the conceptual level. It relies on describing real-world entities and the relationships between them. If something can be individually identified, then you can use it as an entity in your database. Any association between entities is then recorded as a relationship in the database.

The ER model comes with a standardized diagrammatic technique that makes it quick and easy to design a database. We’ll use Chen notation to create our example diagram.

Each of the three basic elements in the model – entity, attribute, and relationship – is represented by a shape. An entity is a rectangle, an attribute is an oval, a relationship is a diamond. There are some variations on this, but at its heart, the ER model can do a lot of heavy lifting with these three shapes.

Make your own ER diagram in Gleek.

Let’s say we’re designing a user database for an online multiplayer game. At the basic level, we want the player’s email address, name, nickname, password, location, date of birth and sign-up date. Each of these is an attribute. The player will get more attributes over time as they get more involved in our game, but for now that’s enough to describe a new sign-up in our database.

So our player entity is represented by a rectangle with the word player inside it.

The attributes listed above (email address, name, nickname, password, location, date of birth and sign-up date) are represented by ovals.

That gives us our entities, but what about relationships? We’re going to let our players become friends with each other so that there’s a sense of community. The friend relationship is represented by a diamond between players.

Later on, we’ll introduce clans to fuel competition. Clan will become another relationship between players. Clans will be entities in themselves, with relationships to many players, but will also have their own attributes.

Entities can also be gathered into entity sets. In our game database, the set of all players is one possible set.

What kinds of attributes can the ER model represent?

There are several different types of attributes and they each do a different job in database design. Each attribute also has some inherent limitations on the values that it can contain, regardless of type. You will set these yourself when designing the database. For instance, the player’s name can’t contain any numbers, while the nickname can. Date of birth is all numbers, but in a particular date format.

1. Simple attribute

You can’t divide a simple attribute any further. That’s it. You’ve got the record and you can’t break it down any more than that. In our example, the player’s email address is a simple attribute. We could have split the email address into user and domain, but we don’t need that level of granularity for a plain old email address. Nickname is another simple attribute. We don’t want player names to be publicly visible, so we’ll give them a single string of alphanumeric characters for chat and messaging.

2. Composite attribute

You can use composite attributes to contain simple attributes. You can always break a composite attribute down into two or more simple attributes (or even more composite attributes, but we’ll get into that later). For our database, the player’s name is being recorded as first name and last name. Two simple attributes combine to create a composite attribute.

3. Derived attribute

These are useful attributes that you can derive from other attributes. We can use our player’s sign-up date to generate an attribute telling us how long they’ve been playing the game. But there are more derived attributes hidden in those basics. When designing our database, we knew that we needed date of birth to make sure that the player is only viewing age-appropriate content. But each attribute can do double duty, and in this case, date of birth is also handy for calculating the player’s age.

4. Single-valued attribute

You can only store one value for each entity. Date of birth is a good example for us. A player can only have a single date of birth. Single-valued attributes can be either simple or composite.

5. Multi-valued attribute

You can store more than one value for each entity. In our example, we want to make sure that we can get in touch with our players no matter what, so we’ll let them add more than one email address as a backup. Multi-valued attributes can be either simple or composite.

Focus on composite attributes

Composite attributes are represented in the ER model as an oval shape connected to other ovals.

In our example, we’ve already made good use of our first composite attribute. We can break down the player’s name attribute into first and last name. We decided to store it that way so that we can address the player by their first name when we want to send them an email.

Make your own ER diagram in Gleek.

Another useful composite attribute associated with our players is their location. We didn’t want to get too intrusive and ask for a full postal address when we don’t really need it. We can get close enough by using the player’s IP address. That gives us the country and probably the city where the player is located. So our composite entity location comprises city and country.

Beyond our example, composite attributes can be very useful elements in a database. For instance, you can break address down into street, city, state and country and use these for marketing or customization. You don’t always need to have access to the attributes contained in a composite attribute, but it’s worth keeping them separate just in case!

You can think of composite attributes as a hierarchy, where the nested component attributes can be broken down ever further. A component attribute can also be a composite attribute, and so on.

Why use Gleek?

There are plenty of graphical tools for creating ER diagrams. But sometimes you don’t want to fiddle with dragging and dropping. You want to keep your hands securely on the keyboard and quickly bash out a diagram. Type, don’t draw or drag, and Gleek will do the rest, giving you a clean, fast diagram to informally explain your database structure.

ER diagram example: online shopping system (Crow’s Foot notation)

All about ER model cardinality with examples

Exploring One-to-Many Relationship Examples in Databases

How to represent a weak entity in ER diagram

Relational schema vs. ER diagrams: A detailed comparison

ER diagram for an E-commerce website with Gleek's AI chat

Guide to entity-relationship diagram notations & symbols

How do you convert an ER diagram into a relational schema?

Improving your database skills with many-to-many relationship examples

What is the entity-relationship diagram in database design?

Crow’s foot notation in entity-relationship diagrams

Crow’s Foot vs. Chen notation: detailed comparison for 2022