Skip to content

Logical design

Logical design is essentially the process of making the conceptual design work once it has been agreed by the users. Whereas the conceptual design needs to reflect the users' understanding of the world, the logical design needs to provide the detail on how the model can be represented using relational technology.

The starting point for the logical design should be the ER diagram from the conceptual design stage. The outputs from the logical design stage are a new ER diagram that reflects the actual relational implementation, and a data dictionary.

ER diagram

Confusion can arise because there are two ER diagrams involved. Remember that the first reflects the community view of the data and must be comprehensible to the users. The second represents the technical view of the model which takes into account the requirements of the relational platform. This level of detail is usually too difficult for users to understand. Possible causes of confusion for users might be:

  • The inclusion of foreign keys
  • The existence of link entities introduced to resolve : relationships
  • The replacement of generalisation/specialisation relationships with a practical equivalent

The following example is taken from the worked example used all the way through Connolly and Begg. If you are finding it difficult to follow any of the processes discussed here, please go through this example in the book. Each stage of refining the data model is discussed in detail with examples.

When designing a database for a property agency, it is established that PROPERTY and CLIENT are two important entity types. A CLIENT can view a PROPERTY, and when they do, they can leave a comment. The conceptual model might be represented as shown below:

Conceptual model

Notice that there is a : relationship between the two entities. Notice, too, that the relationship itself has some attributes associated with it. From what you know of relational structure, you should realise that this is not possible: attributes are only found in tables and relationships are represented by pairs of keys. In the conceptual model, though, this is perfectly valid. The purpose of the conceptual model is to understand the world as the users see it.

During the logical design stage, the challenge is to take the agreed conceptual model and map it onto a set of tables that will give an equivalent description of the subject area. You know a third table is required to resolve the : relationship, and the 'floating' attributes can be included. The logical model would therefore look like this:

Logical model

Connolly and Begg suggest the following process for logical design:

  1. Derive relations for the logical model
  2. Validate relations using normalisation (this is covered in week 6)
  3. Validate relations against user transactions
  4. Check integrity constraints
  5. Review logical model with the user

Data dictionary

The data dictionary is a detailed catalogue of the data requirements for every table and every column that will be used in the database. There is no standard format, but a table like the one below is perfectly adequate:

Table name Column name Contents Type Format Domain Mandatory Key Reference
PROPERTY property_no Unique id Integer 09999 1-99999 Y PK
address Postal address Text Xxxxx Y
type Type code Character X F, H Y
rooms Number of rooms Integer 09 1-16 Y
rent Monthly rent Money £0999 100-2000 Y
CLIENT client_no Unique id Integer 09999 1-99999 Y PK
first_name First name Text Xxxxx
last_name Last name Text Xxxxx Y
telephone Telephone number Text [0-9],+,(,),space Y
email email address Text
VIEWING view_date Date of viewing Date dd/mm/yyyy Y PK
comment Notes Text
client_no Client reference Integer 09999 1-99999 Y PK/FK
property_no Property reference Integer 09999 1-99999 Y PK/FK

The data dictionary duplicates some of the information on the ER diagram, but this is useful for cross-checking. Any corrections should be applied to both documents.

The data dictionary also provides additional information which is used in the next design stage.

The term data dictionary is also used in another sense which is briefly discussed in week 9.