Should I use a plain text database, or SQLite?

Question

I'm creating a point of sale system for a small shop. I am particularly interested in finding patters from all items purchased, which I will log in their entirety. I'm thinking of even making queries on things like temperature to sales of specific categories of products.

Should I use a plain text based database such as CSV, or go for SQLite?

What are some examples of situations where a plain text database is often used?

Answer 1

making queries on things like temperature to sales of specific categories of products

This (Queries) is one area relational databases are designed for. You are better off with a database with SQL support.

Answer 2

I asked the same question years ago and actually built the app both ways. The build-my-own database version was a lot of fun, but it was a toy. Using a real database enabled a commercial product that performs well under stress.

SQL Database

• SQL lets you write one-liners for most of the kinds of reports you are talking about - one-liners that other programmers will recognize.
• There are all kinds of tools written for databases: backup, sharding, replication, pre-built docker containers...
• You can fairly easily migrate to a different database (PostgreSQL, MySQL, etc.) if you change your mind.
• Most databases have Transactions so that when someone purchases an item, and that adjusts both the money in their account and the stock of the item, the database either takes both adjustments or neither. It simply cannot store half of a transaction and leave you with broken data.
• Other programmers will immediately recognize what you are doing
• To get the same performance out of text files or some made-up binary format, you're going to have to implement some serious data structures. If you don't know what a B-Tree is, or want to spend weeks optimizing it, you'll never compete with the speed of a database.
• Someone else is developing the database, so even when you don't work on it, when you apply upgrades, you get the advantage of their work.
• You didn't say what language, but there are ORM's written for every database. I used to use Hibernate, but the learning curve is steep and I've been having good luck with eBean lately. Sub-point: If you have any loops in the data structure you are saving (users are last-updated by other users), the ORM takes care of not going into a tail-spin re-saving the looped objects.
• Most databases allow constraints of some kind to prevent bad data from ever being entered. You can set it to fail of someone tries to enter a customer without a last name, or an order without an amount, or if the user isn't associated with a company.

Home-Grown Database

• You probably don't need to learn someone else's ORM (Object-Relational Mapping Software)
• You will learn a lot about the algorithms behind databases and the difficulties in implementing them.

I run mysql-backup daily on production and a few times over the years. I've had to do a few restores. The data never gets splinched, half-way between one update and another. Our hosting provider automatically runs these backups for no extra charge.

Finally, SQL databases today are largely commodities. You can do the same basic stuff with PostgreSQL, MySQL, SQLite, SQL-Server, and Oracle. Sure, each has some special thing that the other's don't do, but in general, you don't need that thing. They have reached that point because so many people have used them so many different ways for 40 years now.

Other Options

No-SQL Database

Products like CouchDB are ideally suited for blogs, wiki's, email, and chat software, where the base unit of the app is a document of some kind. SQL-databases are more suited for little pieces of data that have lots of relationships, like products, orders, and inventory.

Key-Value Stores

Products like Redis.io are great when the relationship between items is more important than the items themselves. It's like a big dictionary where you pop in a word, and get back a definition.

Datomic

Has some aspects of a key/value store and some of a SQL database (generally uses a SQL database on the back end). This mostly competes with a SQL database, but you'd pick Datomic when keeping a history is almost more important than keeping the data itself.

Flat Files

Images, videos, and audio files don't belong in a SQL database (thought they might in a no-SQL-document-store). Some other kinds of files or data (like HTML) generally don't either. Keep these things in a folder or folders and when something in the database relates to them, store the file-name in the database.