Choosing Our Database

As you know, the database world is divided into SQL databases and other SQL databases. That’s only a slight exaggeration. Even Microsoft Access understands SQL. There are two broad kinds of SQL: server-based and client-based.

Server-based SQL is software that runs on a server. You send the server an SQL query, and the server returns the results to you. The SQL server software receives, manages, and queues up all the queries sent to it by all the users accessing the database. It acts as a traffic cop, a central dispatcher. It makes sure, for example, that two people don’t change the same record at the same time. The disadvantage of server-based SQL is that it runs administrative access to the server, which may not be available.

Client-based SQL runs on the client computer, that is, your computer. In that case, your SQL software opens the database like Excel opens a spreadsheet. You can put a spreadsheet on a shared drive, and then everyone can use it, but only one at a time. If someone tries to open the spreadsheet when someone else has it open, they’ll get a message saying the spreadsheet is in use. This is the same thing that happens with client-based SQL. The difference is that spreadsheets might be opened and closed a couple of times per day, while databases can be accessed thousands of times per second.

Under even medium traffic, client-based SQL databases can quickly become corrupted, assuming their poor performance even allows medium traffic. For this reason, people will often strongly advise against using a client-based SQL database with multiple users. But for low-volume applications, where a database write might come every few seconds or even minutes, client-based SQL works just fine.

What are the choices? The three main ones are Microsoft Access, Microsoft SQL Compact Edition, and SQLite. We’ll eliminate Microsoft SQL Compact Edition because support for it will cease mid 2021. It has a replacement called Microsoft Express Local DB or something like that; I read a couple of pages about it, and it didn’t seem suitable. It’s like a temporary local version of SQL Server, so I didn’t consider it further. Personally, I prefer Microsoft Access because I have Microsoft Access. A sweet deal at work made Office Pro 2016 available on dvd for only $20. But I realize you may not have access to Access, so let’s go with the free solution — SQLite.

That means I’m going to have to figure out how to set up SQLite on my machine.

Leave a Reply

Your email address will not be published. Required fields are marked *