sternocera
asked on
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.
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.
ASKER
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.
firebird won Best Project for the Enterprise & Best User Support at sourceforge
http://news.com.com/8301-10784_3-9750770-7.html
http://news.com.com/8301-10784_3-9750770-7.html
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.
Regards,
Bill
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.
Regards,
Bill
ASKER
Bill,
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,
Regards,
Peter
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,
Regards,
Peter
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.
Bill
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.
Bill
ASKER
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,
Regards
Peter
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,
Regards
Peter
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
FWIW
Bill
FWIW
Bill
> 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.
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.
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.
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.
ASKER
Bill,
That certainly is food for thought. I think I'll end up putting this off for as long as possible,
Regards,
Sternocera
That certainly is food for thought. I think I'll end up putting this off for as long as possible,
Regards,
Sternocera
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.