Introduction to Custom Database Systems

Database systems provide organizations with a central repository of information that can be easily searched or used to generate reports. There are a huge number of technologies used to implement database systems – from Access to server-based systems like SQL Server and Oracle.

MS Access is often the starting point for many database systems in smaller companies or workgroups within larger organizations – and it can work well for smaller systems. But Access isn’t really suited for situations where the data is very important, or where it needs to be shared over the internet.

Server based database systems like MS SQL Server, MySQL and Oracle use a dedicated server to hold the database. This makes the whole system faster and provides effective protection against data corruption, as it allows the data to be easily made redundant and backed up.

Although the database server software is at the heart of the system, the process of designing a new database system has more to do with understanding the requirements and then creating the forms and reports required to enter the data and retrieve it.

Digital Inventum has all the skills required to deliver a custom database system for your organization:

Why use a Database Server?

Many smaller organizations are used to the way Access works – the database file (an MDB) is placed on a shared drive, and all users can access it via Access forms and reports. By comparison, the server-based approach seems complex and generally needs support from the IT department. As a result, we’re often asked exactly what a benefit using a server brings.

There are three main reasons why server-based database systems are better than the shared file approach:

• Data Integrity Systems like Access provide relatively little protection for the data. If one of the programs writing into the Access database crashes – or is powered off at the wrong time, the Access file can be corrupted. By comparison, server-based database systems have built-in protection against such corruption – it’s built into the design of the database.
• Performance Shared-file databases are much slower than server-based systems, because each user is directly reading the whole data file over the LAN. With a server-based system, the user’s program sends a query to the server, which then computes and returns the answer instead of pushing large chunks of data over the network.
• Ease of Maintenance Systems like Access are simply not designed for larger projects – and maintenance of Access DBs can be very time-consuming. As more requirements are added to the Access-based solution, the complexity becomes increasingly harder to track and maintain. In addition, Access developers may implement their own solution to a commonly solved problem, whereas they could have used a standard solution in SQL. It therefore becomes easier to train new developers on the SQL based solution as they only have to learn the design of the databases as opposed to the design in addition to any quirks arising from custom Access code.

Database Server Technologies

There are a huge number of database systems on the market, but most attention focuses on the top three systems:

• MS SQL Server - Microsoft’s flagship database product – available in a number of editions ranging from free to expensive.
• Oracle - The major competitor to Microsoft – and in some ways the original database. Oracle is generally used for larger systems, because most commercial customers tend to go with Microsoft SQL Server.
• MySQL - An open source (free) product that has established itself as the de-facto standard for Linux applications

Digital Inventum has used all three of these products on live projects, but we generally prefer to use MS SQL Server – and we recommend this to customers due to its ease of configuration and debugging thanks to the bundled tools, in particular SQL Server Management Studio. MySQL has only recently become a serious product – earlier versions omitted data security features that we thought were essential. Oracle is generally seen as more expensive than Microsoft – and most of our customers are far more familiar with MS SQL Server.

Why Use a Database Server?

The big advantage of the server approach is speed: all the heavy work happens on the same machine that actually holds the data file, so it's much faster. The second advantage is reliability: the database server software is designed to protect the database against crashes and power failures.

There are obviously many different types of database server software - IBM, Oracle, Microsoft, and SyBase are some of the leading names. Most of these are aimed at the larger end of the market, and have matching price-tags. Users looking to upsize from Access are normally at the lower end of the market - the following list shows the options preferred by Digital Inventum :

Microsoft SQL Server

Microsoft SQL Server is the market leader for small to mid-size database applications - and it's not just because it's Microsoft. The product has a good collection of features, and is easier to use than many of the competitors. However, SQL Server is still a complex piece of software that needs a minor expert to configure, Microsoft Data Engine/SQL Server Express

MSDE was Microsoft's recommended upgrade route from Access for companies with five users or less: it's actually a cut-down version of SQL Server that is effectively free - developers are allowed to give it away with solutions they create. It has some limitations compared to the full version, however support for it officially ended in 2008 and users are instead encouraged to use SQL Server Express.

MySQL

MySQL is the alternative solution: it's an open-source database that is basically free, yet is a completely viable competitor to mainstream products. It should be stressed that MySQL is a tried-and-tested product, with over three million users, and test reports show it to be faster than many competitors. The product may not have all the bells-and-whistles of the big commercial products, but it's definitely a route worth considering. MySQL commercial editions start at $20,000 per license, although the community edition can be used gratis. £2000 per server, there are no per-user charges, so it's ideal for larger users or people wanting to connect a server to the web.

Database Maintenance Database Corruption & Repair

Without a database server, each copy of Access works directly on the database file. If there is some interruption while it's working - such as a crash - then the program might not have written all the changes it was working on. The result is a database that contains errors, and cannot necessarily be opened. This is described in the Microsoft Knowledge base - The likelihood of a database becoming corrupt depends on a number of factors: the number of users, the type of updates and the chance of an interruption. It can also depend on how the software writing to the database has been structured.

Why Doesn't Microsoft Fix This?

