The Horror of finding the right database! Part 1

How often do you use a database?

• With every application?
• With some applications?
• Never?

A friend recently told me that he believes that if we switch our application from flat files to a database, all access would be much faster.

Of course, this is not the case.

We have large structures with many fields in records. Apart from complicated searches, there is, of course, nothing faster than writing or reading this data directly to disk with a blockwrite or reading it with a blockread command. A stream has almost the same speed apart from a few calls; both methods result in the same Windows API function.

With the overhead of passing the fields to a query so that it then generates long strings from them—which then have to be interpreted again by the database so that the data is finally stored on the hard disk with countless calls—is, of course, much slower.

To make it clear, databases have many functions that are hard to do with a flat file - especially if the file does not fit into memory.

But there is more to consider.

For a single user without any shared access, a single-file database like SQLite fits most of the needs.

If you need more, you could use MariaDB, Firebird, or any other database.

So let's start by selecting the right database! But first, what are our needs?

• Free for use in a commercial application (no license fees)
• Easy installation
• Multi-users over the network
• No dedicated server (we only have a main PC and a notebook in most cases)
• Encryption
• Perhaps a NAS for data storage

So, without a dedicated server, where are our data and our server instance installed? Perhaps on the main PC or on the NAS.

If the main PC is off, the notebook must fire up a server instance to load the data from the NAS (we assume the NAS is too basic to run the server).

What if the main PC gets started? Perhaps we inform the notebook to stop the server and start a server instance on the main PC—because it is faster.

How do we inform a PC in the network to stop the local server and use another one?

Every problem starts with not having a dedicated server! All file-based databases are not really "good" in a multiuser environment.

And now?

We need a CRUD database. Normally, there is no need for joins or any master-detail tables, but it would be nice to have. Do we need to access the database over SQL? Absolutely not—in most cases, we need an object-based database. So more like a NoSQL database?

How do we connect to a database? Of course, over TCP/IP.

Perhaps we could use a database, but—as always—nothing fits our needs perfectly.

Stay tuned for our ideas.

Spoiler: TCP/IP is easy to use...