Tuesday, December 20

You do not exactly want a relational database

SQL is touted as a relational database, with some historic infighting because of "relational" issues.

But mostly you do not want a relational database.

A "true" relational database would, in SQL, be like if all SELECT statements were really SELECT DISTINCT statements. (This is based on the mathematical definition of a "relation" which is where we get things like outer joins from. Of course, if we instead redefine "relational" to be whatever arbitrary collection of concepts got included in some particular SQL implementation or SQL standard there's no changes from that particular choice which remain.)

So, for example, if you were tracking money (or food, or really anything), and you happened to have multiple rows with the same value, some of that would vanish from your record keeping.

I think the fundamental structure of a database table is a named collection of columns (where each column is a sequence of the same length and each value in a column is the same type - stored the same way and interpreted using the same mechanics).

Though, for databases optimized for things like retail sales, there's a long history of pushing that structure down into the row and implementing the table as a collection of rows (with some waffling about the sequential aspect of these rows - they really are still a sequence, but the traditional database implementation reorders things rather often).