You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Designing Databases - an Introduction

This blog shows how to design a relational database. You'll find lots
of other wordy articles on the Internet about third normal form, entity diagrams
and the like ... this one just uses common sense!

The Problem we're Trying to Solve

Imagine that you have decided to build a database of contacts for your
company or organisation (a common requirement). Here's what your current
Excel spreadsheet of contacts looks like:

The Excel spreadsheet containing your list of clients

You are getting more and more clients, and notice the following problems:

Because you're storing duplicate company information more
than once, the size of your spreadsheet is becoming
unmanageable.

What you need is a relational database, consisting of different tables linked
together!

An Example of a Relational Database

Here's an example database diagram containing details of films (movies),
together with their directors, studios, etc:

All of the tables are joined together with relationships

The main table contains a list of films - for each film you can see (clockwise
from bottom left) its country of origin, language, director, studio and
certificate.

Access and SQL Server Terminology

All of this blog applies equally well to Access or SQL Server. The only
thing to note is that the two software applications use slightly different
terminology:

Access term

SQL Server term

What it means

Field

Column

Any bit of information for a table

Record

Row

Any collected set of fields

These terms will make much more sense if you read the next heading.
I've chosen to include both terms in all cases.

Records/Rows and
Fields/Columns

Each table consists of a number of fields (or columns), each of which gives a
bit of information about each record (or row) in the table:

The
fields/columns for the films table are shown here - the field/column called
FilmName is shown selected. This holds the name of each film.

Here are some
of the records/rows in this table:

Each
record or row contains the details of one film (or movie, if you
prefer)

The Primary Key Field/Column

There is one very special field/column in each table: the primary key.
This is the field/column which uniquely defines each record/row:

The
primary key is shown with a key symbol next to
it. For the films table, it is the
FilmId field/column. Two films
may have the same name - for example:

Primary keys are nearly always autonumber or autoincrement numerical
fields/columns: that is, the first record/row is number 1, the second number 2,
etc.

One-to-Many Relationships

In a relational database, tables are linked together by relationships.
Far less exciting than the real-life equivalent, these show how a field/column
in one table is linked to its counterpart in another:

Here the
DirectorId field/column in the
tblDirector table shares a value with the
FilmDirectorId field/column in the
tblFilm table.

Every such relationship is one-to-many, or parent-child. What this means
is that for every pair of linked tables, one of the tables is a parent and one a
child. In our example above:

the tblDirector table is the parent; and

the tblFilm table is the child.

The reason for this is that every director can have lots of corresponding
films in the tblFilm table, but the converse is not true (we'll
ignore the case where a film has two or more different directors!).

How to Design a Database

The approach we'll take is to imagine each table is a peg, and decide which
bits of information we want to hang on which peg. So now that we've got
the theory behind us, let's look at how this works in practice!