What kind of Database Suitable for POS Software that need to be network

[සශික]

I'm going to develop new pos system. So I need to choice right database for that. My software work in network.
System going to be handle one thousand transactions per one day. And there are many cashiers will use software.
I want to know is mysql database suitable or any other suitable?
What are any others ?
If you can give me detailed answer I appreciate it.

[rproffitt]

Just my thought on this. I've been at such since dBase days. We used that and Clipper then Btrieve and so on. Why not MySQL? I used it since about 2000 and the apps made back then are still running.

[Amr_3]

It does not matter what database engine you are going to use what does matter is your database design. When it comes to transactions mainly insert, update, and delete data you should targetting a database which structure should conforms to an online transactional processing (OLTP) environment where a normalized model provides good performance for data entry because in a normalized model each table represents a single entity and keeps redundancy to a minimum. However, an OLTP environment is not suitable for reporting purposes because normalized model involves many tables with complex relationships which means that even simple reports will involve joining many tables. When it comes to data retrieval and reporting you are targetting a data warehouse environment in which the database model has intentional redundancy, fewer tables, and simpler relationships, resulting in simpler and more efficient queries as compared to an OLTP environment. In addition to selecting the optimal database model, which corresponded to the usage purpose the second thing is how your SQL queries will be written. To provide a good prove on my opinion, for example, let's say that you realized that MySQL database engine is the best to use but your database model/SQL queries was bad what do you expect the outcome to be?

[Reverend Jim]

At 1000 transactions per day it does matter what kind of database you use. I once had to support an application built on Access. Once the database got to a certain size we had to do a rebuild weekly because it kept crashing. At 1000 transactions per day you'll run into problems soon enough. Unfortunately the developer did not isolate his db code, and he used built-in functions rather than ADO which would have made conversion to MSSQL much easier.

I suggest a database with proper design/maintenance tools such as MSSQL.

[Amr_3]

From my point of view, database engine should not be your first and only concern. Recall if I choosed the right database engine while on the other hand my database design and the way I wrote my queries were not proper it won't matter if I was using Orcale, MySQL, MSSQL etc. The results will be unsatisfactory and do not meet my needs. What should be your first concern is: what type of service your application will provide, what is the database design that will be adequate to serve that application and its provided services, and you should write queries aganist your tables as proper as you can you should be familier with SQL fundamentals. If your first concern was on the database engine while either your database design or your queries or even both were bad the best database engine in the world won't save you from bad results it won't fix your model/queries on your behalf.

Reverend Jim

I did not mention anything about MS Access and I think a few or may be none using such database app to create their software nowadays.

Once the database got to a certain size we had to do a rebuild weekly because it kept crashing

So the problem was not in MS Access as a database engine, however, the problem was on violating either the database itself or the database object specified size because of either how the database is created or database engine specification therefore it needs to be rebuild weekly it does not crash because you were either quering or doing a transaction against as long as that was subject to its specification, the database engine specification. For example, MS Access 1997 Max mdb file size was 1GB while MS Access 2000 and later mdb file size is 2GB. If you violate the size limitation mentioned what do you expect? the same happen with any database engine. MSSQL has database/database object specifications if you violate those specifications your database will crash too. The engine will not be enhance itself automatically to satisfy your need.

I hope that my point of view is clear.

[Reverend Jim]

I did not mention anything about MS Access

I realize that. My point was the the choice of a database engine is important. As for the 1 gig/2 gig size limit, the app in question was nowhere near the stated max limit. It was just an app that was running on a shitty database. In comparison, a system I developed on MS SQL processes almost three million records every day, has been running continuously since around 2000 and is currently about 85 gig. The OP didn't mention Access but he also didn't exclude it.

it does not crash because you were either quering or doing a transaction against as long as that was subject to its specification, the database engine specification

I have no idea what that means.

[Amr_3]

I meant that the db you talked about may be crashed not because you were selecting, inserting, updating, and deleting data from it but because your transactions may be violating the db management system db/db objects specifications any how. For example, MS Access 1997 max record length is 2048. So, if I am inserting a record that exceeds the 2048 length there will be crash. That what I meant by my words and sorry if I wrote it in an incorrect way my English is not that well. I may write statements that cannot be understood :)

[Reverend Jim]

As I recall there was nothing inherently wrong with the database size or the size of any records. But I think I made my point and we are getting into a discussion that is best left for a separate thread.

[Amr_3]

Sure we can move our discussion to a separate thread if you wish :)