Database tools - where to start?

I know many of you have some experience in this matter so I’m taking a straw poll.

I am noobish. I can hello world in python and r and ruby and css/js. In python and R I know how to read from csv files and perform operations on them.

I want my little programs/scripts to have something to talk to - for example a flash card app that helps one study by loading a “deck” of related pieces of data. Then I want to make a database or whatever for each course and keep the same front end.

I’ll probably write this in python first and then re-write it for html5 so it can be more portable.

What database system should I be learning? SQL? SQLite? MySQL? Excel? Oracle? Mongo? Airtable, whatever that is?


First thought: KISS: Keep It Simple, Stupid.

If you can avoid it, don’t use a database. Databases are hard to edit work with outside their framework, and hard to port from one system to another. Any of the ones you listed (except for Excel, it isn’t really a database) will work, but so will a simple text file. The ancient Unix utility “fortune” picks a random cookie from a collection of fortune cookies. Its database? A text file with each cookie separated by a null character.

A CSV file is a great place to start, and you already know how to work with them. You can edit them in a text editor or in Excel/Open/Libre Office.

Don’t feel like I’m insulting your skills, or calling you stupid. The use (or lack thereof) of a database is one of the classic examples the authors of the Extreme Programming methodology (Kent Beck, Ward Cunningham, Ron Jeffries, etc) use to explain the principle of YAGNI – You Ain’t Going to Need It. Don’t add functionality because you believe you will need it; wait until you actually do, especially complicated functionality. Databases are complicated, so don’t use them until there’s no simpler solution. It’s amazing how far that can take you.

As far as the ones you’ve listed…

SQL is a standard language used to talk to “relational databases”. All relational databases implement some variant of the SQL standard. So it isn’t a database system per se; most use it.

SQLite is a light-weight in-process database engine, designed so that a single process can interact with a database using SQL without much overhead.

MySQL is a database server that applications can talk to (using SQL). It runs outside the application, and can handle multiple databases, multiple concurrent processes using a single database, etc. It can also be a pain to set up.

Oracle is a large commercial database server primarily aimed at “enterprise” applications. It has been around for decades and is big and expensive. Using it for a project like yours is like doing your daily commute in an MRAP.

Mongo is a “NoSQL” database server, meaning that it is not based on the “relational database” model. I don’t know much about how NoSQL databases work.

I don’t know Airtable.

If you must use one of those, I’d recommend SQLite3. It’s portable, small, works in-process so it doesn’t need you to set up an independent server. I don’t know how well it’ll work with html5 or JS though. I don’t know how any of these would work in that case.

Thank you this is very helpful!

What if the data is not all text strings? Like math expressions or chemical formulas that I will probably store as PNG files. Can a csv in python handle that? Like storing path names and asking the front end to display the resource?