Just as no two spreadsheets are the same, no two databases are the same.
But by taking a look at some of the projects we've worked on, you can get a feel for what's possible.
What does good database design look like?
Take a look at some examples of databases we've built
Whether it's tracking music or students, sales or training courses, all of our databases begin life in the same way.
A meeting with the client, at which we ask a basic question:
In an ideal world, what would your database do for you?
Don't think about your existing database. Think about what you'd like if absolutely anything were possible. We can always say "No - that's not viable" - but sometimes you'd be surprised what can be done.
Three examples of our work
One of the world's top universities, and a complex set of requirements
When Imperial College London came to us to ask about building a database, it was clear that nothing "off the shelf" would ever work for them. The needs of the department for Chemical Engineering were so bespoke, so variable, so full of exceptions and variations that we knew we needed to create a hugely flexible solution.
Create a database that will track
- Exam questions
- Degree programmes
- Aggregate marks per year
- Study abroad requirements
- ....and many other elements
Not only do these things need to be recorded, but a huge range of additional complexities soon became evident.
Each course can feature both exams and coursework - and the exams might have higher (or lower) weighting than the coursework in determining whether a student passes that course or not.
Each course can make a different contribution towards the outcome for the year - courses can be "Must Pass", or completely optional. Some courses might count as 20% of the total marks for the year, whereas others might only count 5%. And the same course might count differently for students doing different degrees.
Each year also counts differently - the first year counts less than the second, which counts less than the third, and so on. But some years don't count in that way at all - such as when a student takes a year to work in industry.
A student might be exempted from a course as a one-off - or asked to take the exam later in the year.
Reports have to be created in highly specific formats for governing bodies - and these formats change year by year.
Yes, this was - and is - a technically challenging project.
But the truth of it is that the success of the project is as much about the relationship as it is about the database.
It's about having a relationship where the client knows they can call us and say "We're in the middle of the exams season and the rules have just changed" and know that we'll move heaven and earth to make things work for the new requirement at very short notice.
It's about them trusting that we'll keep working until we get it right - and be honest with them when we're struggling to understand the latest rule change or requirement. Often, it turns out that we're not alone in battling to "get it" - and we work things out together.
It's why, when we built this database for the Department of Chemical Engineering, they then recommended us to the Department of Mechanical Engineering. And the Department of Aeronautics. And the Department of Materials Science. And the department of Mathematics. It's why we've become a trusted partner for the College, building huge databases and working on small macros to make Excel tasks easier, providing development and training services for over a decade.
What it looks like
We're a little limited in what we can show, as so much of the data is confidential, but here are some sample screens from the Exams database to give you an idea of what we've created. Click any image to view it full-size.
Ever bought or sold a house? Imagine doing it dozens of times per month...
Okay, so that's what Estate Agents (or Realtors, as they're known in many parts of the world) do for a living. It's what they're paid for. But imagine how you'd feel if they got the details wrong about your house. Or forgot how much it's on sale for. Or when you listed it.
That's why data is critical to an estate agent. And when a top London estate agent came to us to ask for a system to suit their specific needs, we were delighted to help out.
There are numerous variables involved in selling a property, and the database we were tasked with building had to track them all. It's about managing all aspects of the sale from the business perspective, so we were asked to design and build a database which could look after:
- Sale dates
- Contract exchange information
- Staff and negotiators
- Multiple sales per property
- Financial records
- Multiple offices
- Cancelled sales
- Monthly financial reporting
- Customer acquisition sources
The key to the success of this project was to get a deep understanding of exactly what role the database would play in the client's business.
This wasn't going to be a database to hold customer-facing information. There would be no property descriptions, no photos of exquisitely decorated rooms and no logs of viewings. Rather, this is a business tool, and would answer business-critical questions:
- What's the average sale price per square foot of property space?
- How many sales are there per month per office?
- How many sales are aborted per month, and what is this as a percentage of the whole?
- How many sales per month does each negotiator achieve?
- What's the commission due to each negotiator?
Understanding the way financials are calculated in particular was a challenging task, and one to which we dedicated a significant amount of time. And making this information easily visible, with RAG (red / amber / green) statuses being clearly shown for key information, was a central part of the requirement
What it looks like
An independent forensic laboratory, where accuracy is everything
Gradko International is an independent forensic laboratory, based in Winchester. We've worked with them for over a decade now, starting our relationship by running some highly customised training on database design.
It may not come as a surprise to learn that with any business in this field, there are myriad regulations to follow, everything has to be audited on a regular basis, and accuracy is critical - and must be provable.
Every test tube must be tracked - from order to dispatch, from receipt back into the lab to analysis, from documentation of the results to the information being provided to the client.
The database must provide information relating to:
- Quotes provided
- Orders received
- Standing orders for a set number of tubes per month
- Single-use test tubes
- Long-term use test tubes
- Dispatch of tubes
- Receipt of tubes
- Analysis of the tube contents
- Reconditioning of tubes
- Use of laboratory equipment
- Checking and double-checking of results
It must be possible to tell where exactly any test tube currently is, and where it has been since it was originally created. Who has handled it? Where was it sent? What were the analysis requirements? When was it received back into the lab? Who received it? Who analysed it? Which lab machine was used to analyse it? What were the analysis results? Who double-checked those results? Where were the results sent, by whom, to whom and when? Where is the PDF of the results stored within the file system?
Additionally, from a business perspective, it's imperative to see where there are backlogs, such as tubes requiring re-conditioning, or sent out but not received.
The database we've built does this and more.
We've worked very closely with Gradko to produce this database - adjusting it as the business requirements have changed.
We worked with the lab staff to produce a system capable of capturing results output by GCMS analysis machines and recording them in a format required by customers. We worked with the despatch department to produce a barcode-reading system that exactly matches their requirements, leading to the highest possible throughput of tubes - a critical issue when thousands of tubes may be sent and received each day. And we worked with the management team to ensure that they have a clear overview of what's happening from a workflow perspective, with automated alerts when bottlenecks are discovered.
What it looks like
Again, confidentiality prevents detailed display of some of the screens we've created for this client, but the examples below should give a good flavour of what was achieved.