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.
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!
