I've been working with Relational Databases for years and one thing has always been clear to me - that most people just don't 'get' them. This series of posts aims to correct that using examples from one of the databases I work with in my practice, Ninox. You could easily follow this series using whichever database platform you usually use - including Airtable and Knack.

A Little History

Back in the late 1980s, I was in corporate sales for a major (at the time) HR software company and bidding for a large contract where the client was going to be heavily customising whatever solution they chose. Our competition was a major Relational Database supplier.

Whilst I was confident in my solution, I thought I needed to understand the competition, so I bought a database program (Paradox as it happens) for my computer and taught myself the basic concepts. Anyway, this self education allowed me to make the case for our solution and I won the business - and at the same time became fascinated by databases and what you could do with them - even building my own simple CRM.

Moving forward over half a decade, Microsoft introduced Access and I had a crazy thought that I would build my own HR solution - which I did over the next year or so, finally starting my HR software business in January 1996. The business became a significant player in the UK market and I successfully sold it in 2010 - since which time I have been advising people about cloud based database solutions.

The point of this story is to relate the fact that you really don't need to be an IT guru of some sort to understand databases (although you might just become one) and make them work for you - many people start off with a business need and go from there!

Objectives of this Series

Over the past few years, I have seen many people get themselves into a real mess with databases and I want to dispel some of the myths and shine some light on how designing and implementing a database solution both reflects the real world it is modelling and can be fun and rewarding!


Reflecting the Real World

You may have seen or heard some of the 'language' of databases - Inner Joins, Normalisation, Foreign Keys or Constraints for example?

If so, you'll be delighted to hear that although these are important concepts, we're going to start the journey by considering how databases reflect the physical things and processes of the real world. There are a couple of terms that we will need to understand though:

Tables - these are a bit like a single spreadsheet - a set of rows and columns in which we hold data about a thing or a process

Fields - sometimes called Columns, these are the individual components within a table in which we hold data

We'll leave it there for now…

In order to illustrate how a database's organisation reflects the real world, I'm going to work through how we might go about building a Contact database and how this may subsequently grow into what you might call a CRM.

Starting at the beginning, let's assume we want to build a list of all the Contacts we deal with in our business. What information are we likely to need to hold about each person:

• Name (First and Last)
• Address
• Email
• Mobile
• Company (assuming we deal with businesses)
• Job Title (same assumption)
• Notes

When tackling this, a high proportion of people reach out for a Spreadsheet (Excel, Google Sheets, Apple Numbers etc) and simply create a new sheet with these headings and start typing. In fact, if that's definitely all that's needed then that would be fine - but generally requirements expand over time.

Reflecting this in a Database

As this is a series about Databases, I'm not going to show you how to create a spreadsheet (you probably already know). What does our 'spec' mean in database terms:

We need to hold information about a 'thing' called a Contact - we'll do this in a Table called Contacts

We have a number of pieces of information we need to hold about each Contact - they will each be a Field

In all database packages and platforms, there are many different types of fields - text, numbers, dates plus special ones for email addresses, phone numbers, etc.

This series is not aiming to teach you how to use any particular product - rather the concepts - so here is the result of our work so far in Ninox, where I've created a Contacts table:





So, we're done, right?

If you look at the data in the screenshot, you'll see that two of the contacts work at KA Software and Kirkness Associates Software - and these are actually both the same thing (Company) but input differently. Also, if you look back in this post you'll see that I said that a database design tends to reflect the things and processes in the real world - isn't a Company a 'thing'?

These are the type of issues that are almost always present when people build their database in a spreadsheet.

To avoid incorrect entries and to enable us to look at our data by Company etc, we need a way to represent a Company in the database - and as it's definitely a Thing, this is going to be in a table called Companies.

What about the Address field though? In a B2B scenario, it's most likely that this is information belonging to the Company (not the Contact). So, in the Company table we'll need to have a Company Name field and an Address field (at least).

If we hold details of Companies in a separate table, then we are still going to need to be able to identify which Company each Contact belongs to - and this is where the magic (the word Relational in Relational Database) comes in. We can create a field in Contacts which references Companies, linking each Contact with One Company (this is know as a One to Many or Parent/Child relationship).

So, now, we have two Tables representing two kinds of real world Things, Companies and Contacts and can start to create screen layouts which make this information more accessible and useful:




Here, we are looking at a Company and the screen includes a list of all the Contacts belonging to that company - easy when the relational magic sauce is applied!

I think this is a good place to stop for the time being - hopefully you've been able to see how you can map real world things into your database design and how that can lead to a more useful result.