Est. reading time: 3 minutes
Howto - Planning your SQL database structure - a simple example

Planning Your SQL Database Structure

This article is not from a professional SQL Database Administrator’s perspective. It is from someone who has been using MySQL, PostgreSQL and MariaDB for the last 18 years. I have found some basic principles around database design that make life a lot easier. And then researched the best practice whitepapers to confirm the soundness of that knowledge.

Know your queries

When you start designing a database, first generate a sample of data that you will be using. I often find that it is easier to work with the desired output or end result first. Then step back to the data collection or input. In this way, I know the output and have to find a way to generate the desired output from my raw data. This allows me to prioritise my raw data collection to focus on the data used to generate the end result first. If you do not do this, you may end up collecting ALL data on the assumption that you “might” need it later. Having a lot of data to work with is not always a bad thing, but often this data ends up consuming memory and processing power without ever being processed again. Structuring your database properly also allows you to add another table to accommodate data at a later stage.

Know the relationships

How do your data elements relate to each other and do you have some common index that you could use to tie it all together. You may have a client record list and the records all have a unique number. This can become your “Unique/Primary  Key Index”. If properly structured, this “Primary Key Index” may be the only piece of information that occurs more than once in your whole database.

Let us take a Car Dealership. If you have a list of clients, their records may contain personal details such as ID, Home Address, Work Address, Job Title, Bank and Income level. You may also have details on the make, model and mileage of their vehicle or vehicles. Added to this you may have a record of each time they have had their vehicle serviced at your dealership. Your primary key will be your client record number. If you do not have one, you can ask your database to create an “Auto-Increment Primary Index ID field which will automatically generate a unique number for each record added.

Planning the structure

We will create four tables. The first table, which we will call client_idx will be basic client info. Here we have four columns containing a unique customer number (idx),  the customer’s first name (fname), the customer’s last name (lname) and phone number (phone). This still looks a lot like a normal spreadsheet. A relational database is actually nothing more than a set of spreadsheets joined by certain key indexes.

The second table for personal information will contain the unique customer number (idxp) and will have columns for a home address, work address, occupation, ID, bank, banking details and income category ( a number from 1 to 4 ).

The third table for vehicle details will have the unique customer number (idxv). It will have columns for make, model, year, mileage, registration, service date, last service date, last service mileage and description of service. We will also have a notes column for any additional information.

The last table will be a simple two column lookup tables for our four income categories.

Database tables

The Relationships and using the SQL Database

If we need to find the car make for Susan Harris, we look up the idx for her (2). We then find the vehicle make associated with idxv (2) under table 3 which is a Ford. To find an income bracket needs 3 lookups. First, (1) – the customer idx, then (2) the same idxp – income category number. Finally, (3) the bracket under table 4 which has the matching income category number. This can be done with a JOIN in a single query as can all the lookups in the above tables.

As you can see, a database structure need not be a mystery. Plan ahead and know your data output needs.

Happy Hosting!

The Author

Michael O.

Michael is the founder, managing director, and CEO of HOSTAFRICA. He studied at Friedrich Schiller University Jena and was inspired by Cape Town's beauty to bring his German expertise to Africa. Before HOSTAFRICA, Michael was the Managing Director of Deutsche Börse Cloud Exchange AG, one of Germany's largest virtual server providers.

More posts from Michael

Related posts