The Dummies Guide to Database Design

I was reflecting on the number of times that I have been given the prompt “Well I will give you the data and it can’t be that much work to build a database”. In my mind (apart from the indignant inner rage that this causes) this is like a plumber turning 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 key points that I actually go through to build a proper 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 practically nothing about my clients business. Given the diversity of my clients (I’m currently working for two cleaning firms, a charity, a bakery) I need to get to grips with their business problem. So the first stage of the system design is always thinking time. I’m oscillating  between:-

• Asking lots of questions (which of course the client either loves or loathes). You’d be amazed how many times asking a simple question can provoke such consternation  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 completely stumped and am driving myself up the wall trying to find an elusive missing link

2. The client is effectively lying to me time So I’ve worked out what the database is going to do and how it will work and I am about to get to work 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 master 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. Setting up the Database Tables  So now get down to the nitty gritty 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 required tables, then I start to think about the components. So for example a customer will have the first name, last name, phone number, email address etc. Most tables will have a minimum of 20 components. However in more sophisticated systems I have had over 200 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 come in 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 designed.
• 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 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 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.

About the Author

Kapil Kapur is the Managing Director of Fingertips Intelligence a company who helps their clients make better decisions faster. If you would like more information abuut how we can help you then please drop us a line on info@fingertips-intelligence.co.uk