What Database server do you reccomend for my point of sale software?

I'm in the process of developing a Point of sale solution for independent retailers.

This is, in essence, an integrated solution that has two elements: one or more tills and one backoffice system

The backoffice portion of the software is being written in C++/MFC for Windows. Through this interface, the user adds products to the system and is given reports on sales and so forth.

The till(s) run Linux and Qtopia.

I'm trying to decide what database server I should use. So far, I've looked at MySQL, PostgresQL and firebird. They all seem to have there pros and cons. MySQL has a nice C++ API called MySQL++, but I'll have to spend a few hundred dollars on a commercial licence for each unit. Postgresql has the advantage of being free as in beer, and of having a BSD licence that won't force me to adopt the GPL, which I'd rather avoid doing, but it doesn't have great windows support and the API seems very complicated to me. Firebird doesn't seem very popular, which is probably indicative of its quality.
Who is Participating?
When you start talking about 6 to 10 tills, I picture a bigger operation, Best Buy for instance where a server is not that big a deal.  I know for a fact that systems from IBM, NCR, etc., are central server systems.  The industry is actually moving to thin client making the terminals smaller, less expensive, and maintenance free.  The ideal situation is to use tills with no application software at all.  After twenty years, businesses have finally come back around to the reality that terminals are much more effective than PCs when you look at the TCO.  Your proposed architecture is, to say the least, swimming upstream against a stiff current.

If all you ever had to do was push new items out to the tills once per day and get sales figures back once per day, you could probably get what you are proposing to work but it would be frustrating for the users.  The real problems manifest when you have to start doing things that your competitors do like dynamic item and price updates, credit card reconciliation, check reconciliation, inventory control, individual clerk logins, customer loyalty program, preferred customer tracking, etc.  Pretty soon, you are moving a lot of data around in order to keep everything in sync.

When you have 4 to 10 tills with databases, there will unavoidably be occasional problems, particularly during polling and push-downs.  Folks decide that they don't want to burn the power on all the tills 24x7 so they turn the ones that are not in use off or they turn them off at closing while the manager works for the next couple three ours in the back office.  Then the manager, whom you described as a computer neophyte, has to hang around and wait for the status of each transfer and then troubleshoot problems with each.  You will have to build GUI status screens, interactive troubleshooting guides, and have the manager running from the front to the back to try and figure out what went wrong.  Of course, it's been a long day and he finally says, ah screw it and leaves it in a bad state.  The next morning, you, or one of the tech support people you have hired to keep up with the calls, will have to walk them through the fix up process.

The problems in a distributed database system are manifold and well know.  That is why nobody does it if they can possibly avoid it.  Availability of all the participant databases, identifier assignment and conflict resolution, data conflict resolution, guaranteed delivery of updates, retransmission of failed transfers, etc., etc., etc.  The only application of distributed databases I have seen in the last 10 years that are the occasionally connected laptop for field service/sales kinds of folks.  Even then, nobody is trying to do a role-your-own for data replication; it is all done with commercial database products like Sybase SQL Anywhere (AKA iAnywhere) that has that kind of replication built in.  Those systems are also always professionally administered again, because they are so complicated and there are so many error conditions and failure modes.  BTW, SQL Anywhere is another option if you insist on going distributed.  A license for embedded run-time only is pretty inexpensive at a qty 1 list of $149 for one user seat with synchronization.  Unless you literally work for peanuts, you will never recoup the time you invest trying to write that kind of facility and get it to work.

I believe part of the problem here is that your desire not to exclude the bottom end of your target market may be forcing your architecture into a corner that will not serve the 4 to 10 till customers well at all.  There is simply a huge difference in the mentality, expectations, required features, and available implementation dollars between 2 tills and 6.

A serious retailer wants the most reliable and headache free system he can get without braking the bank.  The 2 till sole proprietor selling gifts and greeting cards is more likely to go cheap on the system in exchange for spending extra time closing out and doing administrative chores.  I suspect that a one-size fits all approach that spans these two groups is going to be tough to develop.

The other question I have is about your business model.  Do you intend to do all of the on-site installs or is this something you expect to be able to ship in a few boxes to the customer and have them self-install.  There is good money to be made in the field installation business but again, not for the 2 till systems.

Finally, you have to decide how much your develop time is worth and what you want to get paid for it.  Spending double or triple the development and testing time working the kinks out of complicated system may save your low end customers a few bucks, doesn't help the larger scale customers, and you won't, in all likelihood be able to recoup that time spent.  Maintenance and enhancement also gets more complex and time consuming.

You might be able to have your cake and eat it too.  Here is an idea.

1)  Build this as a centralized database application with PostgreSQL or iAnywhere on Linux
2)  Deploy the first till as the server
3)  Deploy subsequent tills as clients.  You have a three options here; thick client that runs application software, diskless client that runs application software, true thin client that just runs X-windows.  Personally, I cannot imagine that it would ever be desirable to put a disk drive in a client till
4)  Offer the option to setup a second server/till as a warm standby.  You can use PostgreSQL replication to keep the second database up to date (almost to the second depending on how you set it up) so that if/when the first server/till fails, the backup one takes over with maybe just restart.

