Relational database design services
Most of the websites we develop are bespoke database driven web applications, so database design has always been a big part of our job. All of our web development clients benefit from our years of expertise in this field; we also offer a standalone consultancy service, so get in touch with us at our Brighton & Hove office if you just need a beautiful database model designing for your project.
Getting the database model right
The first step in designing a great database for your web application is requirements analysis - in other words, finding out what's needed, and working out the best way to deliver a system that fulfils those needs.
Designing the database will then take place as part of an overall system design phase.
Getting the data-model right is always an iterative process - an initial draft data model will be sketched out by one team member and then passed around for peer review. Issues that arise from the database design process will feed into the overall system design; queries that get answered here may throw up questions that need to be addressed elsewhere.
Cans of worms and other wriggly creatures
Some aspects of the model will seem simple at first - for example, it might seem obvious that an individual's name should be stored as a two short text strings, forename and surname. But how should a middle name or initial be stored? Should we have a field for 'title'? Should a title field have a controlled vocabulary? Should the title field be 'normalised' (see below)?
There's not always an obvious answer to these questions, and the database model that works for one project might not quite make sense on another project. Tricky questions that arise at this stage might prompt a brief trip back to the requirements analysis stage to better capture what the system does and doesn't need to do.
To normalise, or not to normalise...
One aspect of data model design which always needs to be considered is whether (or specifically, to what extent) to 'normalise' data. Data normalisation is essentially the removal or reduction of duplication. For example - rather than store a piece of text for a person's title ('Mr', 'Mrs', 'Dr', etc) you might store a number that refers to another table which contains an exhaustive list of all the possible titles. That's just one type of normalisation - check out the Wikipedia link in the side column for a more detailed discussion.
Normalising data is generally a good thing - after all, it reduces duplication of data - but it will mean more complex queries are required to pull out useful combinations of data. Sometimes a combination of normalised tables and denormalised 'caching' columns can be used to make life easier while keeping the master data in a nicely normalised form.
Thinking ahead
A great data model will cater neatly for all the current requirements of the system, and a few future requirements as well.
But not too many - trying to second guess all the possible future requirements of a system is a recipe for unnecessary complexity!
Often times, the best strategy is to put a bit of thought into how the system might evolve in the longer term, consider what data structures that might require, and then come up with a tight, finely tuned data model for the current requirements that won't be too painful to enhance if and when those future extensions are needed.
We hope the discussion on this page has given you an initial flavour of our approach to real-world database design for web applications. If you'd like to talk to us about your database modelling requirements, do get in touch with us. We'll give you a no-nonsense great value fixed-price quote and you'll be thrilled with the results of our work.
Further reading
Summary of the technologies we use: Java, Rails and PHP
Our MySQL query optimisation service
Summary of our hosting, support & maintenance services
Resources on the web
Database normalisation at Wikipedia