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.
The challenge
Create a database that will track
- Students
- Courses
- Exams
- Coursework
- Lecturers
- Exam questions
- Marks
- Absences
- 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.
The Solution
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.
The student marks screen shows an at-a-glance view of the performance history of any student across all the exams they've taken in their time at the University.
The student details screen shows essential information about a given student, allows free entry of notes and displays a photo of the student
The course marks analysis screen shows a visual representation of marks for any course, and allows comparison with previous years to check for consistency in standards and marking
The degree awards screen shows marks achieved by students in each year of their degree programme, and calculates the degree class to be awarded to each student, allowing the staff to adjust this if necessary
The course mark moderation tool shows whether the marks received fall within the expected range, and provides automated recommendations if marks require adjustments across a specified set of students
The Examinations Centre provides numerous tools for creating mark sheets, reports, analysis and alerts on the data received
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.
The problem
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:
- Properties
- Sale dates
- Contract exchange information
- Pricing
- Commissions
- Staff and negotiators
- Multiple sales per property
- Financial records
- Multiple offices
- Cancelled sales
- Monthly financial reporting
- Customer acquisition sources
The Solution
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
One of the key figures required by the Estate agents management team is the number of valuations conducted and the number of instructions received from prospect - and the fact that this is displayed in a very visual format makes this information highly accessible and makes trends easy to spot
The sales and income chart is available to compare data over a rolling 12 month period on an office by office basis. This not only shows seasonal variation, but grants an easy view of how the current year's performance compares to previous periods
Numerous sales details are available for any transaction - the various dates involved, the staff who led the sale and their respective percentage of the resultant commission, the source of the vendor and the purchaser, and any previous or subsequent sales for this property are also shown.
The New Business Summary screen shows management information about the number of sales, the fees and selected aggregate information. This is available for any specified period, for one office or across the business as a whole.
It's important that our clients don't need to come to us for every adjustment they need to make. We make sure that this client - like all our clients - is able to manage their "lookup lists" as they see fit. This information becomes available in drop-down boxes, in filters and throughout the database. Items can, where appropriate, be marked as inactive and re-activated again later, for example in the case of staff who may take a leave of absence but return later.
The Exchange Details screen shows key financial data regarding individual properties, and allows both drilling down to further information and exporting to Excel the key information required by others without access to the database. Items which remain unpaid are highlighted in red so that this information can't be missed.
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.
The challenge
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:
- Customers
- 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.
The Solution
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.
Within the database, this screen provides a list of incoming tubes, along with a note of the forensic analysis required for each tube. This allows supervisory staff to see what's required over the next few days, and assign work to the staff with the appropriate skills and capacity.
On this screen, a set of tubes received from a client can be seen. Detailed information is logged, including the exposure information for capturing of air particles. Tubes are recorded against specific locations so that a map of pollution levels can be provided for this client.
Gradko use the database to track customers and their orders. Here, they can see exactly what has been ordered - both physical products ( a specific set of test tubes) and related services (provide this type of analysis on these tubes). Orders are highlighted if the customer has paid for a fast-track service, or has overdue invoices which need to be paid before a further service can be provided.
Information is available about every tube - where it is, what type of tube it is, how many other tubes are available of that type, how many times it has been used, how long before it will need to undergo reconditioning and more.