You can build your reporting on Windows if you want and connect as a client machine; though again, with the way the market is moving en mass to Linux, I would not lock myself into Windows.  There is a pretty good chance that your customers are already going to have some kind of computer and printer that could be use for reporting.  Maybe look at something web based for the reports so all they need is a web browser and they can work it from Windows, Mac OS/X, Linux, or whatever.  Ruby On Rails might be a good cross-platform way to get your reports out via browser.

I think this approach can satisfy the spectrum of installation sizes you are looking at and does not penalize the small guys too much.  It will also greatly reduce your development and testing effort, improve reliability, and allow you to go to a diskless or even a thin client with no change in your software at all.

Best of luck,
Aleksandar BradarićSoftware DeveloperCommented:
> Firebird doesn't seem very popular, which is probably indicative of its quality.

Not quite. Firebird is a stable and mature DB server. It has all the goodies including referential integrity, triggers, stored procedures and transactions. The company I used to work for is using Firebird as a back-end for their suite for small/medium sized businesses.

As for the other two, I would certainly go with MySQL. It's flexible, fast and very easy to use and maintain.
sternoceraAuthor Commented:
I would go for MySQL without a second thought, where it not for the fact that it's going to cost about $500 or $600 a unit. Since I'm using a distributed database model, every till is a server, so that adds up to quite a lot. The C++ API wrapper is very easy to use, and MySQL is, as you've said, flexible and fast.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nick UpsonPrincipal Operations EngineerCommented:
firebird won Best Project for the Enterprise & Best User Support at sourceforge

It sounds as if you are trying to make each till stand alone by giving it it's own database.  That seems to me to be buying into a lot of complexity and cost that you might not want unless your typical installation is only one till.

The disadvantages to the distributed approach are:
 - You need more hardware at each till making them more expensive.
 - You will require a hard disk drive in the till.  Without a database, you could use a flash drive
   for the tills to boot and run, increase reliability, and save a huge maintenance problem.
 - The till software installation is more complex and time consuming.  Even if you ghost the drives,
   you still have things like database network client and naming issues to deal with.
 - You buy into a whole slew of data distribution problems including pushing out SKU/Price data,
   coupon information, etc., etc.  The transfers cannot be done in the blind; each has to be verified
   and acknowledged.  A simple flat file push will result in the tills quickly getting out of sync.
 - Collecting the till data becomes a much bigger chore.  How often do you do it?  How do you
   guarantee delivery?  How do you purge old data from the tills?
 - With a centralized database, you only have to put a UPS on one machine, not every till.

There are probably a bunch more reasons not to put a database in each till but the really telling thing is that all of the serious commercial retail systems use one centralized server and maybe a backup.

That said, I would make the central server a Linux machine.  Using one operating system for (or at least different distros of the same O/S) on the tills and the server will save you headaches with things like networking, file sharing, tool chain versions, source code control, cost, etc.  Put OpenOffice and a few other tools in place (all for free) and you have a complete environment for the back office.  You could make the tills even less expensive by removing the disk drive and/or flash disk and having them boot from the server; this might be a bit more complex to setup so you will have to trade that against the cost of the flash drive in the tills.

You can always run a VM (VMware Player is free) with a Winblow$ guest for the sites that want/need Windows for some other reason; a solution that is safer than sharing a Windows installation between a critical server and an office user.  If they screw up the Windows VM, they don't take down the POS system and it can be reloaded without difficulty.  Any Windows-only reporting tools can be run there as well and access the PostgreSQL database without a problem.

PostgreSQL seems to be the right database for your application.  It has all of the big-boy features of the commercial databases, is mature and stable, and when properly tuned is a reasonably fast database, the Windows semi-port not withstanding.  That is, of course, another reason to run the back office on Linux.  

My vote is for one PostgreSQL database on a Linux box in the back office and strip the tills down to make them more cost effective.  You get a multiplier for cost reduction and simplification of the tills since there are several of them compared to only one back office server.

sternoceraAuthor Commented:

I'd have to disagree with your advocating a centralised server, particularly one running Linux, on practical grounds. This product is for small, independent retailers. Reliability and ease of use are paramount. What am I supposed to say when a customer asks where the blue "e" is? What if a customer wants to use his own printer to print reports? What happens when the non-redundant server goes down, which it will? I cannot see these fairly blue collar guys running a windows virtual machine on a Linux postgresql server. It would be asking way too much of them. They probably considered it fairly painstaking to learn microsoft office. Space is a precious commodity in short supply in many of these places, and a server with a back-up server isn't practical. Someone would be bound to put lots of papers on the server, constricting the flow of air and causing overheating, or someone could trip over the UPS wire or any number of things. Getting a single machine fixed isn't a big deal for these guys, but having their whole store grind to a halt definitely is, and it would happen. I'll just have to deal with the complexity of a distributed database as best I can.

