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

  • A database for tracking students
  • A database for an estate agent
  • A database for  managing the flow of test tubes through a forensic laboratory

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.

ICL Student marks details

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.

ICL Student Info screen

The student details screen shows essential information about a given student, allows free entry of notes and displays a photo of the student

ICL Course results analysis

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

ICL Final Degree awards screen

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

ICL database - the Course Moderation screen

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 ICL Database - the Examinations centre

The Examinations Centre provides numerous tools for creating mark sheets, reports, analysis and alerts on the data received

Row of houses

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

The visual display of key information is a critical part of this database. Here, the number of valuations, and the number of actual "instructions to sell" is compared on a month by month basis

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 fees and income achieved is visible on a 12 month rolling basis within the database.

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

Property details screens

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.

A summary of the new business done within a specified time range is available within the database

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.

Managing the "lookup lists" of information within the database

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.

Estate agents database - exchange details screen

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.

Test tubes being analysed in a forensic laboratory

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.

The workflow management screen

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.

Received tubes and exposure details

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.

Customer and order details

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.

The library of test tubes

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.