BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
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:
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.
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!