[Discuss] Frustration with SQL and Spreadsheets

[markw at mohawksoft.com]

> On Wed, 15 Apr 2026 11:24:49 -0400
> markw at mohawksoft.com wrote:
>
>> I'm not sure I accept your example at all. If you add a column to a
>> spreadsheet, you add that column to all the rows. The same as a
>> database. What am I misunderstanding? if you shift a cell left or
>> right you "break" the columnar relationship of the datum. Just as you
>> would a sql database.
>
> Take that address book example. You have columns A through E in the
> spreadsheet, right? If you enter arbitrary data into a cell in column F
> you are not adding a column to the sheet. You are entering data in a
> column that already exists. This is what I meant by "infinite": you can
> just keep entering arbitrary data into arbitrary cells forever (until
> you run out of spreadsheet).

But, maybe you as the user do not think you are adding a column, but in
fact, you are. They are still referenced by row and column. In the code
itself, it is added to the internal data structures of the spreadsheet.
This is no different than

ALTER TABLE table_name ADD column_name datatype;

>
> You can't do this with a SQL table because there is no column F or G or
> H or.... You have to add the columns to the table first and then you
> can add data to those columns in the record. Which turns out to be a
> lot simpler than I thought.
??
>
> Shifting cell data left/right will break columnar relationships but I'm
> not sure you can do this with SQL records. Which is to say there
> probably are ways to do it but I don't know what they are or what the
> consequences will be.

alter the table and add the column
use an update/select compound statement to more the data
set the old cell to be null

I would bet that the functional flow in both systems is very similar.
>
> An interesting question, I think, is storage efficiency. If you put 8
> bytes into, say row 562, column ZZ, of a spreadsheet then your save
> file will be about 7Kb according to the Untitled 1.ods I just created
> from LibreOffice Calc, and most of that is XML metadata. How big would
> a sqlite3 database file be if you did the same thing?

almost negligible, sqlite and other databases don't save data where there
is no data.

markw at snoopy:~/spread$ cat sqlite.sql
create table sheet
(
    row integer,
    a text,
    b text,
    c text,
    d text
);
markw at snoopy:~/spread$ cat sqlite.sql | sqlite3 test.data
markw at snoopy:~/spread$ ls -l
total 2
-rw-rw-r-- 1 markw markw   93 Apr 15 12:54 sqlite.sql
-rw-r--r-- 1 markw markw 8192 Apr 15 12:55 test.data

markw at snoopy:~/spread$ sqlite3 test.data
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> insert into sheet (row, c) values (20, 10000);
sqlite> .q
markw at snoopy:~/spread$ ls -l
total 2
-rw-rw-r-- 1 markw markw   85 Apr 15 12:56 sqlite.sql
-rw-r--r-- 1 markw markw 8192 Apr 15 12:57 test.data
sqlite> alter table sheet add e text;
sqlite> insert into sheet (row, e) values (20, 10000);
sqlite> .q
markw at snoopy:~/spread$ ls -l
total 2
-rw-rw-r-- 1 markw markw   85 Apr 15 12:56 sqlite.sql
-rw-r--r-- 1 markw markw 8192 Apr 15 13:01 test.data

The databases are pretty efficient.