The Big R-Book

♣14♣SQL

14.1 Designing the Database

Before starting to create tables, it is a good idea to reflect on how the tables should look like and what is optimal. Good database design starts with an entity-relationship diagram (ER diagram). The ER diagram is a structured way of representing data and relations and the example of the library from Chapter 13 “RDBMS” on page 219 is represented in Figure 14.1 on page 224.

ER

entity relationship

The ER diagram is designed to be intuitive and understandable with just a few words of explanation. In our ER diagram, we notice that:

• the entities are in the rectangles;
• their attributes are in ellipsoids, where the optional fields have a dotted line and the unique identifiers (the primary keys (PKs)) are underlined;

PK

primary key

• the relations between entities are the green diamonds.

This ER diagram is the ideal instrument for the analyst to talk to the data owner or subject matter expert (SME)¹. So the ER diagram is the tool to make sure the analyst understands all dependencies and relations.

For example, it is critical to understand that one author has only one pen-name in our design. Is this a good choice now and in the near future?

Once the ER diagram is agreed, we can start focussing on the database design. The first step of the database design is a layout of the tables and relations. In our – very simple – case this follows directly from the tables Table 13.1 on page 220, Table 13.2 on page 220, and Table 13.3 on page 220. ...