Logical database design

First we need to understand the requirements of the users. Why do they want a database? What is it supposed to achieve? What real-world process is it designed to emulate?

Then we need to actually create a technical solution – a set of tables, complete with columns, each of which has the correct data type. We need to choose the primary keys, add the correct indexes (clustered and non-clustered) and so on.

In as early as the 1970s we learned that it is not helpful for the users of the system if we mix up these two processes. In other words, as database designers we are comfortable with the difference between a varchar and an nchar; we may see right away that a particular column is going to have to be varchar(50). But it is certainly not necessary to inflict this fascinating information onto the potential users of the system; they simply want a database that works. On the other hand, we cannot design the database for them without talking to them because they are the only people who know what the database should look like.

The good news is that it is perfectly possible to separate the two processes. We can talk to the users and construct a model of the database they want, without adding any of the technical stuff. This model is called the logical model because it is a logical description of what we are going to build.

Once this logical model has been agreed on, we can take it away and add the technical information (data types, indexes etc.). As we do so, we are converting the logical model into what's called the physical model.

So the logical model captures the users' requirements for the database. Since these can be complex we usually use a formal process called Entity Relationship (ER) modeling for this.

How does it work in practice? A business analyst talks to the users and helps them to identify the entities that will appear in the database (Customers, Salespeople, Goods etc.). Each of these has a set of attributes: for example, Customers might have attributes such as Name, Age, Address etc. The business analyst will also help to identify the relationships between these entities (Customers buy Goods, Salespeople sell Goods). This information is all captured diagrammatically.

In practice, when we create a logical model we are not so much designing a database as creating a model of how the business works. So you can also think of a logical model as a model of the business process that is constructed during the database design process.

So, to revisit the question "What must be included in a logical database design?"- the answer is entities, attributes and relationships. However equally importantly, what must NOT be included in the logical model is the geeky stuff -- tables, columns, data types, indexes and so on. We add that later in the physical model.

More information