Member Avatar for Member #1129692

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.

  • 6 Contributors
  • 10 Replies
  • 1K Views
  • 9 Years Discussion Span
  • Latest Post Latest Post by kableone

Dani AI

Generated

Short practical addendum for that ties the thread together: is right to stress data model and query quality, and is right that engine/tooling and operational practices matter for long‑running systems. For a networked POS with many cashiers and roughly 1,000 sales/day, follow patterns that make correctness simple and operations predictable rather than chasing raw RDBMS benchmarks.

Make each sale an atomic, short transaction and keep an immutable event/receipt record (easy audit and replay). Use idempotency tokens for external retries and apply inventory changes as single atomic updates (no read-modify-write in application code). Offload reporting/analytics to a replica or nightly ETL so OLTP stays fast. Example minimal write path:

BEGIN;
INSERT INTO sales (sale_id, cashier_id, sale_time, total) VALUES (?, ?, NOW(), ?);
INSERT INTO sale_items (sale_id, sku, qty, unit_price) VALUES (?, ?, ?, ?);
UPDATE inventory SET qty = qty - ? WHERE sku = ? AND qty >= ?;
COMMIT;

Operational checklist: use a connection pooler (PgBouncer or ProxySQL), monitor slow queries/locks, enable point‑in‑time backups, and run bursty-load tests (simulate many simultaneous cashiers). If terminals can go offline, implement a local write queue with deterministic conflict resolution on sync. For many teams the simplest reliable stack is PostgreSQL or MySQL/InnoDB (managed service if no DBA), a normalized OLTP schema plus a replicated/reporting store, and regular profiling to guide optimizations rather than premature engine changes.

Recommended Answers

All 10 Replies

Member Avatar for 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.

Member Avatar for 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?

Edited by Amr_3
Member Avatar for 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.

Member Avatar for 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.

Edited by Amr_3
commented: 2GB. No one needs more than this. (Bill, probably) +12
Member Avatar for 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.

Edited by Reverend Jim
Member Avatar for 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 :)

Edited by Amr_3
Member Avatar for 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.

Member Avatar for Amr_3

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

Member Avatar for aartijangid31

For a network-based POS software system, a relational database like MySQL, PostgreSQL, or Microsoft SQL Server is usually the best choice. These databases provide strong data security, multi-user access, real-time synchronization, backup support, and high performance for inventory management and sales transactions.

If you are developing a cloud-based POS software solution, PostgreSQL is highly recommended because of its scalability and reliability. For small businesses, MySQL is also a cost-effective option.

Keywords: POS software development, network POS system, cloud POS software, database for POS software, retail management software, POS application development.

commented: I'm reminded of Little Johnny Drop Tables! +16
Member Avatar for kableone

For a network-based POS system, the best database usually depends on the size of the business, number of terminals, and scalability needs.

For most modern POS software, these are the best choices:

PostgreSQL → Excellent reliability, performance, and multi-user support. Great for medium to large POS systems.
MySQL / MariaDB → Popular, fast, and easy to manage for retail and restaurant POS systems.
Microsoft SQL Server → Very good for enterprise-level POS environments, especially on Windows networks.
SQLite → Good only for small single-terminal or offline POS systems, not ideal for heavy network usage.

If the POS needs:

Multiple terminals
Real-time inventory sync
Centralized billing
Network access
Good transaction handling

then PostgreSQL is usually one of the best choices because of its stability, ACID compliance, concurrency handling, and scalability.

Also consider:

Automatic backups
Offline sync support
Replication
Security and user permissions
Cloud + local hybrid setup

A common modern architecture is:
POS Clients → API/Backend Server → PostgreSQL/MySQL Database

This is much safer and more scalable than letting all POS terminals connect directly to the database.

Edited by Reverend Jim because: Removed promotional link
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.