Relational Database Design

Introduction

I probably should have done this section before the SQL School section but I didn't so let's try and get over that eh?

Much of this section is not complete. I know it is very annoying seeing 'Under Construction' all over the place - but work on this site has to be scheduled in between real work, so please be patient.

What is a Database?

A Collection of data. A Filing cabinet is a database, that pile of stickies on your desk is a database, iTunes is a database.

In this tutorial when we say database we are talking about a database that is managed by a Relational Database Management System (RDBMS). It doesn't matter if you are going to use MySQL, SQLite, Postgres, Oracle or even that Microsoft thingy - relational database design is the same.

One of the biggest design errors I see with new DB designers is multiple database creation. Unfortunately some RDBMS solutions call what we (correctly) call tables - databases. So, new designers end up creating millions of databases when they only really needed to create one!

In fact at the design stage we are not even talking about databases and tables we are talking about Entities - more about them later.

A Relational Database is a collection of many, possibly related tables

Our aim in designing a database is to break down our data to its Atomic level - i.e.: the smallest detail that we are interested in. We do not have to break everything down to an absolute atomic level - for example if we had a requirement to analyse just the colour of cars, we would not need to break down cars into makes and models, just cars would do.

Breaking down data to the atomic level we require

Background History

You do not need to know any of this ancient history, but you may be interested in how Relational Database technology came about. So, heres some hopefully interesting pages - Relational Database History

Why Use A Relational Database?

Because once you understand a few simple database design principals then you will be able to store (without duplication), sort and most importantly retrieve your data using the remarkably powerful and simple Structured Query Language (SQL).

A Relational Database allows you to efficiently Store, Sort and Find Data.

A Relational Database allows you to manipulate and find data using SQL.

What is SQL?

SQL stands for - Structured Query Language.

First is is Pronounced 'Sequel', It is NOT pronounced as the letters -'Ess Queue Ell'! I have had many arguments about this. I am right. If your disagree then leave my website immediately! For more proof head over to the History Department and read about how in the olden days, IBM folks invented Sequel!

SQL provides all the tools you need to [C]reate, [R]etrieve, [U]pdate and [D]elete data from a relational database. This CRUD matrix of operations provides everything you need to use a database! Fantastic. head off to our SQL School Section to learn all about SQL!

SQL is a remarkably simple and powerful language that gives good CRUD!

Always be SAD!

Does anyone use the good old Structured Systems Analysis and Design Method (SSADM) any more? Cripoes that takes me back more years than I want to remember.

SSADM Waterfall

Forget aout the first [S] - Structured - it's a bit like: Step 1 - Enage Brain, Step 2 - Pickup pencil ......

[S]TRATEGY - Why do you want a database ?

[A]NALYSIS - What data wil be in the database ?

[D]ESIGN - How are you going to do it ?

For the [M] - Method, It's time to get the Code Monkeys out of the cage, shown as Implemenation on my diagram, but who cares this tutorial is about database design not implementation!

Do SAD and you won't be sad. When you get to [A] return often to [S] - does the data allow you to fulfill the purpose of the database? From [D] pay visists to [A] and [S] - does the design manage the data at the lowest required (Atomic) level?

Why Design a Database?

Because if you don't your project will fail! I have seen many. many, many ( that's loads) of code monkeys trying to 'fix' bad database design by writing more and more code.

Get the Database design wrong and spend the rest of your life coding fixes.

madman

How Do I Design My Database?

Now that's a much more sensible question! You use something that is called a Data Model. You may have heard of Entity Models(EM), Entity Relationship Diagrams (ERD) and other names that so called techies have invented to frighten the normal folks. The first section in this guide will show you how to create a Data or Relational Model.

Create a Data Model - you know it makes sense!

What is a Data Model?

OK here's what this Wikipedia page (hardcore page that!) says about Relational Models:

The relational model's central idea is to describe a database as a collection of predicates over a finite set of predicate variables, describing on the possible values and combinations of values. The content of the database at any given time is a finite (logical) of the database, i.e. a set of , one per predicate variable, such that all predicates are satisfied. A request for information from the database (a) is also a predicate.

Hmmm. I'm sure that makes sense to some clever folks.

A Relational Model is a pictorial representation of your data and its relationships.

What, Where, How When, Why ????

Read the FAQS!

OK Let's go!

Now you can use this handy navigation bar below to whizz through the pages of this tutorial, or you can use the drop down box at the top of this page - it's up to you.