Choosing & Managing The Right Database Program

Organization is the key to a successful business. An effective database program can organize schedules and inventory, sort and analyze data and strategically arrange customer information for promotions and mailings. My goal with this installment of Art Circuits will be to offer you some guidelines on what a database program is. My hope is that this will help you to evaluate and choose the right database program that will address your current needs and be flexible enough to help you project for the future.

“Database” and “Program” The two words go together so well! But in truth, they are two very different things, each of which has its many strengths and weaknesses. This is definitely a case where the value of the whole is far greater than the sum if its parts. It is the marriage of the two, database and program, that has, literally, changed our world. And believe me, a “Database Program” can change your world also!

Let’s talk about Databases and Programs. We’ll begin with databases, since a program doesn’t do much unless the underlying database is there. A database is a grouping of related information. It can be as small as a list of email addresses, or large enough to include every piece of inventory, customer, and sales data for a gallery operation spanning multiple locations with multiple computers in each location.

For these large databases, the information is usually broken up into TABLES that hold different types of information. For example, you could have one database that holds both inventory and customers. But these two pieces of the puzzle would be in two different tables.

Each table includes a ROW for each piece of information. So, each customer in your contact table would have it’s own row in that table. Breaking it down further, each row in the table can be broken down into individual COLUMNS. So, for a contact, the first name may be in one column, and the last name in another.

The entire bundle of all of your columns, rows, tables, as well as the rules that connect them together, make up your database. There are several database products that help you to group these tables, rows, and columns into some manageable order. But not all are created equal.

At the bottom of the database food chain are products like Excel, WordPerfect, and Outlook. Yes, Excel, WordPerfect, and Outlook.

“But, Excel is a spreadsheet “, you say.

“That depends on how you use it”, I reply.

If you use excel to total a list of numbers, or compute the amortization schedule on your new Beemer, it may be working as a spreadsheet. However, if you just use Excel to enter a list of inventory, than you’re not really using Excel as a “program”, you’re using it as a place to store your data. A database. And, while Excel has a lot of power, this is not an application that really takes advantage of its power.

The same is true of WordPerfect or Outlook. “But one’s a word processor, and they other is an email program”, you say. “Those aren’t databases.”

To which I would reply, “But if you use them to store your mailing names, then you’re using them to consolidate and store data. That’s what a database does.”

Outlook is a great program. I use it all day every day. But, I don’t use it to store all my customer contact and marketing information. So what else is there? Well, next up the database food chain are products like Access, or FilemakerPro.

These products have good structures, and come with the ability for you or someone else to write a “program” that accesses and manages the data that is in the database. I’m going to talk about programs later in this article.

These two databases are great for the home or small business user that wants to do their own thing. For example, if you want to set up your own database to track your inventory, you can do this pretty easily with Access or Filemaker. You map out your own tables, including the columns that make up the tables, and then the tables are linked together in the database.

A third type of database, called FoxPro, can also be used very easily in this capacity, to set up your own structure, but it’s a little more sophisticated, and faster, than Access or FileMaker, at least in my opinion. FoxPro tables are made in a similar manner to the dBase tables you may have heard about. Microsoft actually bought out the FoxPro company several years ago. At the time, rumors were flying around that they wanted to kill off FoxPro and use the FoxPro search technology, which gives FoxPro it’s speed, for the Access database. Thankfully, there are enough geeks like myself all over the world that still use FoxPro, so they haven’t been able to kill off the FoxPro product. It’s alive and well and it remains, in my opinion, the premier database programs for small to medium needs. The next generation, FoxPro 8.0, is due out any day now.

Each of the three products above can also act as “Relational Databases.”

A Relational Database is one in which several parts of the database RELATE to each other. Suppose that you have a list of artists. Each one of the artists can have several pieces of art in your gallery. Each artist can also have several addresses, or phone numbers. In a relational database, we don’t have to repeat the information that stays the same when we enter all the records that are different.

We do it like this.

When we add the artist, he or she is assigned a number – in this case, say the number is 2007. It’s not important what the actual number is – it has NO significance in the real world. This number is called it a “primary key.” The number must be unique to that artist and, depending on the PROGRAM we use, we may not even have to know what the number actually is. The program will take care of that for us.

After assigning a number, we continue and add the artist’s first name, last name, company name, tax id, commission rate, etc.

Now, when we start entering inventory into a new table, we COULD enter the artist’s first name and last name into a field called “artistname” in the inventory table. But we could also just enter the NUMBER for that artist into the table as an “ARTISTID”. Why is one better to use a reference, rather than repeating the information in more than one place? There are a couple of reasons. One is to save space, the other is accuracy.

How many characters are in an artist’s name? Well, we’ve found that we have to allow for about 50 characters, even if most artist names are much shorter. Add the artist’s company name, and you get at least another 50 characters. If we have a table with 10000 items, then if we have an “artistname” field for each record, and a “companyname” as well, it requires space for 1,000,000 characters [10,000 x (50 + 50)] in that table to handle the artist names and companies, even if these fields are EMPTY. That doesn’t even include the artist’s address, tax id and other information.

Now, if we use a NUMBER to RELATE the artist record to the inventory line, we only use between 4 and 10 characters. Suppose the piece of inventory we are working on is for artist number 2007. We can easily look up the name, address, company, taxid, etc for artist 2007. In fact, our PROGRAM might look that information up for us automatically and display the artist information on a screen along with the inventory information.

Ok, that saves space. But what about accuracy? If you have the artist name on each line of inventory, you probably use “cut and paste” to copy information from one line to another. That is, as you enter inventory for Ansel Adams, you might copy that name down the list instead of retyping it for each piece. But what if your office assistant is entering inventory for you? And they enter the name as “ANSEL ADAMMS” instead? And what if they did this for 375 prints? And what if they also screwed up 6 other artist names?

WITHOUT a relational database, you would have to update each of those records and correct all of the mistakes. But WITH a relational database, you only have ONE artist record for Ansel Adams. The inventory just has the artist’s NUMBER there. So, you go in and you change ANSEL ADAMMS to ANSEL ADAMS and you have instantly changed the inventory for EVERY piece that RELATES to that artist.

Now, just because a system CAN be set up in a RELATIONAL way, doesn’t mean it is. It is up to the database designer to make sure that everything is cleaned up. I’ve translated data from lots of other gallery management programs and I still can’t believe how many designers that use Access or FilemakerPro fail to use the relational aspects of these products properly.

Failing to use the relationship features can actually be dangerous, because you can have the same information in more than one place. Here’s an example. Suppose you have a mailing list record. It would probably have addresses and phone numbers in it. Now, suppose you also have a sales record. During the sale, a customer might give you their address and phone number, yes? If you were designing a new database, it might be tempting to put the customer address and phone information directly into the SALES table. This is easier to program than relating the sales table to the contact table. But it would be a BIG NO NO. Why?

Well, suppose the customer calls in a year, and informs you of their new address. You would naturally go to the contact record and update their address. Would you naturally go to the sales record and update that address? Well, 6 months later, you may want to print a mailing to everyone that you SOLD an Ansel Adams piece to. Since you have names and addresses in the sales record, you decide to pull the addresses from there. What do you get? THE OLD ADDRESS! If the sales record didn’t have an address, but it REFERRED TO the contact table instead, than you would automatically get the CORRECT address.

Before I talk about programs, I also want to mention the top of the food chain, so far as databases are concerned. If you have lots of data, or several computers, the databases above can start to slow down. Access and Filemaker are really bad at moving lots of data over a network, but even FoxPro will begin to slow if there is too much data to cope with. The problem isn’t actually with the database itself, or the program that is accessing the data. The problem is with the network design and how the data gets from the SERVER to the WORKSTATION. In this environment, you want to use a product like ORACLE or SQL SERVER( That's pronounced "SEQUAL SERVER" ). The databases above usually run in a PEER TO PEER mode, whereas ORICLE and SQL run in a CLIENT/SERVER mode.

Yes, I’m going to tell you what that means.

First, Oracle is a very large, very fast, and very expensive database. It’s used for airlines, insurance companies, and search engines, that have more data to manage than you can even imagine. It’s not a consideration for anyone I know in the art industry. Besides, according to Bill Gates, SQL SERVER (a Microsoft product) is better and faster than Oracle anyway. I don’t know if that’s really true, but I do know that SQL is certainly more than adequate for our needs.

SQL Server is a product that is still used for LARGE databases, but is reasonable enough in price for a mere mortal to think about using. Some galleries that have multiple locations, or thousands of records (clients, pieces of inventory, etc.) from several years worth of data, may need to use SQL to manage the data.

MSDE, which stands for MicroSoft Data Engine, is a scaled down version of SQL SERVER but it is optimized for 5 connections or less. Think of it as "SQL Lite". It is used in businesses that have lots of data, and several computers, but do not run multiple locations in real time.
Why is Client/Server better than Peer-To-Peer ?
On a normal (peer to peer) network, each computer is doing its own work, even if all your data is on a single computer that you call your "server".
When you're on the main computer and want to bring up one name from your mailing list, it's easy. The data is on the same hard drive and that computer reads the data and gives you the name you are looking for.
Now, on a "client" or "workstation" computer, the data isn't actually on the same hard disk, or on the same machine. The data may be across the room, or even across the country! If the workstation needs to bring up one name from the mailing list, it has to get THE WHOLE MAILING LIST from the server and pick the name it wants. How does it get the data across the room, or the country? Via your network.
This design causes a lot of traffic on your network. Each time the workstation wants to pull up a name, or a piece of inventory, or anything, it has to bring all the information it needs, like your entire mailing list, off the server, then pull out just the piece it wants.
To make matters worse, it doesn't just bring the mailing list over one time. Each time you do anything, like click the NEXT button to go to the next record, the workstation has to bring the whole mailing list again and pull out the next name.
If you click NEXT, NEXT, NEXT you actually get your whole mailing list three times over the network wire.
Now, the wire that connects the two computers is very slow (relatively speaking) so the more information that has to go over the wire, the slower the system. (WIRELESS networks are even slower than WIRED networks.) If your network happens to include a dial-up modem that links you from one gallery to your business office where your server is, forget about transferring peer-to-peer data over THAT connection.
Also, each workstation you add increases the traffic on the wire even more.
What's the solution? Client/Server.
With SQL (or MSDE), you have a program running on your server that "listens" to all the workstations. Since this program is on the same computer as the hard disk, it doesn't have to use that slow wire to talk to the data.
Each time a workstation want to pull up one name from your mailing list, or one item from you inventory, it sends a request to the server. The server checks its hard disk and finds the right information and sends back just that bit of information to the workstation.
This type of system can cut the traffic on your network down dramatically. Instead of passing, say, 10,000 names over your network, you just have one request and one answer.
This option cuts the network traffic down so much that you can use it over the internet, or even phone lines, to connect multiple locations. So if you have a Gallery in Santa Fe, and a Server in Scottsdale, the Santa Fe computer (the client) sends a tiny request over the wire to Scottsdale (the server). The computer in Scottsdale, running SQL server, hears the request, finds the answer on its own hard disk, and sends back the answer.
I guess I’ve talked enough about databases. Hopefully, this gives you a pretty good idea what goes on with a DATABASE. If you are going to “do-it-yourself” you will probably want to look into Access or Filemaker. If you want to purchase a system, go for a system that uses FoxPro or SQL. This will let you expand in the future.

So, what about the PROGRAMS that work with your database?

I mentioned above that a program isn’t any use without a database. But a database can be next to useless if you don’t have a good program to access it. Using a spreadsheet to manage your data is just one step above using index cards. Why? Because it’s the program that takes care of all the little details associated with keeping a database accurate. It can also be the link between you and the data.

If you have set up your own database with something like Excel, then you probably don’t use a program at all to change the data. That is, if you want to go in and change a piece of inventory, you open the spreadsheet, scan down the list until you find the piece, and then you change it. If you want to print a report of all the pieces that sold this month, you might sort the spreadsheet into some order, create a printout, and send it to the printer.

This system, while better than a paper system, is still very cumbersome and time consuming. Wouldn’t it be nice if you could click a button and a PROGRAM could pull up all of this month’s sales, or pay your artist consignments, or update your web site, or send out emails, or compute your sales taxes, or print mailing labels, or display your digital images, or or or or … woops, got carried away there.

The fact is, no matter how good your database is, it is the PROGRAM that determines whether you will be able to use that data on a day-to-day basis. You may also hear a program referred to as a “front end” (where the database itself is called the “back end”) or a GUI ( pronounced GOOEY and stands for Graphical User Interface. )

Some programs come in modules. Modules can be good or bad, depending on how and why they are created. The concept of program modules is a good thing. It lets a developer create different parts of a program that may be used by different people. If you’re never going to do framing, why have a framing module built into the system?

However, some developers, create modules so that they can make the “base price” of the software low, but still charge people more for the different features that they may need. This can make it appear that a program is less expensive, when in actuality the cost of all the parts you need could work out to be more than some other program. That’s bad enough, but it’s not even my biggest gripe about modules.

My biggest issue with programmers that use modules is that these “program parts” are often afterthoughts that are tacked onto the main program to perform some function, when that function wasn’t originally designed into the program. If done properly, this is a valid way to respond to customer needs and requests. The problem with tacking on new modules is that often the new module doesn’t connect to the main program as seamlessly as if the particular feature the module adds were designed into the program in the first place. By that I mean that, while the module is SUPPOSED to access and write to the same data, sometimes a module has a hard time opening and closing files, or passing information to and from the main program.

Ok, what should you look for in a program?

Well, certainly, you want it to track your customers. This is what helps you with your marketing.

Tracking customers makes you money.

You can use a contact database to print mailing labels, or create a merge file to merge into an invitation document, or create and send emails.

Some things I believe are import in managing contacts include; 1) multiple address and phone fields – not just mailing and shipping, but office, summer, winter, fax, toll free, mobile, etc. 2) recording email AND web site – especially if you use your contact manager to track artists and vendors, 3) Lots of notes areas – I don’t programs that have those check boxes like “Hot Prospect”, “ArtExpo Client”, etc. The check boxes clutter up the screen and no matter how many you have, you always need more. Instead, I like searchable text fields where I can enter all the notes I want and then search for them later. I can add “Hot Prospect” to the notes if I like, as well as “ArtExpo Client” and then find all the contacts that have these words in their notes. Check boxes should be reserved for things like “client” or “artist” only. 4) The contact manager should immediately display all of a client’s Purchases and Payments without any extra work on my part. 5) I like to have a place where I can enter reminders, or action items, like that I need to follow up on a client request.

OK, you have clients under control.

Next, you certainly want the program to track your inventory. This, with the possible exception of managing staff, is probably your biggest headache. How on earth do you keep track of what’s owned, and what’s consigned? What if you have a piece that is consigned, but you paid to have it framed? That would mean that the piece on the wall is part yours, and part not yours! How do you know which pieces, or artists, are your best sellers? A good inventory program will answer all of these questions with just a few clicks of your mouse. Most inventory systems are pretty similar, and include common information like title, subject, etc.

However, if you’re going to take the time to enter in the title, subject, medium, dimensions, price and artist for a piece, along with maybe a few digital images, why would you want to enter that information again to add it to your web site? Even worse, why would you want to pay some web designer to enter that information for you? These people can charge $50 to $100 per HOUR.

So I believe that any inventory program worth looking at will be able to update your web site in the blink of an eye. And it should not only put information UP to your site, it should be able to remove a piece when it’s sold or no longer available.

Some of the specific things I think should be included in the inventory side of the program are; 1) the ability to store digital images – preferably several, as sometimes one picture isn’t enough. 2) Drop down menus wherever possible to select common information, like mediums, subjects, etc. - How many ways can your staff spell Lithograph? 3) Barcode labels, price cards, inventory lists, certificates of authenticity, and lots of other reports are important. 4) If a piece is sold, the system should be able to instantly tell me when, for how much, and to whom it was sold. 5) it should be able to synchronize your data with your web site, not just throw information up to the web site. 6) The input screens should be SIMPLE, not full of buttons and graphics that clutter up the page 7) Notes – as with the contact page above, I would much rather have a searchable notes area, that I can type anything I want into, instead of lots of annoying check boxes.

OK. Now you have Contacts and Inventory covered. So, how does a program link the contacts that purchase a piece to the inventory they are buying?

A gallery or frame shop is a BUSINESS. You have to SELL stuff. Otherwise, you’re just a museum with “lots of lovely pieces, thank you very much.” Well, along with selling, comes paperwork. Of course it’s important for the system to automatically link the pieces on the transaction to the client that is purchasing the pieces. But the system also has to compute sales taxes, track receipts for cash, visa, travelers’ checks, and the dreaded layaway – for which it should send out statements. What if the customer wants to split the purchase over several credit cards?

If you have a do-it-yourself database, like a spreadsheet, you may have to actually go into your inventory list and enter the name of the client that purchased it. That can be tedious and time consuming. But if your program has a point of sale system, then it will take care of connecting the inventory to the clients. In short, you want the program to have a POS system.

This POS aspect of the system should also be able to be linked to your web site. We all agree that you should have a web site to display your inventory. It takes the place of the old printed brochure or catalog. But let’s face it, as of today, very few galleries make any substantial sales from their web sites. Don’t get caught thinking that this will always be the case! I believe the day is coming when instead of having an “in store” business and an “online” business, you’ll just have “your business”. When this day comes, you will have customers going online, selecting pieces to purchase, and entering their payment and shipping information on the web site.

Why would you want to manually enter that information again into your POS system? If your system can read this information from your web site, you have less data entry, and you cut down on mistakes. I agree it’s still too soon for this to be something everybody needs, but keep this aspect in the back of your mind when selecting software.

Things to look for in a POS system are 1) it should be easy enough to do a cash sale in seconds, but powerful enough to handle any combination of items and payments we can throw at it. 2) it should automatically link the customer to the pieces being purchased 3) it should figure sales taxes and payment schedules 4) it should remove the items being sold from inventory, but not from the system, so that we can generate consignments and staff commissions, or look up what a client purchased three years ago. 5) it should be able to work with POS hardware like cash drawers, receipt printers, or barcode scanners, but should not require these items in order to run.

Here’s something to look for in the software in general: A balance of ease and power. You can make software really simple by taking out all the features! Or, you can add features until the software requires an engineering degree to operate. Look for something in the middle, where the common screens, like adding inventory and making a sale are a piece of cake. You want to be able to show a new staff member how to make a sale in about a minute. But also look for a system that is deep enough for you to customize your own reports, or print detailed mailing lists, or generate a purchase order for a co-venture between you and three other investors on that Picasso piece you’re going to sell.

I guess that wraps up my rambling about databases and programs. I hope this article helps you find what you need, whether that’s a do-it-yourself spreadsheet, or an enterprise wide solution to handle multiple computers in multiple gallery locations. Do you have any questions? Send me an email. Or, if you’re going to be at ArtExpo NY this year, I’ll be talking about this very topic in one of the free seminars. I hope to see you there!

************************************************************************
James Archer has been a gallery owner himself, and developed The Masterpiece Gallery Manager, a software system designed specifically for the art and related industries. Visit the web site at www.masterpiecesolutions.com
***********************************************************************