Database design for Dummies

I was reflecting on the number of times that I have heard the phrase “Well I will provide you with the data and it can’t be that hard to build a database”. In my mind (apart from the indignant rage that this causes) this the same as expecting a plumber to turn up with your new boiler and radiator and having a new central heating system ready in a matter of minutes.

So I thought I would put together a list of the steps that I actually go through when doing database design so that we can build your system. At Fingertips Intelligence we build the majority of our platforms in Filemaker Pro.

1. Thinking time

At the end of the day, I am actually a business analyst / database developer. I know very little about my clients business. Given the diversity of my clients (I’m currently working for a National Firm of Appliance testers, a Chartered Accountants and a Sales Coach) I need to get to grips with their business problem. So the first stage of the database design is always thinking time. I’m oscillating between:-

• Asking an excessive amounts of questions (which of course the client either loves or loathes). You’d be amazed how many times asking a basic question can provoke such rage with a client.
• Having those Eureka moments when things fall into place
• Having those non-Eureka moments went things fall apart
• When I’m stumped and need some clear thinking time
• When I’m absolutely stumped and am driving myself up the wall trying to find an elusive missing link

2. The client is virtually lying to me time

So I’ve done a lot of the premilinary database design work and I am about to get to building it when I think about the stuff that is going to break the system.

I will ask the client for example “Does the list of products ever change ?”. The answer from the client is “Absolutely not – it is a definitive product list”.

And half way through the project build it transpires that there is an extra set of optional products which does change. Hence in my database design I need to try and work out how this will happen,

So this tranche of my time is devoted to seeing what information the client thinks is not important to telling me, but in fact is fundamental.

3. Creating the Database Tables

So now get down to the fundamentals and start designing / building the underlying tables. Obviously there are the obvious tables such as customers and orders. However there can be some esoteric tables such as user names or even price history, where each time the price changes on a product it needs to be recorded. Some of my database solutions have over 25 tables.

4. Database Components

Having worked out the database tables, then I start to think about the components. So for example a customer will have the first name, last name, mobile number, email address etc. Most tables will have a minimum of 20 components. However in more sophisticated systems I have had over 300 components.

5. Relationships

This is where the fun can really start as I start thinking about how the various components can link up to each other. So ensuring that there is a relationship between the customer and all his orders. This will extend as each order will have order components that will feed from a master product list. Fortunately Filemaker makes it comparatively easy to connect up tables up using their visual interface.

6. The Data

So now I can go back to the data that the customer supplied me with and import it. You can bet that it will be in exactly the required format… In fact here are some of the things can go wrong:-

I will have asked for the data in a spreadsheet but it will be supplied as Word
Even if it is a spreadsheet, it will be formatted e.g instead of seperate columns for First and last name, they will be as one column and i will ned to split them out.
The dates will be in a non standard format e.g 12.07.2010 rather than 12/7/2010.

7. The User Interface

Naturally every customer wants an easy to use interface that is simple to follow. The things that they fail to realise are:-

• It is not a single interface – it is several interfaces (or in Filemaker Pro layouts) that interact with each other. All of these need to be individually created.
• Further it is never the first version of the layout that looks good – but rather each one needs several versions as I refine them continuously
• And none of the layouts are stand alone, if I change one, it might force me to make change on several others
• Having designed all the layouts, the client will want their own branding which will mean revising all the layouts

8. Making the database actually do something

Yes the database needs to do a few things. In Filemaker (and in other systems) this is doing by writing scripts. So there can be several scripts each doing a bit of the overall functionality

Add a new customer / order /product etc
Produce a report
Email a report

However as well as writing a script, it needs to be tested. So I test as I go along, and then when it is complete. Further you need to ensure that the script is idiot proof – what happens if the end user does not supply all the data that is required for the script. This means that there has to be comprehensive testing of the script.

9. Security

At its simplest level this means having seperate log on details for each user. However, it can often mean a lot more – in particular allowing certain parts of the system to be restricted. For example only certain users can view pricing data.

10. Testing

This is the most misunderstood aspect of the system design. Here are some examples of the testing that needs to be undertaken:-

The most important test is that I have understood the user requirement thoroughly. The majority of my clients have practically no systems experience other than facebook/linkedin usage. So I need to demonstrate that I have done this by regularly meeting with the client and show them progress. This gives both sides a chance to give feedback.
The client needs to undertake their own testing to check that they are happy with the system functionality.
However as part of my own role, I need to test from the ground up – look at every button / layout and script and ensure that they behave in the correct manner. This has to be done both at the microscopic and macroscopic level – the output from one part of the system needs to feed into another.