What is the best way to plan my database?

Step 1 – Understand the Purpose

Before we dive into the details of, “What is the best way to plan my database?” We need to ask a more basic question: What is my database for? Well, OK, that seems a bit obvious: It’s a place to store information so we can look it up again later.

But let’s try to go a bit deeper than that. The more effort we make, to understand what we are trying to achieve, the happier we will be with the outcome. So, at this stage we need to ask a lot of questions:

Step 2 – Gather Requirements

• What information are we going to collect?
• What do we want to do with that information?
• Who will be collecting the information?
• Who will be using the information later?

Step 3 – Identify the “Things” We Want to Track

The idea here is very simple, but it’s surprisingly hard to avoid slipping into jargon. Most guides to planning a database will start talking about “Entities” and “Attributes” at this point, which is fine once you are familiar with the terms but can be confusing at first.

In Core, we use the terms “Content Types” and “Fields”. Which may be a bit clearer but still not entirely jargon free. Anyway, it all becomes much clearer with some examples.

A “Content Type” is a type of thing we want to collect information about. It could be a person, a company, a physical thing like a car or a business process like a sale. The “Fields” are the properties of that thing, that we want to record.

There are lots of ways we could approach this, from a simple list or an excel spreadsheet through to fancy database planning tools. But I like to use a simple bullet point list:

Person

• Name
• Date of Birth
• Telephone Number
• Favourite Colour

Car

• Manufacturer
• Model
• Colour
• Registration
• Cost Price

Sale

• Date
• Customer Name
• Customer Telephone
• Car Manufacturer
• Car Model
• Car Registration
• Cost Price
• Sale Price

Etc …

Step 4 – Define Relationships

This step is really important, and one that is most commonly neglected. If this is your first time planning a database, there is a good chance you will skip over this section too quickly and regret it later.

It’s also another area where the ideas are really simple but can often be made confusing by the jargon. If someone starts banging on about “Data Normalisation” your eyes might quickly glaze over. But if we stick to talking about how one “Thing” relates to another “Thing” then maybe we have a chance.

Even better, if we talk about specific things, i.e. the People, Cars and Sales in our example, then it’s not confusing at all, and the work done in step 2 really starts to pay off. So, let’s say we know from our requirements gathering, that we want to record the Sales we make, but better than that, we have two specific requirements:

1. We want to know who each car was sold to in case of a query or complaint
2. We want to know how much profit we made on the sale so we can track financial performance

With these requirements, we could create our Sale Content Type like this:

Sale

• Date
• Customer Name
• Customer Telephone
• Car Manufacturer
• Car Model
• Car Registration
• Cost Price
• Sale Price

And for a simple business, where these are the only two requirements, that would be all we need. We wouldn’t even need separate Content Types for People and Cars.

But suppose we add in some more requirements. Let’s say we don’t just make one-off sales to customers who never come back. We actually have a lot of repeat business. In fact, many of our customers manage a fleet of vehicles for their own business. So now it’s important to add two more requirements:

• We want to know how many cars have been sold to each customer
• We want to know the profitability not just of an individual sale, but a breakdown by make and model, maybe even by colour. Do we make a better profit on black cars or red cars? Etc.

Now, in theory, we could still do this with the same, simple database structure shown above. But it does start to lead us into some tricky terrain:

• What happens if the customer changes their telephone number? Do we have to update each individual record?
• What happens if we have two “John Smith”s?
• What happens if someone mistypes a name? Are “John Smith” and “John Smithe” the same person or two different people? What about “Johnny Smith”? etc.
• And all the same types of question apply to the Make, Model and Colour of the Cars.

The simple way to think about this is to try to avoid duplication, it’s basically about keeping your data organized, avoiding repetition, and making it easier to work with. You don’t want to record the name and telephone number in every Sale record. You just want to link the Sale to the Person record. Which leads us to a simplified structure:

Person

• Name
• Telephone Number

Car

• Manufacturer
• Model
• Colour
• Registration
• Cost Price

Sale

• Date
• Customer (Links to Person)
• Car (Links to Car)
• Sale Price

We could take this even further, to help users keep the data consistent, we could make separate Content Types for Manufacturers and Models:

Car

• Manufacturer (Links to Car Manufacturer)
• Model (Links to Car Model)
• Colour
• Registration
• Cost Price

Car Manufacturer

• Name

Car Model

• Manufacturer (Links to Car Manufacturer)
• Model
• Etc

This helps us avoid any problems with misspellings and questions like: Do we want to track the “Ecosport Active” separately from the “Ecosport Titanium” or are they both just “Ecosports”?

We can make those decisions once and let users choose from the options provided, that way we don’t have to worry about different users making different choices. The person using the financial reports will be glad we made the effort. Even better if that person is you.

Step 5 – Build & Test

The precise steps to build your database are outside the scope of this blog post. If you are building it in Core, then hopefully, it should be easy and intuitive. You will find plenty of help articles and other blog posts about that.

Core takes care of a lot of things for you, Primary Keys, Foreign Keys, Backups, Database Security, etc. If you are using another system, then you should definitely look deeper into those topics before you go any further.

Step 6 – Review & Improve

Remember, database planning is an ongoing process. As your needs change, be ready to update your design accordingly. Even if your needs don’t change, your understanding of what your needs are, probably will.

Summary

In the follow-up to this blog post, we will look at what to do if you change your mind and how Core can make it easy for you to restructure your data when necessary. To find out more about our Core give us a call on 0330 2020 118 or Contact Us from our webpage.

Good luck and have fun planning your database!