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

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.

(written 2010-11-20, updated 2010-12-04) 2723

Associated topics are indexed under

S152 - SQL Primer as Used in MySQL
  [4007] Which database should I use? MySQL v SQLite - (2013-02-16)
  [3060] INSERT, DELETE, REPLACE and UPDATE - changing the content of SQL tables - (2010-11-19)
  [2240] How do I query a database (MySQL)? - (2009-06-15)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [515] MySQL - an FAQ - (2005-12-03)
  [502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [270] NULL in MySQL - (2005-04-06)
  [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)

S157 - More MySQL commands
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [2645] Optimising and caching your MySQL enquiries - (2010-02-22)
  [2644] Counting rows in joined MySQL tables - (2010-02-22)
  [2643] Relating tables with joins in MySQL - (2010-02-21)
  [2448] MySQL - efficiency and other topics - (2009-10-10)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
  [1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
  [1331] MySQL joins revisited - (2007-09-03)
  [1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
  [1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
  [673] Helicopter views and tartans - (2006-04-06)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [567] Combining similar rows from a MySQL database select - (2006-01-17)
  [517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
  [513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
  [449] Matching in MySQL - (2005-09-24)
  [279] Getting a list of unique values from a MySQL column - (2005-04-14)
  [159] MySQL - Optimising Selects - (2004-12-21)

Back to
INSERT, DELETE, REPLACE and UPDATE - changing the content of SQL tables

Previous and next
or
Horse's mouth home

Forward to
Melksham Carnival - getting ready for 2011

Some other Articles

Pictures from the Birkenhead to Belfast crossing
On the way to the course this week
Comments in and on Perl - a case for extreme OO programming
Melksham Carnival - getting ready for 2011
Databases - why data is split into separate tables, and how to join them
Object Orientation in an hour and other Perl Lectures
MacBook Air - hardware and system review
Lots of things to do with and within a C++ class
C++ - a complete example with polymorphism, and how to split it into project files

4086 posts, page by page

Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82 at 50 posts per page

4468
This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

Public Training Courses

Running regularly at our UK training Centre.
[Schedule] - [About] - [Book]

Like this? ??