Over 46,000+ Business Solution Developers Find answers, ask questions, and connect with our community of business solutions developers, business owners and partners.
Here is some FileMaker Database Design Food for Thought from Kapil Kapur of Fingertips Intelligence. 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 hard to build a database”. In my mind (apart from the indignant rage that this causes) this is equvalent to 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 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 practically nothing about my clients business. Given the diversity of my clients (I’m currently working for a building firn, an architect, and IFA) 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 simple 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 effectively lying to me time
So I’ve done a lot of the premilinary database design work is going to do 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 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 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 200 components.
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 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 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 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