Cynics would say that Microsoft benefits from this problem, as users are more likely to upgrade to SQL Server. This is almost certainly an exaggeration - the corruption problem is probably fundamental to the design of Access, and is unlikely to be fixed easily.

However, this is not to say that the situation hasn't improved: Access 2000 certainly introduced features to help with data integrity, and this product is definitely better than Access 97.

Repairing Databases

Repairing Access databases can be difficult. Although Microsoft provide tools for compact and repair, these often fail to work. There are programs on the market that can help, but we have found that there is no single tool that will repair all types of database corruption. We individually assess each database and its problems before establishing the best route to repair and we find that this approach delivers much better results. We are also more capable than the repair tools of identifying potential record losses and therefore preventing loss of data.

If you are having problems with your database, we can repair it and help you to minimize the possibility of data corruption in the future.

Data-Only Upsizing

Many companies have an Access database with forms that work well, but with poor reliability or performance. In these cases, the solution is to move the data only to a server, leaving the existing Access forms alone. Access supports this through a feature known as 'linked tables' - each table in the database is copied to the server, and the local table is replaced with a link to the new table.

Before this can happen, a suitable database server must be chosen and installed. Many companies opt for MS SQL server simply because it's from Microsoft, and this should mean compatibility with Access. There are, however, many other options, and some of these are considerably cheaper

Bear in mind that data upsizing is rarely just a matter of copying some tables and setting up links in Access - there's usually some extra work to be done. For example, although Access and SQL server both use the same language (SQL), they actually speak slightly different versions of it. So it's often necessary to examine the project carefully to check for incompatibilities - often there will be some small problem with tables or queries. Access also allows the developer to use Visual Basic code, and this needs extra checking and testing to ensure server compatibility.

Front End Upsizing

Front-end upsizing rarely provides a solution to performance problems because forms and reports aren't performance critical. Instead, there are two main reasons for front-end upsizing. The first is to move to a better programming environment - many systems begin and evolve on an Access database but, as user requirements, scope of use and complexity of functionality increase over time, they eventually outgrow it. The second is to allow the database to be used on the Internet or Intranet, a requirement that is increasing dramatically as remote working becomes more common.

The Access programming environment, whilst great for small projects, is generally regarded as unsuitable for larger projects: the VB language supported by Access is quite cut-down, and not hugely flexible. Companies that want a friendlier user interface, interactive graphics or integration with other systems generally need to look elsewhere. In past years, the default choice has been to move to Visual Basic, although there were other choices - Borland's Delphi for example. Recently, this has changed as Microsoft have released Visual Studio .NET, with a dramatically improved Visual Basic, and a new language called C#. This platform, although very new, is undoubtedly the best route forward for many applications.

Publishing a Database on the Web

Planning a Web / Database Project Since the Internet took off, companies have been finding new uses for the web browser: many internal systems are now delivered through internal websites (or Intranets). The reasons for this take-up are pretty simple: First, users find web applications much easier to use. Second, web technology doesn't require any software to be installed on users' computers, cutting rollout and PC upgrade costs.

These benefits mean that many companies are looking at their database systems and considering how they can web-enable them to extend their reach or improve usability. However, before starting such a project, there are two key questions to ask:

Is the data to be available internally (i.e. on an Intranet) or externally (i.e. on the Internet)? Does the company just want to show selected information, or convert the whole system to web-based operation? The answers to these questions dictate, broadly speaking, how large and complex the project will be.

Publishing a Database on a Web Site

In principle, displaying data on a web page isn't difficult - most current web servers can read data from an Access database easily enough. However, there is one big problem: the database needs to be accessible to the web server - which normally means both must be on the same network. This is no problem if the web site is an internal (Intranet) site - but it's much harder to do if the web site is hosted with an external Internet Service Provider - as is the case with most small to medium sized companies.

There are a number of solutions to this problem:

• Host the website with a company that also offers database hosting. This solves the basic problem, but the database can't easily be used inside the company, which probably isn't acceptable.
• Get a dedicated connection to the internet. This has recently become a lot more affordable with the arrival of ADSL, but it's still quite technically complex. And ADSL won't support large amounts of traffic.
• Find some way of synchronizing the database in the office with a second copy at the web server. This is technically challenging, but is the best low-cost solution to the problem.
• Publishing Selected Information

For many companies, it would be useful just to be able to present a summary or subset of the database information on an internal web page. This is quite easy to achieve: the company installs a web server such as MS Internet Information Server (built-in to Windows 2000) or Apache, and writes scripts that read the data and display it as web pages. Note: the Access database engine isn't suitable for applications where high reliability or performance is required. In these cases, it's advisable to consider moving to a database server.

Data-Only Upsizing

At present, there is no automatic method of converting an Access application to web-based operation - the system effectively needs to be re-implemented using a web server. However, in practice it can be a lot simpler and less expensive than this suggests. First, the programmers creating the new system have a solid specification to work from, in the form of the original system. Second, some parts of this process are reusable, so programmers undertaking this type of project on a repeated basis can accelerate the process. Also, Microsoft have recently introduced a new web server technology, called ASP.NET. This system makes the creation of database-driven websites many times easier than previous technologies.