The Relational Data Model - Normalisation and Effective Database Design

Peter Kitson

ISBN : -

Order a printed copy of this book from Amazon --UNAVAILABLE--

For your free electronic copy of this book please verify the numbers below. 

(We need to do this to make sure you're a person and not a malicious script)

Introduction

I have been designing and building applications, including the databases used by those applications, for several decades now. I have seen similar problems approached by different designs, and this has given me the opportunity to evaluate the effectiveness of one design over another in providing solutions to those problems.

It may not seem obvious to a lot of people, but the design of the database is the heart of any system. If the design is wrong then the whole application will be wrong, either in effectiveness or performance, or even both. No amount of clever coding can compensate for a bad database design. Sometimes when building an application I may encounter a problem which can only be solved effectively by changing the database rather than by changing the code, so change the database is what I do. I may have to try several different designs before I find one that provides the most benefits and the least number of disadvantages, but that is what prototyping is all about.

The biggest problem I have encountered in all these years is where the database design and software development are handled by different teams. The database designers build something according to their rules, and they then expect the developers to write code around this design. This approach is often fraught with disaster as the database designers often have little or no development experience, so they have little or no understanding of how the development language can use that design to achieve the expected results. This happened on a project I worked on in the 1990s, and every time that we, the developers, hit a problem the response from the database designers was always the same: Our design is perfect, so you will have to just code around it. So code around it we did, and not only were we not happy with the result, neither were the users as the entire system ran like a pig with a wooden leg.

In this article I will provide you with some tips on how I go about designing a database in the hope that you may learn from my experience. Note that I do not use any expensive modelling tools, just the Mark I Brain.

What is a database?

This may seem a pretty fundamental question, but unless you know what a database consists of you may find it difficult to build one that can be used effectively. Here is a simple definition of a database:

A database is a collection of information that is organised so that it can easily be accessed, managed, and updated.

A database engine may comply with a combination of any of the following:

• The database is a collection of table, files or datasets.
• Each table is a collection of fields, columns or data items.
• One or more columns in each table may be selected as the primary key.
• There may be additional unique keys or non-unique indexes to assist in data retrieval.
• Columns may be fixed length or variable length.
• Records amy be fixed length or variable length.
• Table and column names may be restricted in length (8, 16 or 32 characters).
• Table and column names may be case-sensitive.

Over the years there have been several different ways of constructing databases, amongst which have been the following:

• The Hierarchical Data Model
• The Network Data Model
• The Relational Data Model

Although I will give a brief summary of the first two, the bulk of this document is concerned with The Relational Data Model as it the most prevalent in today's world.