560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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!
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!
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:
What you need is a relational database, consisting of different tables linked together!
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.
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.
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)
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 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!).
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|
25 Aytoun Street