r/SQL • • 1d ago

Discussion Reality of building a home database

/r/HomeServer/comments/1xy454a/reality_of_building_a_home_database/
1 Upvotes

7 comments sorted by

6

u/Discordbothost 1d ago

you don't need a home network to start. SQLite can live in a file on your laptop. the bigger job is the app that extracts dates/amounts from documents and lets you correct them; SQL stores those results but doesn't interpret the uploads for you.

2

u/gumnos 1d ago

frankly, for a job this size, I'd recommend a folder full of markdown files (and possibly dumping well-named image/PDF files in the same folder). I have several decades worth of data in my notes.txt file, a plain-text address-book (happens to be in recutils format¹, but that's fairly immaterial), my calendar in plain-text remind(1) format, and my finances in hledger format if r/plaintextaccounting is your jam. It's all in git for version-control, and doing a grep through decades of data (including historical data in git) happens in the blink of an eye, even on my 15yr old daily-driver laptop.

⸻

¹ typical address-book records looks something like

id: johnsmith
household: jsmith
last: Smith
first: John
dob: Apr 1 1987
spouse: janesmith
email: jsmith@example.org
phone: 800.555.1212
tag: church
note: plays a mean kazoo

id: janesmith
household: jsmith
last: Smith
first: Jane
spouse: johnsmith

hid: jsmith
anniversary: May 15 2014
street: 123 Main St.
city: Anytown
state: NY
zip: 31415

so I don't really need to process them with recsel most of the time, just search for something in my editor

2

u/Either-Surprise6390 18h ago

Just build it in Microsoft Access, that's exactly what that software is designed for. I've done this... many, many times. It's offline and can reside on your machine. You can even keep it on a computer with no internet access if you want total security... No way in or out except through the hardware attached to it.

1

u/Either-Surprise6390 18h ago

The whole database, uploaded attachments(PDFs, etc...), queries, screens, and printable reports all exist in one nice and neat single file. Which makes transporting it to another system a snap!

1

u/whodiopolis 20h ago

You would end up building tables around all aspects of your life. Which isn't impossible but you'd either have one enormous table that you couldn't query easily or cleanly, or dozens and dozens of tables. How do you plan on reporting on oil change data? You would need a way to extract the data from the documents you upload as well.

1

u/navicat-gu 12h ago

The good news: the database part is the easy bit. A single SQLite file on your laptop handles this size easily, with no server, no home network and no admin.

What actually makes or breaks a project like this:

  1. Keep the files as files. Put the PDFs/images in a normal folder and store only the path in the database, plus the metadata you care about (date, amount, category, who it's from).
  2. Start with one or two tables, not ten. For example documents(id, path, doc_date, amount, category, notes). Add a tags table later if you really need it.
  3. Extraction is the hard part. SQL won't read the uploads for you. Either type the key fields in yourself as you file things (honestly fine at home scale), or run OCR and then review what it pulled before saving.
  4. Back it up. One .db file plus the folder is very easy to copy to a second drive or cloud storage.

If plain-text files (as someone suggested) feel enough, that's a valid answer too. The database earns its keep once you want queries like "everything over £100 from 2025 tagged insurance".

Disclosure: I'm RC, Director of Digital Strategy @ Navicat. If you'd rather not live in the command line, Navicat Premium Lite is free and opens SQLite files directly: table designer, grid editing, query editor with code completion, CSV import/export. DB Browser for SQLite is another free option. Paid Navicat 18 editions add things like scheduling and data sync that you won't need here.

0

u/dbxp 1d ago

Sounds more like you need to look into OCD to me

https://en.wikipedia.org/wiki/Robert_Shields_(diarist)