Best Database to be used for a multi-user POS system
Hi! I'm developing a POS (point of sales) system to be used in a supermarket. There are a total of 30 terminals. I'm planning of putting the main database in the main server in which every transaction in each terminals will update the main database. My problem now is i'm considering what database to be used? Can MS Access do the job? For example if in case many terminals transact at the same time, will there be no problem?
I've heard that MS Access handle concurrency by locking records being updated, if a terminal is updating, it will disallow other terminals to update the same records by telling them that the "records is being locked by other users". With this kind of concurrency, can this become unreliable especially when the system is being used at peak hours?
Can you guys give me some feedback regarding my problem or can you suggest other better database system available in the market taking into considering the performance and price.
[998 byte] By [
nokiamania] at [2007-11-17 17:07:58]
# 1 Re: Best Database to be used for a multi-user POS system
I have found if u use access it makes the file to corrupted.more often u have to repair the database.
So I feel it is better to use the SQL Server.
That also gives you other facilities like data trnasfering ,Replication etc.
# 2 Re: Best Database to be used for a multi-user POS system
Access shouldn't be used for *serious* business applications. Since you are talking about 30 terminals in a supermarket, I can imaging that the number of transactions will be large. You will notice that Access databases will degrade in performance, with the number of records in the tables, and the number of concurrent users. To put it in other words, access is only suitable for little, small scale applications.
SQL Server on the other hand (or any other database server) use a much better technology than Access, and can limit the amount of data beeing sent over to only that what you really need. Performance is much much higher, and is much more powerfull and expandable than Access. Imaging business going that well that an additional 20 terminals are to be installed. SQL Server can handle this without any problems. Access on the other hand...
Cakkie at 2007-11-10 0:23:39 >
# 3 Re: Best Database to be used for a multi-user POS system
Thanks for all the ideas!
I think i'll try using SQL Server. Just another question, does SQL Server have a friendly way of allowing users to design the table structures like MS Access do? Is it hard to maintain? So If i use SQL server, just the same, i'll connect to it through ADO , ODBC?
Does SQL server handles concurrency very well? I mean like if there are 10 terminals updating at the same time, it will queue all the transactions and update one by one. Because in MS Access, if there are simultaneous updates, it will just tell the other terminals that the record has been locked by other users and does not perform the update.
# 4 Re: Best Database to be used for a multi-user POS system
You can try Sybase also.
Might be less costy than SQL Server and pretty nice too.
# 5 Re: Best Database to be used for a multi-user POS system
Yes u can use ADO or ODBC with SQL Server.
U can generate the script of the SQL Server Tables so that whenever u need to update in the customers location u can just the script.
Even i found SQL server is more fast than the access.
In access,If u write to a table then u tried to get the same data from the some other connection which was opened earlier,it will take some secconds to retrive data,but in SQL it will take no time.
# 6 Re: Best Database to be used for a multi-user POS system
It seems SQL server is really developed by Microsoft to compete with Oracle database :) Far better than MS Access of course :) too bad it cost a lot :(
# 7 Re: Best Database to be used for a multi-user POS system
Normal, Oracle Cost a lot too so is DB2, and it's normal. Those RDBM systems are supposed to hold Data more valuable than the application itself. Since the data is very valuable, they must also make it very secure, very fast and very reliable.
Which I doubt from any microsoft products.
hehe but that's my opinion.
# 8 Re: Best Database to be used for a multi-user POS system
Originally posted by Boumxyz2
Which I doubt from any microsoft products.
Normally I would agree, but I must admit that SQL Server *IS* a good, fast, secure and reliable database server. It all depends on how good the administrator is...
Cakkie at 2007-11-10 0:29:47 >
# 9 Re: Best Database to be used for a multi-user POS system
SQL server has one Major flaw though...
It must run on a microsoft platform ( NT Server, 2000 server or XP server )
But that is not necessarily the case with DB2 which can run on Windows, Unix and OS/390 and oracle that can run on windows, Unix.
And if you have very valuable data.. Don't put it on a windows platform unless you want to keep being unable to access it because you had to reboot the server hehe
# 10 Re: Best Database to be used for a multi-user POS system
Originally posted by Boumxyz2
SQL server has one Major flaw though...
It must run on a microsoft platform ( NT Server, 2000 server or XP server )
Not really a flaw I would say. SQL Server (2000) is highly optimized for Windows 2000, and even better for Windows XP (or .Net server as they like to call it). this gives a kick-*** performance, which can be compared with performance you can get on a mainframe/midrange. Also, it is (quite) easy to administer.
And if you have very valuable data.. Don't put it on a windows platform unless you want to keep being unable to access it because you had to reboot the server hehe
Yeah, right, and don't feed gremlins after 12 o'clock. Another typical arguement of someone who probably hasn't even worked with SQL server, aside from maybe running a query or two. I've been working with SQL Server 2000 for more than 3 years now, and I only had 1 real problem. That was something with replication, and that was with a Beta version. If all Micro$oft products worked as good as SQL Server 2000, the world would be a better place :)
Cakkie at 2007-11-10 0:31:54 >
# 11 Re: Best Database to be used for a multi-user POS system
Well have you worked with Oracle ?
I do and I did. And I worked with DB2 also.. And yes I worked with SQL server an old version though which was running on windows NT.
And the SQL server was down at least once per month.
With my experience with Oracle it never went down. And for DB2, the only time it was down it was because there was a power outage hehe. But Maybe Microsoft tried to make something better out of SQL server, since they're not the leader in the industry for RDBM systems.
# 12 Re: Best Database to be used for a multi-user POS system
Yes, I have worked with Oracle before, but not very intense. Personally, I find that the system is good, but hard to administer.
The biggest problem we have with Oracle is the configuration of the clients.
Yes, I know that there are better overall-RMDB systems out there, but when working in a Windows Environment (like when using VB), SQL Server really is the better one. I am talking about 2000 here, because the previous versions are indeed less performant, and less user-friendly. Another thing to keep in mind (to the future), is that .Net will have backed SQL Server support into it's core. I've done some test with it, and I was really (and I mean really) amazed. I got an average speed gain of 20% when doing database operations in VB.Net compared to VB6. (the program just added 100000 records, and deleted them again)
Cakkie at 2007-11-10 0:33:55 >
# 13 Re: Best Database to be used for a multi-user POS system
Originally posted by Boumxyz2
Well have you worked with Oracle ?
I do and I did. And I worked with DB2 also.. And yes I worked with SQL server an old version though which was running on windows NT.
And the SQL server was down at least once per month.
With my experience with Oracle it never went down. And for DB2, the only time it was down it was because there was a power outage hehe. But Maybe Microsoft tried to make something better out of SQL server, since they're not the leader in the industry for RDBM systems.
I've worked with ORACLE too and you're right, it is hard to find it crashing ONLY IF YOU CONFIGURED IT CORRECTLY THE FIRST TIME. Otherwise ... let me just say GOOD LUCK.
Oracle is better but it comes with huge amount of responsibilities. You really need to understand it inside out to ensure stability. The TOC is much higher too compared to others. :)
So NokiaMania,
My advice is do a little more accessment on your new system design and see if you can stick with SQL Server first. Easy to use and stable enough if your system is not too big (POS system with 30 access points - SQL Server is good enough).
Good Luck,
-Cool Bizs
# 14 Re: Best Database to be used for a multi-user POS system
I've tried using Oracle as the database for our thesis when I was in college. I had to agree with Cakkie by saying that it is hard to configure. Performance-wise, Oracle is really fast and reliable. For SQL Server, I haven't really tried using this before. But based from feedbacks i got from you guys, i think i'll try using it since my concern now in choosing a DB is simplicity and realibility. My understanding is SQL Server is easy to configure and user friendly? I hope i'm right :)
# 15 Re: Best Database to be used for a multi-user POS system
SQL Server IS really easy to maintain. Of course, as with all products, you'll have to know what exactly everything means, but this mostly applies to the configuration options only.
For database design, SQL Server comes with Enterprise Manager, which allows you to create tables and views (queries) on a very simular way as in Access, which speeds up development.
Cakkie at 2007-11-10 0:36:54 >
