Under the hood of every successful website there is invariably a database or more likely a whole cluster of them. It is quite literally the driving force in website architecture, and probably the most important component in managing and delivering content efficiently to the pages of your website. So whether your website is a Mini or a Hummer its time to talk DB’s.
BUT (and its a big but), the learning curve can be steep particularly for the first time database user - there’s nothing quite as mind boggling as the complexities of Database topology. And there is no getting away from them – if you want your website to perform well and be able to manage content yourself, it obviously makes sense to have some rudimentary understanding of what is going on in the background.
But where to start? If you have a Mac you may have already come across Filemaker – a user friendly but quite powerful WYSIWYG relational Database, while the unfortunate Windows sufferer has to be content with MS Access – although once you have a rudimentary understanding of how Access is supposed to work you can certainly see its appeal as a file based ‘lite’ application.
As a website backend DB, MS Access is also relatively easy to implement and integrate. Updates and backups can be carried out by dragging and dropping the mdb file on to your hosted web space via FTP (obviously in a secure folder off the web). It can even be administered using the local copy of Access on your machine. In fact I have deployed a number of eCommerce sites using Access and they have worked surprisingly well, as long as the website does not get too popular (no more than a few concurrent users at a time please!).
Unfortunately however that’s where the plaudits end. As the size of the database increases, as it invariably will do, Access performance starts to degrade significantly . So life on the web can be short lived for our little desktop application. There are many other reasons, including the limitations on concurrent users, security issues, etc why MS Access is not a good idea, more of which are listed
here.
Certainly for non Windows web database requirements Access doesn’t get a look in. On Unix based servers, MySQL has proved without doubt the most popular and now the most widespread:
- It’s Open Source (free-ly available, financially speaking)
- it’s proven reliability has resulted in widespread implementation.
- it’s scalability (don’t underestimate this one - it is running YouTube after all)
- its ongoing support and development structure
Many hosting companies, even those running Windows based servers, don’t encourage or are reluctant to support use of Access, and generally advocate using MSSQL as a windows based alternative – which makes lots of sense as again MS Access can still act as the local ‘front end’ for the web database admin tasks (a unique moment of Microsoft joined up thinking).
So which is best?
Horses for courses here I reckon – arguably the choice can be narrowed to a few main contenders -of the Open Source applications MySQL and PostgreSQL are the first choice for Linux servers; while MSSQL might be the obvious way to go if your website is on a Windows server. However the level of online support and documentation may be the reason why increasing numbers are opting for MySQL even on Windows servers.(also see ‘and finally’ below )
Migrating to MySQL
On one occasion I was told that one of my Access DB websites was being removed immediately from the shared server because it was causing performance problems for all other sites on the server (MS Access was identified as the culprit) – which prompted me to migrate fairly quickly to MySQL. however this turned out in practice not to be so quick -it certainly wasn’t a straight swap. Again OS tools came to the rescue to make the process easier, although there were one or two coughs and splutters before the website DB ‘engine’ came back to life
Eureka moment
Having a ‘free’ and scalable database architecture just makes sense - from the new web venture through to the monolithic sites like YouTube.
The lack of a front end WYSIWYG can be a bit daunting for the novice though, and tackling MySQL through a command prompt may not make for Rapid Application Deployment, particularly if you are not familiar with SQL syntax – is the lack of it MySQL ’s achilles heel? Not at all, there are shed loads of excellent web based Open Source and proprietary interface tools for MySQL such as MySQLAdmin, MySQLyog, Heidi etc etc which will do the job effectively. In any case for most of the day to day website requirements much of the transactional activity will be hidden behind a well designed admin ‘backend’ .
And finally…
…there is some good news for Die hard Access users also – you can now use it for the front end admin for your MySQL database through So I was surprised recently to find out that the MySQL guys have launched
Further info at:
see also limits on Access databases at:
http://blogs.msdn.com/access/archive/2006/06/05/access-2007-limits.aspx
Facebook DB architecture:
http://glinden.blogspot.com/2008/05/scaling-facebooks-databases.html
Development of youtube architecture vid:
Tags: MS Access, MySQL
This entry was posted
on Thursday, March 11th, 2010 at 10:54 am and is filed under Main, Web.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.
