What exactly is a database?
A database is simply a collection of related information that is stored in an organized manner. Your Christmas card list is a database, as is your address book. A database does not have to be electronic; an old-fashioned card file is a database as is a collection of recipes torn out of the newspaper.
I’ve heard of Access, is that a database?
Yes, Access is a database. In fact it is an electronic database and further, it is a relational database management system, or RDBMS, and is produced by Microsoft. It runs on various versions of the Windows operating system.
RDBMS…………….. ummmmmmmm?
Yes, it is a bit of a mouthful. RDBMS stands for Relational Database Management System.
DB – The DB part; this just means database as described above!
MS – The MS bit, as in Management System, implies an electronic system. A database Management System is, as it sounds, a system for managing and controlling a database. It would normally include functions to create tables, add data to the tables, extract data from the tables, and display the extracted data either on screen or on paper.
R – Right, the Relational bit. Getting trickier here! A relational database is a database that is designed according to the Relational Model.
Err, that sounds a bit circular to me! What exactly is the ‘Relational Model’?
The Relational Model was originally conceived in 1969 by a chap called Dr Edgar Codd and is based upon mathematical predicate and set theory. It reached more or less it’s current form in 1985 with the publication by Codd of a set of 12 rules (Codd’s rules). Much clarification and expansion of the relational model has since been done by Chris Date. A full explanation is beyond the scope of this site but suffice it to say that the Relational Model has proved to be the most popular, successful and practical method of organizing complex data into a database and of subsequently retrieving that information.
Why can’t I just use a spreadsheet instead?
Well, maybe you can….. within certain limits!
(I knew there’d be a catch………..)
A spreadsheet is fine as a simple database but you can only take it so far before it’s limitations become more and more obvious. In a relational database the data storage (tables) are separated from the data display (forms). Further, the two can be linked together by queries allowing you to extract and combine data from multiple tables. This separation gives enormous benefits, making the whole system much more powerful, flexible and maintainable.
Okay, you’ve mentioned Access, aren’t there any other relational databases?
Sure there are. Dbase, FoxPro and Paradox to name but a few. However in our opinion Access is certainly one of the best workgroup RDBMS (yes, we already covered this acronym earlier on) on the market.
So what about these other databases like DB2, Oracle and SQL Server that you haven’t mentioned so far?
These are Client/Server databases designed to be run over LANs (Local Area Networks), WANs (Wide Area Networks) or the Internet and are intended for industrial strength use. An insurance company would use a Client/Server database to hold it’s customers records, it would not (or should I say, ‘should not’!) use a workgroup database such as Access.
So can’t I use a Client/Server database instead?
Maybe you should! It all depends upon what you are going to do with it! Client/Server databases are usually much more sophisticated than a straight Access/JET database (JET is the native database engine that comes with Access). Consequently they tend to be more expensive to purchase, develop for and to support. You may also need someone in-house with some knowledge of databases, or have an expert that you can call on if you run into problems (that would be us then….), which is less necessary with a straight Access based system.
As a rough guide if you have over 50,000 records in your database, or want to have more than ten simultaneous users, or require remote, wireless or WAN connections or are considering a browser based application, or if the database is essential to the operation of your business, then you should consider a Client/Server solution.
From your description I need a Client/Server system, what do you recommend?
Personally we recommend SQL Server, but then again we may be biased since this is one of our main area of specialization! SQL Server should also integrate better into a Microsoft environment (eg linking up with Excel, Word, Outlook, Sharepoint, etc.) than the alternatives. To be fair Oracle is a very well-established system with a large user base and proven scalability. Another alternative is DB2 which also has an excellent pedigree, is very stable and, in its latest incarnation, is much more friendly and easier to use than earlier versions.
In practice the decision on which server to use is likely to be decided as much on political reasons than on technical ones. If you have in-house expertise in DB2 and already have several DB2 system running then your best option is probably to use DB2 for any new project. On the other hand if you have no existing database server in use then the decision is more open. Certainly in the value for money stakes SQL Server usually wins – especially if you take advantage of all the free extras thrown in with SQL Server such as Reporting Services, Analysis Services, Integration Services, etc.
In addition we need to decide on a front end to use with your client/server database. One possibility is to use the Access front end; which can work reasonably well in a simple LAN (Local Area Network) application. However for most applications we tend to favour VB.NET for Windows environments and ASP.NET for Internet applications as these are much more powerful, more flexible and more future-proof.
So tell me about SQL Server.
The current version of SQL Server is version 2005 (which replaced SQL Server 2000 in November 2005). This runs on all current versions of Windows. SQL Server includes many bolt-on extras (depending upon version) including a reporting tool, an OLAP (On-Line Analytical Processing) tool, a scheduling tool and a data import/export tool. All of these extend SQL Servers capabilities significantly beyond that of a simple database engine.
A limited version of SQL Server is available and this is also bundled with the more comprehensive versions of Microsoft Office 2000. This is referred to as MSDE (Microsoft Data Engine) in its SQL2000 guise and SQL Server Express Edition in it’s SQL2005 flavour. Note that MSDE/Express lacks many of the more advanced functions hence it’s main use in laptop deployments and small-scale server implementations. See also our page comparing SQL Server with Accesswhich explains the benefits of the client/server architecture.
Right, so what should I do if I don’t currently need Client/Server but may do in the future?
If you may require a client/server solution within the next five years then go for that option now. For longer timeframes the look at having your database developed in Access but make sure that it has been designed with future client/server use in mind. The first thing is to have the data part (the back-end equivalent) split off into a separate physical database container/.mdb file to the code (the client equivalent). A good Access developer should do this as a matter of course (but unfortunately a very large percentage of Access developers fall below this standard!). A major design issue is to think Client/Server during development. This generally means NOT retrieving all the data for a table but to retrieve only those records that are really required, preferably only a single record at a time. Other things to watch are making sure that techniques specific to Access, and which would cause problems with a client/server based version, are either not used or are suitably commented. One very simple example is to make sure that all table and field names do not contain characters, such as punctuation, that is legal in Access but is illegal in SQL Server. Again, any decent Access developer should do this instinctively but, judging from the many Access systems we have examined over the years, the majority of database developers do not take even these simple precautions!
When in the future you need to move over to a Client/Server based system you just migrate your Access tables (structure and data), business rules and data integrity rules across to the Server and carry on using your previous front end/client. This is a simplification but the bottom line is that you do not need to write a new program from scratch. At a rough guess it may cost you around 20%-50% of the original development cost to have your Access database converted into a client/server version, provided that it was designed with this in mind in the first place. Not cheap but much less so than having the system rewritten from scratch.
I need to make my database available over the Internet.
Databases can be accessed over the Internet or over an internal company Intranet. This can be a two way process where existing data can be retrieved from a database and new data can be added to it; in both cases using nothing more than a standard browser (eg Internet Explorer or Netscape Navigator). For low volume work an Access .mdb may suffice although it is not really robust enough for such environments and for most scenarios a client/server database such asSQL Server is a pre-requisite.
I know I need a database but I don’t know how to define what I want?
Join the club! Many of our users have trouble with this unless they have had professional training. This topic’s a bit wordy to include on this page so click here to jump to a specific page for this. Also click here for another page which describes what you need to include in your Terms of Reference to make sure that you get a professionally developed database solution.
What To Do Next…
If you would like some further advise then please contact us and we can either have an initial discussion on the phone or we can arrange a meeting.