Designing databases for SQL Server and Access
Part one of a three-part series of blogs

Designing relational databases is easy once you learn the few basic rules, and the procedure is identical whether you're using SQL Server or Access. This blog illuminates the subject!

  1. Designing Databases - an Introduction (this blog)
  2. Designing our Example Database
  3. Modelling Many-to-many Relationships

This blog is part of our tutorial on writing SQL.  You can also learn SQL on one our introduction or advanced SQL courses.

Posted by Andy Brown on 23 April 2012

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:

List of clients in Excel

The Excel spreadsheet containing your list of clients

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

  • Your company's data entry clerks keep mistyping company names, making it difficult to extract meaningful data.
  • 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:

Films database relationships

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:

List of fields for the table of films

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:

The film table rows

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:

Film primary key

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:

Two fields linked together

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!

 

  1. Designing Databases - an Introduction (this blog)
  2. Designing our Example Database
  3. Modelling Many-to-many Relationships
This blog has 0 threads Add post