Hi All,
As you are all aware I am creating a EPoS application and at the moment the databases are all CSV files but is there a better way to run the databases as I would like to be able to save, update and delete records from the database simpler then having to read in 2,000 products just to delete one item.
I was thinking about using the csv data only as a data feed from the back office system (windows for now as I am working on plans for a linux version of the back office)
I would LOVE to have direct MySQL support but I have no way of having "offline" support (if the database server or back office computer was switched off) as I can not run local version of MySQL on the machines I have as it would slow them down WAY to much.
Advice is always welcomed as in this field I am still very new (the csv data files work fine but for large systems I would need a better approach so may as well deal with it now while the program is still being designed and created)
The PoS is from now on aimed at running on Linux and Maybe Windows (so if possible a solution that would work on both operating systems)
Best Database practice
Re: Best Database practice
Are you sure about this? Your EPoS application likely uses a fraction of the CPU at any given time, and not very much I/O. I assure you there's plenty of CPU power on even a 15 year old machine to run MySQL with a small database like yours next to EPoS.Gablea wrote:I would LOVE to have direct MySQL support but I have no way of having "offline" support (if the database server or back office computer was switched off) as I can not run local version of MySQL on the machines I have as it would slow them down WAY to much.
What are you database needs exactly? Have you already developed your database schema (tables, fields)? Do you require relational DB capabilities? Do you require more than one instance of EPoS to access the database at the same time? In other words do you require a server/client architecture?Advice is always welcomed as in this field I am still very new (the csv data files work fine but for large systems I would need a better approach so may as well deal with it now while the program is still being designed and created)
The PoS is from now on aimed at running on Linux and Maybe Windows (so if possible a solution that would work on both operating systems)
If you need local databases, SQLite is pretty popular for this sort of thing. Also you'll want to think about backup. SQLite uses files, so you can just back the file up at any time (even while your app is running) as it's always in a consistent state. With MySQL, there is a dump utility that can dump a live database to a file that can be backed up.
Re: Best Database practice
I have the same experience. I used run a wikimedia wiki (with mysqld, php and apache) on a netbook without problem. Starting Firefox on this netbook however, was not the fastest experience.caseih wrote:Are you sure about this? Your EPoS application likely uses a fraction of the CPU at any given time, and not very much I/O. I assure you there's plenty of CPU power on even a 15 year old machine to run MySQL with a small database like yours next to EPoS.Gablea wrote:I would LOVE to have direct MySQL support but I have no way of having "offline" support (if the database server or back office computer was switched off) as I can not run local version of MySQL on the machines I have as it would slow them down WAY to much.
Re: Best Database practice
Depending on your needs I'd recommend SQLite over MySQL (or MariaDB) if you need a simple, leightweight database on a single POS system. However, if you need a central database accessed from different POS terminals or need more advanced database features (like special constraints or stored procedures) you'll have to use MySQL or even more advanced solutions like PostgreSQL (or comparable DB implementations) anyway.
Ideally you'd implement some simple database-abstraction-layer that allows you to support multiple databases or at least simplifies transition from one database solution to another.
Ideally you'd implement some simple database-abstraction-layer that allows you to support multiple databases or at least simplifies transition from one database solution to another.
Re: Best Database practice
Firefox can bog down just about any machine. Well actually any modern web browser is pretty much its own OS these days and can bring a machine to its knees!
Database abstraction could be as simple as writing a set of functions that make the queries, add, delete, or modify the database information. That way all database calls are in one place and it becomes easier to change the database, both in terms of structure, and the engine itself. Think of all of the things you want to do with the database and write functions for each one. For example, if you want to add a new item you are selling, call a function to do that. This is known in the industry as "business logic." Doing it in this way would allow you to start with SQLite, since it's simple and requires no server, and then later when you need a centralized database server, you can easily switch with just minimal modification to your database abstraction functions.
Database abstraction could be as simple as writing a set of functions that make the queries, add, delete, or modify the database information. That way all database calls are in one place and it becomes easier to change the database, both in terms of structure, and the engine itself. Think of all of the things you want to do with the database and write functions for each one. For example, if you want to add a new item you are selling, call a function to do that. This is known in the industry as "business logic." Doing it in this way would allow you to start with SQLite, since it's simple and requires no server, and then later when you need a centralized database server, you can easily switch with just minimal modification to your database abstraction functions.
-
RockTheSchock
- Posts: 252
- Joined: Mar 12, 2006 16:25
Re: Best Database practice
I wonder what is happening now, with csv files, when you have changes on both sides. Do you have some sort of merge/sync mechanism. And what are your hardware specs. You can run mysql/mariadb even on low memory machines with the right configuration without slowing down anything. Most effective option is:Gablea wrote: I would LOVE to have direct MySQL support but I have no way of having "offline" support (if the database server or back office computer was switched off) as I can not run local version of MySQL on the machines I have as it would slow them down WAY to much.
performance_schema = off
So "how to configure MySQL to use less memory, which may also be beneficial in situations where peak performance is not important"
http://www.tocker.ca/2014/03/10/configu ... emory.html
Re: Best Database practice
@RockTheSchockRockTheSchock wrote:I wonder what is happening now, with csv files, when you have changes on both sides. Do you have some sort of merge/sync mechanism. And what are your hardware specs. You can run mysql/mariadb even on low memory machines with the right configuration without slowing down anything. Most effective option is:
performance_schema = off
at the moment the CSV files are the database files at the end of each transaction the PoS checks the shared folder on the server to see if any of the date & time have changed since the last check and if any are different it then downloads them to the local machine (most of the machies have RAM Drives set up to speed up read / write to the Data files)
Ideally I would love to have MySQL running (I am in the process of working out how to migrate my apps to Linux so i can use the text only interface and the frame buffer for graphics) I belive I will have enough CPU and RAM to run a local version of MySQL (CPU is 766MHz RAM is 512MB)