Contents

Creating an entity-relationship diagram (ERD)

On the last page you learned how records from different tables are linked to each other in a relational database. Before you start creating and linking tables it is important that you think about the entities that exist in your system and that you decide how these entities are related. In database design, the entities and their relationships are represented in an entity-relationship diagram (ERD). The ERD is the result of the database design process.

Entities

You may be wondering what an entity is. Well, it's a "thing". In a system. There. My mother always wanted me to become a teacher, because I explain things so well.

In the context of database design an entity is anything that might deserve its own table in your database model. When you design a database you should identify the entities in the system you are creating. This is mostly a matter of talking to your client or to yourself and figuring out what data your system will be working with.

Let's take a webshop system as an example. A webshop sells products. A product would be a very obvious entity in a webshop system. Products are ordered by customers. There, two more obvious entities we've already seen: orders and customers.

An order is paid for by the customer... that interesting. Are we going to have a payment table in our webshop database? Possibly. But isn't the payment a "single piece of information" that belongs to an order? That's also possible.

If you are not sure just think about what information you want to store about a payment. You might want to store the payment method and the payment date. These are still single pieces of information that could belong to an order. You could interpret these as the payment method of an order and the payment date of an order, so I don't see the necessity to model payment as a separate table, although conceptually, you could see a payment as an "entity", because you might regard it as a separate container of information (payment date, payment method).

Let's not get too academic

As you can see there is a difference between an entity an an actual table in your database. IT specialists can become VERY academic about this difference. I am not such an IT specialist. This difference depends on the way you look at your data. If you look at data modelling from a software perspective you might come up with a lot of entities that don't translate directly to tables. In this tutorial we are looking at data strictly from a database perspective and in our little world an entity translates to a table.

database design degree

Hang in there, you are really close to attaining your database design degree.

As you can see, deciding what entities your system has is a bit of an intellectual process that takes some experience and that is often subject to some changing and tinkering and reconsideration, but it's certainly not rocket science.

 

erd

An entity-relationship diagram can get pretty large if you are building a complex application. Some contain hundreds or even thousands of tables.

Relationships

The second step of database design is deciding what relationships exist between the entities in your system. Now this may get a little complicated sometimes, but again, it's no rocket science. With some experience and some (re)consideration you usually end up with a database model that is right or almost right.

I already introduced you to the one-to-many relationship and I will tell you a lot more about relationships in the coming pages of this tutorial, so I am not going to go into them here. Just remember that deciding what relationships your entities have is an important part of database design and the relationships are represented in the entity-relationship diagram.