Tutorials‎ > ‎

Basics Of Database Table Design - Pt1

posted 5 Sep 2012, 02:13 by Alistair Hamilton   [ updated 29 Mar 2015, 07:20 ]
database table design
The techniques outlined in this series of tutorials apply to all relational databases including high performance backend systems like MySQL. However, this series is primarily aimed at those users of MS Access et al who, from my experience, generally have very little design skills or knowledge.

Over the years, I've designed many databases primarily in various flavours of MS Access. I've also presented even more training courses on behalf of numerous training organisations. When presenting these courses, the course material those firms provided always missed out one very important feature - the design process. The material invariably discussed how to use the application interface, but always failed to mention what I would regard as the most important topic.

Much of the blame for this can be apportioned to MS Access itself and its inclusion in the MS Office suite. Companies would expect their staff to pick up and use MS Access just as comfortably as they did with MS Word and MS Excel. This was always going to be unrealistic.

Desktop database applications like MS Access are sophisticated and powerful development tools. They are completely different beasts from their word processing and spreadsheet brethren. There's a world of difference between designing a database using MS Access and using an MS Access designed database.

The delegates attending those courses simply didn't have the design mindset required to use these applications properly from a development point of view. The databases produced by those people after attending the courses were nothing more than large spreadsheets. The courses provided by those training organisations were clearly inadequate - and those firms should have recognised that.

I took to telling delegates to ignore the training course material they were given - at least for the first morning - and I spent the first few hours giving them an overview of the design process, in much the same way as I'm about to do here.

The object of this and subsequent articles to follow is to provide those users with a grounding in the process of database table design that will ensure any databases they create will maintain the integrity of their data and allow for easy expansion without having to redesign the database every couple of months.

The Design Process

There are three main stages involved in the design of your database tables:
  1. Specification – this process involves the drafting of a set of requirements that essentially define what data the database needs to hold and how sections of that data should relate to other sections of data.
  2. Modelling or Design – This is the process whereby the database tables are organised (designed) in accordance with the requirements as laid out in the specification.
  3. Implementation – This is the only stage of this three-stage process that requires the use of a computer and involves the creation of the database tables in software as per the specification and model that came before it.
Obviously there’s a lot more involved in the construction of your database application not least of which is the design of the software interface. However, as I am concentrating on the table design, I will not consider this aspect further in these articles. The main thrust of this series will be stage two – the design or modelling of the data tables and how they inter-relate.

Exercise 1

Now that I have laid the groundwork for the articles to come, let me finish this introduction by giving you a little mental exercise. This is something I always did on the aforementioned training courses in order to try and get delegates thinking about the design process, the data they will be modelling and what attributes of that data are important to the model they are creating.

Getting clear in your own mind what the database is meant to achieve is an important step and is all wrapped up in step 1 of the design process outlined above.

Imagine you work for a manufacture of coffee tables and your boss has asked for a database to be created for all tables the firm makes. Depending on your role in the company, the 'attributes' you want to record for those tables may differ.

For example, if you work in the manufacturing department you might assume that the information that needs to be recorded is to do with the manufacturing process for each table: the material used for the surface; the material used for the legs; what type of screws hold it all together; how long it takes to assemble; should it be painted/stained/varnished and so on.

If you worked in the sales department however, you won't be interested in what screws are used for example. The 'attributes' that concern you will revolve around your job function: sale price; profit margin; warranty; colour/finish; transport costs and so on.

You can see from this that the same table could result in two very different databases being created. That is why it is vitally important that specification process is rigorous and accurate. Doing so will help minimise, if not eliminate, complete redesigns at a later date.

So, your mission, should you decide to accept it, is to spend some time analysing the odd household object. Whenever you're taking 5 mins to put your feet up, pick out something around your house and mentally note what 'attributes' it has. Think about how those attributes describe that object. Can those attributes be applied to any other similar but different object in the house?

Ask your partner to have a go as well, but don't discuss it. You may find it surprising how different your attributes are.