A Programmer's View on Database Software
You have kept your customer information on paper, in spreadsheets, in your accounting program and now you want to set up a central database for all your customer information. As you evaluate your options, you face questions like:
I am a programmer of twenty years helping businesses to set up their customer databases. Having answered these questions many times, I wrote this article to help small business operators navigate around available options in the database world.
The first question about the database you should consider is:
What Information to Track?
This varies dramatically, but there are several frequently used types of business information:
- Contact details
- Customer preferences
- Communications history
- Sales history
Then there is business-specific customer information. For example, family-oriented organizations track family members, real estate companies - properties, engineering firms - equipment they maintain on behalf of the customers and so on.
How to Organize the Information?
How all this information can be organized for an easy access and use? Should it fit into a single system or multiple systems? How can I fit my data into the database structure?
Single vs Multiple Systems
Using a single database minimizes the hassle of synchronizing multiple sources of data and improves data accuracy.
However, if your business software must incorporate industry-specific logic, you are unlikely to find an integrated system that includes both that logic and a full-featured customer database. In that case, the best option is to use your industry-specific software as a primary source of information and copy the customer records from that software into the customer database, where you add more information to them.
As much as possible avoid synchronizing the two systems both ways - there should be just one master customer database.
Customizing databases to fit your information
Now that you decided what information goes into your database and where it comes from, consider how this data can fit into the database.
The simplest method of customising a database is creating extra fields in the customer record. Many database programs make it easy and so you can accommodate business-specific information by adding more and more fields.
When you have hundreds of fields, the system becomes difficult to manage. This is probably because you have put together information about different objects. Re-structuring your data makes it easier to access and manipulate. There are two methods of re-structuring such data:
- Splitting customer records into related record types
- Storing some information in external files
Splitting customer records into related record types:
Suppose we need to record basic family information:
- Family name
- Husband's name and birthday
- Wife's name and birthday
- Child 1 name and birthday
- Child 2 name and birthday
- Child 3 name and birthday
If you were to add all these details to your customer record, they will occupy eleven fields. Finding a John Smith will be difficult - you need to search four times - for John as the husband, Child 1, Child 2 and Child 3. And the system won't accommodate families with four or more children.
To handle this information, the databases can store it in different record types and relate these records to one another. Because of these ability these databases are called relational (as opposed to flat) databases. In a relational database, you would have family and person information in different records and link persons' records to their family record.
In our example, each personal record will then have three fields: name, birthday and the link to the family record. John Smith can be easily found with one search, no matter who he is in the family. The difference in ease of use is even more apparent when you need to store more information, like insurance policies with number, name of the insurer, montly premium, expiration date etc.
To see if a piece of customer information should be tracked in a separate record type check whether a customer can have more than one of it. Can a customer have multiple policies? family members? air conditioners? Can a customer be included into multiple lists? If the answer is yes, then those objects should be tracked separately and linked to the customer records. If no, they can be incorporated into the customer record.
Storing some information in external files.
There is a natural desire to track all customer information on the database, but is it really required? There are some data types that databases don't handle well, for example, spreadsheets. The databases are designed to work with large number of similar records, while in a spreadsheet each row could be different from the previous one. Spreadsheets' formulas can be easily modified, while in a databases they are programmed.
So if you are used to tracking some customers information in spreadsheets or some other external files (photos, text documents etc), perhaps it is worthwhile to continue doing so, linking those files to the customer records. You will keep your database simple and agile and retain the familiar ways of working with these files, but can still easily access all customer information from a central location.
The down side is that you cannot easily search on or otherwise process the information that is stored externally. If you store customer borrowing power calculations in Excel spreadsheets, you can easily access this data on any customer, but cannot find customers who can borrow in a specified range without going through each and every record.
Back to top
Which Database Software to Use?
Many people consider one or more of the following choices:
- Microsoft Access
- Popular contact managers, such as ACT or Goldmine
- Accounting program
- Employing a software developer
- A package that is both simple and easily customizable, such as Simply Contacts Database.
Perhaps one of the first programs people turn to when it comes to building customer database is Microsoft Access, probably the single most popular database program. Most already have it as part of Microsoft Office Professional or Premium, making it an easy choice. The system comes with Wizards assisting in setting up new databases, creating tables and basic forms.
However, beyond the basic setup, you are on your own. To add a field, you need to add it first to the database, then to all the forms and reports where you need it. Maintaining lists of values for drop-down lists is not a trivial task. As the number of fields grows, maintaining forms layout quickly becomes a chore.
Access is a great system in the hands of a professional, but developing a functional customer database in it usually requires more expertise and time than a business person can afford.
Popular Contact Managers
Many people have heard about ACT and Goldmine. Unlike Access, which is a generic database tool, these programs are business applications designed with customer management in mind. They offer rich contact management functionality out of the box and can be extended by adding extra fields, reports or buying add-on modules.
These are capable systems, but their primary target audience is salespeople. According to the vendor's site, ACT is designed for "individuals and organizations involved in selling and other functions
where management of contact details, activities and communications is
critical." From customizability point of view, ACT is similar to Access. Yes, you can add a field to the program, but you have to do it at least twice - in the database and on-screen - and in any reports if you wish to see it.
The program's functionality suits a busy salesperson whose day is filled with appointments, pursuing leads, writing proposals, closing sales and so on. But for many other users it is simply excessive.
The main purpose of a customer database is to improve service and relationships with your customers. The main purpose of accounting software is to balance your books. Customer database deals with the client side of your business and does not go beyond order fulfillment. Accounting software deals equally with the sale and supply sides. You should track prospects on your customer database, but a prospect is of no interest to accounting. Customer database software includes information specific to your business; accounting software incorporates national tax laws. Even though some accounting programs include CRM modules, they lack the flexibility to record the business-specific information.
In my view, customer database and accounting are different areas and don't mix well in one package.
Employing a Software Developer
Use a software developer if your needs are radically different from other companies. Developers can build a system highly tailored to your business but you need to know very well what you want to accomplish and relate this to a developer in a clear and concise brief.
Very often business people describe what they need as a "simple program". But as the project unfolds, it turns out the software should do more or handle special cases that were not initially planned. This causes disputes, cost increases and frustration on both sides.
When you are just starting and don't have the full knowledge of what you need, a better way is to experiment and fine-tune the database over time. Starting with a simple, easily customizable package, such as Simply Contacts Database, where you can add extra information in minutes, gives you an opportunity to learn and test your system before investing more time and money.
Simply Contacts Database
Then there are programs like Simply Contacts Database. Out of the box, it includes basic contact information, customer history, notes and can attach external files, such as text documents, spreadsheets or pictures to the customer records or history. It can print mailing labels and do mail merge and e-mail merge.
But the best thing about this software is its flexibility and expandability. There is a simple function to add extra or remove unwanted fields that automatically adjusts all standard screens and reports. On top of this, users can define their own screens and reports. For example, if you want to track both customers and suppliers on your database you can create "customer view" and "supplier view" with different fields to view the respective records, yet display full information in the "contact view".
With a program like Simply Contacts Database, you can start with the data that you already have and easily add more fields as the need arises, perfecting the understanding of the business requirements for your customer database. Once you outgrow the basic system, you can upgrade using the extensive modules library offered by the vendor, or go for an industry-specific or a custom system.
Back to top
How Will it Work with Other Software We Use?
Most modern software packages can exchange data in standard formats, so there is no need to re-enter the data if you already have it in an accounting or a point-of-sale system. There are two main ways to keep the systems synchronized:
- Export+Import: Periodically export the data from one system and import it into another.
- Interface Module: a special program or a module that transfers the information automatically.
- Open Database Connectivity: programmatic access by one program into the database of another.
Export+Import is the most universal and popular method of copying the data, but may require some effort to set up properly. There are two types of problems you may experience:
- Fields in one system may not correspond to the other one.
For example, address in the accounting program may have separate fields for line 1 and line 2, while the customer database may have one long field instead. The easiest method to get around it is to have two address fields on the customer database as well.
- Duplication of records
In order to be able to change data in one system and automatically propagate these changes to the other, you need to export the entire customer database, not just the new records. When you load the data into your customer database programs, this may create duplicates.
- Maintaining link between customers and sales records.
To link a customer to multiple purchases, most systems extract customers and sales information in two different files. When the data is imported into the customer database system, this link breaks down. Some customer databases can import both files while maintaining the link between them. For example, in Simply Contacts Database the user can nominate a group of fields as "unique identifier" of a customer record. This allows the program to automatically find the appropriate customer record when loading sales data and correctly link sales to customers.
Interface Module between the system is easier to set up but is only available between the limited number of programs. It already knows about the database structures of the two systems and ensures correct data transfer. Most mainstream contact managers such as ACT or Goldmine offer interface modules to Outlook and popular accounting programs like Quickbooks or MYOB. Simply Contacts Database includes interface module for Outlook.
Open Database Connectivity (ODBC) is a technical standard that allows one system programmatically access and/or modify the database of another one.
Accounting software Quicken and MYOB make their databases accessible via ODBC. Microsoft Access and systems based on it can both make their database available via ODBC and import data from other ODBC-compliant systems. Operation of ODBC-based connections is fairly easy as they eliminate extra steps required to export and then import a file.
The downside is that setting up an ODBC-based link between two systems typically requires programmer's expertise. Thus Open Database Connectivity is often used in the pre-programmed interface modules.
Back to top
How Much Will it Cost?
Back to top
- Most custom-built database programs for small businesses cost between $1,000 and $5,000. Apart from functionality, the factors that affect the cost include:
- clarity and completeness of your requirements
- whether the system is web-based or desktop
- whether it is a multi-user or a single-user system
- the number of records it must manage, ie hundreds vs hundreds of thousands
- If you want to build a customer database program yourself in Microsoft Access, you would either need to have a retail copy of Access (around $200) or Office Professional on each computer or purchase Microsoft Visual Studio Tools for Office (around $800) for your development machine. Your time costs will be probably much more than the software.
- ACT for individuals and small teams retails for around $230 a copy
- Simply Contacts Database retails for around $140 a copy for a basic version. With all the add-ons, it can cost up to $1000 per copy, but you can pick and choose only the modules that you want.
- The cost of industry-specific software including customer databases very dramatically from industry to industry. Sometimes these packages are cheaper, and sometimes they are more expensive than a custom-built program.