I wouldn't put a ups on the tills at all, even if they were all database servers. Why should it really matter if there is a powercut?

I appreciate the advice, but I don't think that the efficiency of a centralised database represents a worth-while trade off against my distributed model, for the reasons already outlined. I don't think it'll be that difficult to get windows and linux talking - everything is done over tcp/ip. I'll connect to the DB remotely, and use an efficient messaging framework like Hessian or JAXRPC something to monitor difference between the master and slave databases, so that the differences may be consolidated efficiently,
You clearly have some compromises to make if your target market is the one or two till bunch.  That is really like squeezing blood out of turnip.  I was considering systems for somewhat larger operations.  I have done work for a restaurant chain and would never even consider putting a database at each server and kitchen station.  What a nightmare with the average restaurant having 8 stations.

As far as user friendliness goes, you are better off if they don't know anything about Linux and don't go poking around.  A server is supposed to be left unmolested.  As you point out, your average back office person doesn't know squat and is as likely as not to let there kid come in an surf the web or install games on the Windows machine on the schools in-service days.  You will spend a lot of time rebuilding those machines and the lost records.

I don't know if you have played with VMware but running a virtual machine is trivial.  You could even have it fire up automatically at boot up.  After that, it is just like a Windows machine and only somebody who knew what he was doing would ever be able to tell the difference.

You are correct that when you have a centralized system, you need a backup.  Again, if your average till count is 1.5, you are correct that having a backup server is probably not practical.  What you are really building is a stand alone till that can be networked with one or two more and you can get centralized reports from.

As far as UPSs for the tills; no database likes to have to plug pulled in the middle of things.  Since you will be recording sales and, I presume performing credit card transactions, on each till's database, you should plan on a UPS for each one.  A decent UPS also gives you some protection against nasty power which you may well run into.  Unless you take it into account, your perfectly good system might gain a bad reputation through no fault of yours.

I believe that you are underestimating the complexity of building a bullet-proof distributed database environment.  Database replication is the most complex thing you can do with databases and is the hardest to get right.  There are trap doors waiting all over the place down that road for those brave enough to attempt to roll-your-own.

Your chosen market is a tough place.  Best of luck to you.

sternoceraAuthor Commented:
I have played with vmware extensively. My market isn't so much the one or two till crowd as the two to ten till crowd. Do you really think that its such an overhead to have each till function as a database server? I mean, I've seen systems running windows xp that are connected to a local firebird database through the loopback interface, and , at least ostensibly, are absolutely reliable. They are in complete isolation - what has a greater then negligable chance of going wrong? If a local copy of a database becomes corrupt, thats not a big deal, and its not all that likely with modern filesystems. I just copy over the database from the backoffice again. If the worst comes to the worst, I'll override the data with my ghost copy.

I can't have a till that is also a server. The store manager can't input new products into the system on a till, its just not practical in that environment.

I accept that creating a distributed database may be difficult, but I cannot live with the alternative. All my competitors have tills that don't require a server. These guys will be spooked (rightly so) when they hear that the whole system requires a server, and that if it goes, the system goes.

Remember when estimating the complexety of implementing such a system that this is a distributed database with a single *central*, authoritative master database, unlike, say, the domain name system.  The only thing that is passed from the till back to the server are the sales figures.  I'll have to devise some kind of integrity check, which, if failed, will cause the whole database to be copied over.

I do hear you, Bill. Could you elaborate on some of the problems I might face when implementing the distributed database model? I am not a database guru,

Aleksandar BradarićSoftware DeveloperCommented:
Centralized systems generally perform quite well. However, the are situations where it's critical (speed, availability, etc) to aslo have a local copy of the data. In these cases it's a good practice to have your application designed so that you can redirect your reads/writes as necessary. At first you can point both reads & writes to the central server. For stations where this is not the ideal solution, redirect reads to a local copy of the DB (sync it using replication or something similar). In the worst case scenario you can also redirect the writes to the local DB. This gives you a high level of flexibility to start small (1 DB server) and improve as/if necessary.
Again, local databases are used for performance or when there is a lack of connectivity; e.g. laptops in the field.  In the case of a system all on a LAN, neither would seem to be an issue.

Aleksandar BradarićSoftware DeveloperCommented:
> In the case of a system all on a LAN, neither would seem to be an issue.

No need to repeat yourself. I'm not contradicting you. I implement centralized solutions myself. What I said in my previous post is what years of field work taught me. Nothing's perfect in the real world; issues arise and even on a LAN, there are cases where a local data copy is needed.
earth man2Commented:
I have always considered the postgresql  C language libpq API simple.  Do not forget you can call C functions from c++ !

With distributed systems you will have the following to consider.
1 Distinct keys when greating record    eg prefix sequence no by distributed db character tag
2 Data security - if a till gets stolen make sure customer confidential data is not retrievable.
3 Data traffic - only replicate what you need ie product codes/prices and summarise others like daily sales.
sternoceraAuthor Commented:
That certainly is food for thought. I think I'll end up putting this off for as long as possible,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.