Custom Database Design and Development

A service for small and medium sized enterprises requiring a database created to fit the way they run their business and to get the maximum advantage out of the information stored in the database.

The strictures on travel forced by Covid-19 have lead to emails, conference calls and secure, remote access being widely adopted as efficient ways of addressing issues relating to new or existing databases. To such an extent that I now work for clients worldwide.

Last updated by David Wallis on 2022-02-13.

Database Design Goals

These are aims I have in mind when designing a database for a client:

• To make the database reflect business practices
• The database to provide significant advantages over any existing systems it’s to supersede
• Ease of use. By way of its on-screen displays, provide users with straightforward and efficient ways to input and manage data
• Comprehensive reporting to provide full benefit from the effort gathering and inputting data
• Reporting that identifies missing or incorrect data.

A data modelling exercise completed before committing to a new or revised database will save you money during the development stages and also later when the database gets into full use.

Database Development – Return on Investment

Weighed against design and development costs, returns on investment should accrue from the use of the database over its lifetime. I work closely with clients to make certain that aiming for such returns is central to the delivery of a satisfactory database.

By focussing on true needs, database development costs are kept from inflating.

Care needs to be taken to distinguish needs from wishes, which, lacking scrutiny, may sound like good ideas, but unfortunately lead to inclusion in a database of “features” that never get used and get in the way of efficient use and upkeep of the database.

Experience across scores of business sectors and business applications means I’ve much to contribute to determining the best approach to take for a new database.

Long conversations with some potential clients points to pressure on them to consider cloud-based solutions without them having at their fingertips the facts about what such solutions require and about the cost implications for adopting them.

Certainly I can help you avoid the sorts of costly mistakes that are being made in the specification, commissioning, development and usage of relational databases.

Microsoft Access and SQL Server

Microsoft Access is included in a number of Microsoft 365 packages, including Microsoft 365 Family and Microsoft 365 Personal. Chances are that Access will remain for the foreseeable future as important as it has been up to now.

From designing and developing Microsoft Access databases since Access first arrived in the UK in 1992, I claim I’ve gained the experience to achieving for you a fully-fledged business tool for use by everyone in the office.

Access is good software for databases that are used “locally”, e.g. on a local area network (LAN), in an office.

I divide any Access database into two main components. These make it ready for many simultaneous users, even if the initial plan is for a single user only:

• The back end (BE) A single file comprising of a suite of interlinked tables that store the data input into the database

In a multi-user database the BE file resides on a network server.

• The front end (FE) A file, a copy of which each user has on their computer. It’s an app providing the user with links to the data in the BE. This app — often called the user interface (UI) — is how the user works the database.

Some medium sized businesses like to have FEs customised for different departments. Hence Accounts, Marketing, Production and Management FEs, possibly. Yet all linked to the same data in the BE.

As a starting point for any new databases, I use Access to create both FE and BE. This is a cost-effective way of producing a prototype database and a fully-fledged test bed.

Sometimes it’s judged a benefit to beef up the BE and place the tables in structured query language (SQL) database.

Working for some clients, I’ve combined Access FEs with Microsoft SQL Server BE's. This has proved an effective pairing.

The Cloud – Remote Working

The pandemic has accelerated the demand for staff to be able to use their business databases from wherever they’re working. Home use has become a major issue.

… We want to run our database from the cloud … We want our database to be web based … It needs to work on laptops and tablets … From home and on site … On smart phones …

Access, the software, was never intended to support databases in the cloud. That’s not to say that a workable solutions for people working from their home offices can’t be found.

For example, you could set up a remote desktop, in which you connect to a computer in the office when working from home or other locations. Using your mouse and keyboard, you operate the office computer as if you’re sitting at it.

On your screen you see the other computer’s screen as it responds to your actions. The office computer is doing all the processing. What passes back and forth the internet are your mouse and keyboard actions and, in return, images of office computer’s screen as it responds.

Another technology is virtual desktop, in which all your software and applications, including your databases, are on servers in the cloud. You use your computer from anywhere and log into your virtual desktop when you need to work on anything hosted there.

Virtual desktop costs need to be carefully considered before making any decision on which company, or companies, you engage to provide the service. You need to make contingencies for your provider failing as a business, for faults occurring in their servers, and any other factors that could interfere with the smoothness and responsiveness of your desktops.

Both remote desktop and virtual desktop rely on your connection to the cloud. If that connection goes down, then you’re without your database. If you’re experiencing breakup or interruptions in communications during lockdown, then this will be the pattern if you go to the cloud with your business applications.

Where I sit, the cloud is not reliable enough for me to entrust it entirely with DMW Consultancy’s key business applications.

For assistance in finding solutions that suit you, please call +44 (0)1732 833085 or email david@consultdmw.com to discuss the possibilities.

Bespoke Database Designs

These are some of the databases developed for DMW Consultancy clients:

This list is not complete, but hopefully it provides a guide to the scope of my work and an indicator of the experience that I’ll bring to any future project.

Access Database Conversion

As you convert your databases from versions 95, 97, 2000, 2002 (XP), 2003, 2007, 2010, 2013 and 2016 to Microsoft 365, you’re faced with the question as to whether or not to refine or extend them as part of the conversion project.

I provide a conversion service and consultancy on refining and extending databases across all versions of Access.

There’s more about some conversion paths on these pages:

• Converting Microsoft Access Databases
• Converting Microsoft Access 95 and 97 Databases
• Converting Microsoft Access 2003 Databases
• Converting Microsoft Access 2007 Databases.

Contact DMW Consultancy

Where visits might be necessary, based near Tonbridge, I’m is well situated for clients within the M25, London, Kent, Surrey, East Sussex and West Sussex. Beyond the south east, I’ll travel to anywhere in the UK where my particular skills could be of value.

To make initial contact, please call +44 (0)1732 833085 or email david@consultdmw.com.