557 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 three 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!
In real life, people move jobs: so Bob may work for Shell one year, but IBM the next. This inconsiderate behaviour on the part of human beings complicates our database!
In the real world, you have two tables: the people who could potentially work for companies, and the companies for whom the people could work.
The link between the two tables shown above is therefore many-to-many:
Given that every relationship in a relational database is one-to-many, how can we get round this?
The solution to this problem is to create a table which contains the children of both the companies and the persons table. This will contain the following fields/columns:
|Field/column||What it contains|
|CompanyId||The company for whom this person worked for this job|
|PersonId||The person who worked for this job|
|DateJoined||The date the person joined the company|
|DateLeft||The date the person left the company (may be blank)|
|JobId||The unique number of each record (see below)|
Thus the final database diagram would look like this:
The final design contains 3 linked tables
You can create two different primary keys for tables like the jobs one above. One possibility is to create a brand new field/column to number each record:
The JobId field/column numbers each record
In the example above, the selected row/record shows that person number 1 (Alan Arbib) started work for company number 3 (Microsoft) on 1st May 2010, having previously worked at Shell (company 1) and IBM (company 2).
The other possibility is to create a primary key based on the fact that the combination of CompanyId and PersonId must be unique:
Here the composite primary key contains the company and person numbers.
If you do go down this route, you must be certain that you can't repeat the same PersonId/CompanyId combination in the table. Thought: what happens if Alan Arbib decides at some point in the future to rejoin Shell or IBM?
And that's it! A typical relational database will contain tens - perhaps even hundreds - of tables joined together with relationships, but that's essentially all of the building blocks taken care of.
|Parts of this blog|
25 Aytoun Street