Main Content

Databases - why data is split into separate tables, and how to join them

Archive - Originally posted on "The Horse's Mouth" - 2010-11-20 09:33:28 - Graham Ellis

If you're travelling from Chippenham to London by train, you may want to grab a quick coffee at the station before you set off - and where better than Steamers? And if you were traveling to Plymouth, Bristol, Southampton, Llandrindod Wells or Cheltenham, you would use Steamers too.

Let's assume I'm keeping a database of journeys and where to get a coffee before I start the journey ... the data above is the sort of thing that I'll need to store. And I'll only want to store the details for Steamers ONCE no matter how many places I may travel to from Chippenham - to store the data multiple times would be (a) wasteful of space, (b) require a lot of effort in setting things up, (c) be a lot of work to update when things changed and (d) be prone to small errors which will be hard to spot as they will only come up on very occasional queries.

So ...
• I store the data for each journey ONCE in a table of journeys
• I store the data for each eating place ONCE in a table of eating places
and
• I "JOIN" the tables each time I extract data about where to eat prior to a journey.

That is the principle of joining tables in a database!

The syntax will look something like this:
  select * from place join journey on called = start_at
and you can join more that two tables if you wish [example]- one of our customers joins over 20 ...

Once you start linking tables in this way, you come up with some extra questions when you're maintaining the database such as "do we have any journeys where there's no-where to eat at the start", and "can I have a list of all journeys, irrespective of whether I can eat at the starting place or not ..."; those can be achieved using a LEFT JOIN which ensures that there is at least one record on the output for every input in the left hand table, even of there is nothing to match it in the right hand table (the resultant record is NULL padded).

I've set up the above example - table creation and a big variety of joins, with sample output - [here] in our examples directory. And there's a further description - join v left join v right join - [here] on this blog. We cover database design (the MySQL clavour) on our MySQL course, and how to access databases from applications on our various language courses, such as our courses in PHP, Perl and Java.