This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Designing Databases - an Introduction (this blog) Designing our Example Database 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.

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!

Designing Databases - an Introduction (this blog)
Designing our Example Database
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 07 July 2023

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:

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:

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!

Parts of this blog
Designing Databases - an Introduction (this blog) Designing our Example Database Modelling Many-to-many Relationships

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR