Best customer database for a small business?

November 5, 2006 4:06 PM

Need to figure out the best kind of database for a small but growing business. MS Access isn't cutting it anymore. Of course there's

First off, any recommendations for particular database programs for small businesses or general advice on where to go to find out about database options for small businesses would be appreciated.

The particulars: we're small but we're getting thousands of new (and returning) customers every year, so it can't be too lightweight. Also, if a new program won't allow us to transfer our old database information over from Access, it's no good to us. We get orders over the phone and on our website. We currently have two separate customer databases- MS Access for the phone orders and another on our website, which we'd like to integrate if at all possible.

I know I'm not giving too much information, but it would be tedious- not to mention inappropriate- to explain everything here. General information on what I need to know, where to learn it, and what sort of options are out there would be most helpful.

posted by TayBridge to work & money (32 comments total)

Filemaker?
posted by chillmost at 4:15 PM on November 5, 2006

RDBMSes:

MySQL: Free. Fast, but non-complaint and doesn't scale well.

MS SQL Server: Not free. Essentially Sybase rebranded. Great GUI, relatively easy admin.

postgresql: Free. Very compliant, scales well. Admin is less friendly.

All will allow you to transfer your data, and your database structures, from Access.

Integrating your two existing database will be a real pian, and will cost you serious money. There's no easy way around this.

TayBridge posted "MS Access isn't cutting it anymore."

Isn't cutting it how? That'll help answer what rdbms you should upgrade to.
posted by orthogonality at 4:16 PM on November 5, 2006

Well - here is the problem. You may need more than just a database. Access is both a 'database' and a custom development/application environment.

Sure - it's easy enough to move your data & structure (columns/rows/tables) to any database technology - but how are you going to 'access' it? (Pardon the pun)

Access can be 'upsized' and use a SQL Server database (I'd recomend 2005) backend, but I don't honestly know how much that will cost you. I think there is a 'free' version, which comes with some development products (Express) - but has a limitation on it's usages.

However, if you are comfortable moving your 'user interface' and 'database' - then there are tons of both front-end (application) and back-end (database) options.

Personally, if it is a free database you are looking for, many people recomend mySQL or Postgress - but I prefer Firebird.
posted by jkaczor at 4:20 PM on November 5, 2006

We find MS Access clumsy and just not user-friendly. We need readier, uh, access to our information. One problem, for example, is that we often deal with separate branches of one company, and searching for the particular branch is cumbersome. We need much more detailed information than it currently provides, and we need to be able to get that information with relative ease. Also, it's not as stable as we'd like.

Money is not a huge issue. We are small, but we're willing to invest in something if it's highly functional and useful in the long term.

Also, although I appreciate your help, orthogonality, I don't really understand what you're talking about. I should probably be more clear- I am not any sort of computer expert. I've heard of SQL but definitely don't understand it. (I had to look up RDBMS!) So yeah, please talk to me like I'm a child.
posted by TayBridge at 4:36 PM on November 5, 2006

mysql, php, apache.

All-in-one Windows installer here. The best way to learn it is to have a couple of co-workers help you while you read through and enhance their code. Second best way is a combination of O'Rielly (pocket) guides and a couple of big online (or pdf) searchable resources.

If you don't have an IT Manager, it sounds like you should get one who can split his/her time between support and database development. You shouldn't have any problem finding one that knows php/mysql.
posted by krisjohn at 4:45 PM on November 5, 2006

Actually - it doesn't sound like you have an Access-issue 'per-se' (Access can be a problem when handling large amounts of data, many users at the same time or being used via a website (back to the many users at the same time issue)), it sounds like you have general usage/design/training problems.

(Disclaimer - I work for Microsoft - which is one of the reasons why I don't know how much SQL Server costs ;-)

Access can easily handle your needs - and with the correct choices is probably the most user-friendly system out their.

The only product that I've ever heard of that is actually as easy to use as Access is 'FileMaker' - but I think what you really need is some time spent with a consultant, resulting in a proper database design, a decent user interface and some quality reports. You will need those, regardless of which database you choose.

What I have seen often, over the course of 14 years, is that eventually someone who had a 'database' in either Access or FileMaker wants to take it to the 'next level' - and are faced with bad data, bad design that could have been easily corrected years before.
posted by jkaczor at 4:45 PM on November 5, 2006

Well, basically what jkaczor wrote "You may need more than just a database. Access is both a 'database' and a custom development/application environment."

It sounds as if you want a more useable interface to your data. Access provides both a customizable interface ("front-end") and a database where the data is actually stored ("back-end"). The RDBMSes I described are solely back-end.

From what you've described, you have two problems: the same sort of data in two different database, and a poor custom user interface.

You probably need to hire someone to create a better overall business process system for you, and to do a requirements analysis to figure out what's wanted.

For that, yeah, I'd definitely move away from Access. I'd also avoid MySQL and PHP. MySQL just doesn't scale well, and PHP tends to be cheap to write but expensive to modify and secure.

It's also possible that your business needs are pretty standard, and can be served by an off-the-shelf customer database, with or without some custom modification. We'd really need a lot more information to tell.
posted by orthogonality at 4:51 PM on November 5, 2006

jkaczor writes "What I have seen often, over the course of 14 years, is that eventually someone who had a 'database' in either Access or FileMaker wants to take it to the 'next level' - and are faced with bad data, bad design that could have been easily corrected years before."

Very very true. This is why you need to hire someone who truly understands database design, rather than try to figure it out yourself. (yes, I could probably figure out how to re-roof my house, but it'd end up ugly and leaky and take three times as long as it'd take a professional to do it.)
posted by orthogonality at 4:53 PM on November 5, 2006

Django. Plone. Or maybe an issuetracker. Or wiki.
posted by five fresh fish at 4:56 PM on November 5, 2006

As Ortho said - stay away from PHP - and just to ensure I am 'balanced' - I say stay away from classic ASP as well.

Both produce solutions which can be very expensive to keep running.

One thing - do you have a technology strategy & direction? Is it simply 'whatever works' or do you have a long-term plan? This can influence recomendations. Some people, will want to stay with Windows/Microsoft-platform solutions - while others may prefer open-source, alternative directions.

Hey - I guess, what with our recent Novell partnership I can actually recomend Novell, Mono & OpenOffice... ;-) Hey - OpenOffice has a database now, called 'Base' to compete with Access & FileMaker.
posted by jkaczor at 5:12 PM on November 5, 2006

Asking for the 'best' database is like asking for the 'best' car. It's not specific enough to let anyone answer the question.

From the sound of it, your problem may not be Access at all, but rather the fact that you don't understand databases very well. Even if you replace it, most of the complaints I'm hearing sound like 'bad design', rather than 'bad database'. If you replace the system, I think you might end up with the same problems you had before, plus new ones from switching.

It sounds like what you really need is a good consultant to come out. He'll need to spend (at least) a day or two looking at your database and how you use it, and then take (at least) another few days to rearchitect it for you. If you have a very complex setup, it could take much longer. The database is very often the core of your business, so DO NOT cheap out on the design work.

While you're doing that, you might want to upgrade your back end to SQL Server. Putting the data on SQL Server, but keeping your existing front end, will let you continue to do everything you're doing now, and should be pretty painless for data transfer. That will give you much, much better data integrity. Access' own database engine, Jet, is.... well, 'weak' comes to mind. So does 'abysmal'. But it's not too bad as a front end. SQL Server is very robust, and if you use Access to, well, access that very strong engine, it's a good pairing.

The freeware stuff is also good, but less compatible with Access as a front end, so you'll usually have to do more work integrating them. The freeware stuff in general usually requires a much higher up-front investment, but generally will cost less over the long run. There are no forced upgrades, and you have full control over every single aspect of the system. But I'm not sure that would be your best choice.

So where's the line between the two? If you have the resources to hire someone full-time, then freeware is likely to be a very good solution, because you'll have someone on hand to make it do EXACTLY what you want. If you just want someone to come out, do something, and then come back once a year and tweak a little, you'll almost certainly be better off with Access/SQL Server.

If your consultant is really into the freeware, listen to his pitch, but remember stuff that's easy in Access is often hard in freeware. Conversely, stuff past a certain level of complexity becomes outright impossible in Access. Freeware starts harder but can scale to do almost anything. Access starts very easy, scales up to medium complexity, and then hits a brick wall.

Overall, making a guess from the limited data in your question, I think the combo of a consultant, Access as a front end, and SQL Server as the engine will probably make you the happiest.
posted by Malor at 5:24 PM on November 5, 2006 [1 favorite]

Ortho, you use "scaling well" as if he's hitting millions of records.

"Thousands of customers" per year is nothing, and MySQL is perfectly capable of handling that.

In a case where you don't have, say 57 million customer records that you need to recurse to count properly, on one hard drive, I'm thinking importance should be placed on ease of use and cost to implement.

Scalability is a concern to some degree, but some applications will just never, ever get to that level.
posted by disillusioned at 5:51 PM on November 5, 2006

I think I have to agree that sticking with Microsoft will be the easiest, and probably best, solution for you. You can move to the free SQL Express which is basically the same engine as full-blown SQL Server, but with a size limitation (16 Gb database? anyone?), and without some of the extra management features. If you outgrow that, then it'll be even easier to move up to it's big brother, and if you've grown to that then you'll be ready for it.
posted by JamesMessick at 5:59 PM on November 5, 2006

Call a local computer consultant. Talk to someone with lots of experience solving problems like this for customers like you. Get someone you can trust.

Be prepared to spend the kind of money you would on any other infrastructure project.

Ignore platform evangelists and find a solution that works.
posted by mrbugsentry at 6:06 PM on November 5, 2006 [1 favorite]

Clearly, I can see that you're the kind of person that would put the computer containing the database in front of yourself...

From some one who has been to the classes for both and used both Access and Filemaker Pro, Filemaker has it hands down over Access; ease of use, short learning curve, powerful and excellent tech support. And a free trial of their most current version. Hard to beat that combination.

In Santa Clara County CA we had multiple relational data tables, some containing more than 400K records with dozens of fields for each record. Filemaker handled it all, making it quick and easy.
posted by X4ster at 6:11 PM on November 5, 2006

I know very little about databases. I can use Access. But, when the trading company I was running needed to be able to use our trading data for multiple purposes including compliance and accounting, the cheapest decision I made was the most expensive in the short run. I hired a consultant who was not paid by the hour or paid to program. She was paid a flat fee to answer the question you asked. WHat scalable database software should we use, what user friendly front-end could we use or design, should we hire an inhouse database administrator to run queries for us or what should we do? She spent enough time educating us just enough to make an informed decision knowing our risks and rewards of all our choices.

Bring in a pro to get it right now and save beaucoup headaches down the road.
posted by JohnnyGunn at 6:48 PM on November 5, 2006

The primary business issue that limits the utility of Access is the Jet database engine that Access is built upon. It's not a multi-user product, and doesn't have the capabilities for backup, recovery, or optimization that business users should look for in any business database. As a user front-end for SQL Server, I suppose it has a place, but there are far better commercial database products available.

But I wonder whether going at the problem you describe bare handed is the wisest course you could pursue. Simply moving your existing database records to a commercial level database product, along with whatever queries and forms you've developed in Access may be appear to be a significant project, but unless your customer information is integrated with your accounting systems, and unless both of those systems have connections with your order processing and fulfillment processes, your ability to grow rapidly as a business is going to be artificially limited. You may be better off spending your time and money on a developed vertically integrated system for your business, rather than an ad hoc database, which you then try to grow yourself, as most other businesses eventually do.

MySQL is probably the most widely deployed RDBMS system in the world, partly due to its legacy as a "free" database. It has come a long way in the last couple of years, and MySQL AB now offers commercial product support options, and there are tens of thousands of people world wide who are familiar with MySQL whose expertise you can draw upon for solving many business problems. Unless you are facing some incredibly unique business problem, you may find complete business systems and applications that have already been built around MySQL databases, that you can use as a template, saving you many, many hundreds of man hours of design and testing.

PostgreSQL has also been mentioned as another "free" database product, and it serves as the database and transaction engine for many, many business systems. It probably still has better transaction capabilities than MySQL, but the gap continues to close, and MySQL has a far larger user community from whom you can draw upon. So, my sense is that while PostgreSQL is a good RDBMS, more development time is going into MySQL worldwide, and more vendors and open source projects to deliver complete vertical business applications are using MySQL.

But what really matters for you, is that you find a good overall solution for your business, that won't be a "dead end" in 3 to 5 years, from either a support or development standpoint. If you look, I think you'll find candidates in both MySQL and PostgreSQL variants, and some applications packages can offer your choice of RDBMS engines.

You can also look to commercial vendors such as Microsoft, Oracle and IBM for bare RDBMS systems. But these vendors also have developed networks of organizations offering sophisticated vertical solutions built over their database products. For you, choosing to start with such a product offers a commercial roadmap for the growth of your business, with access to a broad base of expertise, and comparable case study history, that you can't get in custom built projects. Companies like Oracle, Microsoft and IBM all offer a wide range of small business systems which use their database products as the technology platform for full featured accounting systems. Then, upon the database and accounting application system base, they build complete business systems by adding additional software and database structures for customer information, sales and service/item information, order processing, warehousing, shipment, etc., on a modular basis. Each modular function can typically be added independently as needs grow, but some groups of functions are typical of certian kinds of businesses, are usually offered as packages, to make a vertical intergrated application offering. Thus, a retailer may have a system consisting of accounting (G/L, AP, AR and Reporting modules), Inventory (Item Master, Stock Location/Warehouse, Quantity), Sales (Customer Master, Sales Order Processing, Pricing) and Purchasing (Vendor Master, Purchase Order Pricing, Material Control). All the systems work together, from the same database tables to offer a consistent picture of the funcitons of the business. No data is redundant, and only people with proper authorization have the ability to change or modify information consistent with their roles in the business.

But beyond just vertically integrated application systems for your business, major commercial vendors like Oracle and IBM can pair you with appropriately sized partners in their Value Added Reseller networks, who have the staff and resources to help you convert your existing Access databases to the new systems, and get you up and running, with full system design capabilities, including hardware sizing, sourcing and setup, software installation and configuration, and training for your personnel in the use of your software, as well as ongoing support for upgrades and expansion as your needs grow.

Generally, in my experience of over 25 years as a business owner, MIS manager, project leader, programmer and user of integrated business systems, I'd say that businesses that research and select comprehensive commercial systems solutions for their business operations as soon as it is economically feasible to do so, outperform those who try to develop their own by such a wide margin, that unless the business is inherently unique, that there is no question that purchasing a commercial solution is the preferable path.

So, although you see your immediate problem as one of moving customer data to a more robust database product, you might well want to stand back a bit further, look at the larger picture, and talk to some of the major commercial vendors about a more general solution, before you spend money on building your own database system from the ground up.
posted by paulsc at 7:08 PM on November 5, 2006 [1 favorite]

"Thousands of customers" per year is nothing, and MySQL is perfectly capable of handling that.

MySQL is a great database if you're just doing selects. Once you start hammering updates and inserts, however, it gets ugly. It's still pretty ugly in handling transactions on an enterprise level. That said, it doesn't sound like the OP is at that point yet, nor anywhere near it.

PostgreSQL is an excellent, excellent DB system, though if you're accustomed to Oracle/MS stored procedures, you'll have some relearning to do. Again, doesn't sound like that's the problem here.

MS SQL Server is awesome. I hate to admit it (we're about 90% OSS at our company) but if you don't want to pay the outrageous sums Oracle requires but you still need something robust and easy to administer, MS SQL is hard to beat. For a purely OSS alternative, I'd go with PostgreSQL.

FileMaker Pro is complete and utter dogshit. The creator is--or at least was--evangelically anti-MS, such to the extend that it impossible to export your data to MS formats (even quasi-standardized formats like .MDB). On the other hand, if you want your hand held--nay, gripped to death, FileMaker Pro is the way to go.

One problem, for example, is that we often deal with separate branches of one company, and searching for the particular branch is cumbersome.

Honestly, it sounds like your problem is interface, and possibly design, not platform. Get a professional DBA consultant in there to check over you schema. Get off Access just because it's not designed to be a multiuser database server. Any of the above (with the exception of FileBreaker) will do fine.
posted by Civil_Disobedient at 7:11 PM on November 5, 2006

Some people really like Servoy. I've never used it. It's kinda like Filemaker, but more open.
posted by rbs at 7:21 PM on November 5, 2006

I can't comment on Filemaker because I've never used it, but, otherwise, I can't imagine a more user-friendly database platform than Access. As others have said, you need professional help here. Don't even think about moving to SQL Server, MySQL, etc., if you can't handle Access.

I don't mean to sound condescending, but it sounds like you're under sway of the common misunderstanding that databases are nothing more than electronic filing cabinets. I think the car analogy is a good one -- cars seem simple in the sense that there's an engine that turns the wheels, and you can use one without knowing details of how it works. But being able to drive a car or even having a rudimentary, abstract understanding of how it works doesn't qualify you make decisions about how a car should be built. Same goes for databases. They're inherently complex beasts under the hood, and you need to bring in someone who knows enough about them to do it right. Otherwise, you'll find yourself trying to keep up on the freeway in a go-kart.
posted by treepour at 7:53 PM on November 5, 2006

Well, half of you get it and half of you are totally missing the point. Recommending mySQL, SQL Server, Filemaker or any other specific product to a person who "had to look up RDBMS" is putting the cart before the horse.

The OP has a business problem, not a technology problem. Getting a consultant in to perform a needs assessment and make some recommendations should be the first step. As paulsc suggested a vertically integrated solution makes sense for a company that is not particularly tech savvy and does not have specific needs that would require a great deal of customization.
posted by MCTDavid at 7:54 PM on November 5, 2006

Uh-oh. You have opened up the holy grail of database wars. Take it from someone who has been in your shoes - growing a business and soaking in data. Data that you want to organize, sort, print, email, chart, slice and dice a million ways to Sunday. You want to be able to do this quickly, turn-around reports sub-sorted by sub-sorts that the branch manager just thought of and needs 5 minutes ago. Been there. Done that.

Filemaker Pro- and no, I don't work for them, develop for them, or know anyone in the company. Contrary to what someone said above, FM will import and export into a dozen different popular formats- including Excel, and with some middle-ware Access.

I've worked with SQL solution and Access solutions as a government drone. Filemaker has yet to let me down when it comes to importing, entering, sorting, printing, reporting, thousands and thousands of records in some quite complex databases that have been developed by myself and/or others. I strongly suggest you take it for a spin. If you have any other questions regarding it- drop me a line. I'd be glad to pass on what I have learned.
posted by bytemover at 8:14 PM on November 5, 2006

bytemover has it right! You don't need to be a database professional to jump directly into Filemaker. And yes, you can export data to MS and other formats, or import data from your existing data in Excel or other tables. You have little to lose by doing as bytemover suggests. "take it for a spin". You can download the free trail along with the DIY guide 'How to Integrate Filemaker Pro with Microsoft Office', and the free productivity kit that has .
posted by X4ster at 8:40 PM on November 5, 2006

Sorry, too fast on the Post Comment button...

FM Pro productivity kit has sample inventory management, contacts and shipping database templates. You can also get Filemaker Mobile for your PDA.
posted by X4ster at 8:47 PM on November 5, 2006

Thanks for your thoughtful responses, everyone.

I never dreamt of doing this myself (obviously) and was thinking in terms of what we would need to to know in order to get a consultant to design something to suit us. We've had consultants come in before with disasterous results, I suspect because we didn't really understand our needs and options. All of this (even the parts I didn't understand :-) ) gives me a lot to think about.

Thank you, Ask MeFi.
posted by TayBridge at 10:18 PM on November 5, 2006

Just to clarify the whole front end/back end thing: there are two basically different ways to organize a multi-user database.

One way is to run something like Access, which by default keeps all its data in a file or set of files (something.mdb), then make those files accessible in a central location, and manipulate them by running Access on each workstation that needs to do stuff with the data. This is generally a Bad Idea.

AFAIK (and somebody please correct me if I'm wrong) Filemaker works this way as well: that is, what runs on the workstation is a complete database manipulation system, and the only way to centralize your data is to arrange for multiple instances of Filemaker to share a central data file somewhere, or to set up some kind of periodic synchronization between disparate Filemaker databases. If you've got multiple offices that need simultaneous access to a common database, this is just asking for trouble.

The Right Thing is to centralize the data manipulation services, as well as the actual data; so you have one program running on a central server in charge of the data files, and that program has a way to connect to your assorted workstations. The workstations might still be running something like Access, to present pretty forms and nice reports to the user; but the actual grunt work of selecting, sorting and updating data is all done centrally.

This second way is much, much, much more reliable; so much so that people with database experience often forget that some people actually do try to use the first way.

In this second model, the central database server (the "back end") typically doesn't have any kind of user interface. It has a programming interface designed solely to connect to workstation programs ("front ends") that do have a user interface. Typically, this programming interface will use some form of Structured Query Language (SQL) to interrogate and maintain a relational database; hence SQL Relational Database Management System (RDBMS).

Those workstation front ends can be something specialized like Access, or they can simply be web browsers. It depends entirely on what the back end is set up to talk to.

In the web browser case, as well as the centralized database back end, you also need a (usually centralized) middle layer to translate between web pages for the users' browsers and SQL commands for the back end. Doing it this way means that you've centralized your data, your data manipulation services, and your business rules; which makes the whole thing much easier to maintain (and, importantly, back up!).

Hope that's not too kiddie.
posted by flabdablet at 12:43 AM on November 6, 2006

flabdablet, it's possible to use Filemaker in a centralized way -- you just have to get Filemaker Server. It works pretty well, basically no different in interface from the "file on my desktop" model which the lower-tier products support. Hell, you can even have Filemaker run as a web server, and it translates your layouts into DHTML.
posted by breath at 12:58 AM on November 6, 2006

Thanks; I was unaware of Filemaker Server.

Is the web interface the only standard interface supported on it, or does it do SQL in some form as well?

Also, what's its migration support like? I know Microsoft has a tool (the Upscale Wizard? Some crazy piece of marketroid speak) that's supposed to make it easy to move an Access database from the Jet backend to Microsoft SQL Server; does Filemaker have something similar?
posted by flabdablet at 1:47 AM on November 6, 2006

Just for the record (since someone asked for it upthread): SQL Server 2005 Express' major limitations compared to SQL Server 2005 Standard are:

- 4 GB maximum data base size per database
- 1 GB of maximum RAM will be supported
- Only one processor will be supported
- Doesn't come with the SQL Server Agent component, which is able to schedule tasks such as database backups
- Doesn't come with Analysis Services, Integration Services, Notification Services components. Reporting Services are limited in SQL Server 2005 Express.

The major benefit is of course that it's free as in beer.

Other than that, I'll second the recommendation to get professional help and I can confirm that MS offers an easy migration path from Access to SQL Server in form of the Access Upsizing Wizard.
posted by Herr Fahrstuhl at 3:16 AM on November 6, 2006

We've had consultants come in before with disasterous results, I suspect because we didn't really understand our needs and options.

Part of the consultant's job is to help you define your needs, then present options to satisfy them
posted by Robert Angelo at 5:06 AM on November 6, 2006

The best database is the one that you know how to administer the best. For me, this would be oracle (APEX works great too), but if you are already a microsoft shop, that that may be the best solution for you.

Use something from a major vendor that you can get support on. Use something that has a proven track record to accomplish the tasks you need.
posted by Slenny at 8:37 AM on November 6, 2006

Have you taken a look at Quickbase? I'm not sure if this is what you are looking for, but I have used it at work and am pleased with it. It is fairly flexible and they are actively working on the software, adding more features regularly. (And they are always open to suggestions for said features.) It might be worth a few minutes time to check it out.
posted by inatizzy at 1:35 PM on November 15, 2006