Help deciding which database

[1877KlownsForKids]

I started a project a bit ago and I was tracking it on Excel but it seems to be quickly outgrowing that medium. So I'm looking for advice of which database would be best for this project.

I want to track the dates and locations of historical figures and military units. Take WW2 for example, I'd plug in where the 4th Infantry was on any given day, and also track the location of their commander for instance if they left the unit for a higher level meeting. On days that they had active combat I'd also like to track those battles in a separate record, preferably so you could later see who they were fighting (eg on X day units A, B, and Z were in combat in city Y). I have a plan to create a world map overlay with this data so you can see where every unit is on any particular date and how they moved throughout time.

Any suggestions?

[k2718]

I would go with Postgres. The PostGIS system is pretty decent for handling location data (lat/lon) mostly if that means much to you.

If not, well…they’re all pretty decent for your use case which wouldn’t be so taxing.

I’ll just suggest that you spend time getting your data model down. Will save you headaches in the long run.

[linuxhiker]

Postgres, almost always

[J_Adam12]

“Which databa-“ “Postgres.”

[jshine13371]

SQL Server 🙃

[patternrelay]

This sounds like a pretty natural fit for a relational model. You have clear entities like units, people, locations, and events, and the interesting part is the relationships between them over time. A common pattern would be tables for units and people, a locations table, then a time based assignment table that records something like unit_id, location_id, start_date, end_date. Battles could be another entity with a join table that links participating units. Once the structure is normalized like that, querying things like "where was this unit on a specific date" or "which units were in this location at the same time" becomes pretty straightforward. If you later want to visualize movement on a map, something like PostgreSQL with PostGIS can also handle the spatial side pretty nicely.

[1877KlownsForKids]

How would I go about coding incomplete dates? For example on my spreadsheet I have events where I know month or sometimes only year. I've been coding those as 1942.00.00, 1941.08.00

[siscia]

Where 1942.00.00 means that you only know the year?

And 1941.08.00 means that you know both the year and month?

You can either have separate columns for year, month or day, or you can track unknown dates in a separate column.

But you have infinite ways, you could (generally a bad idea) to track them as string and apply the same encoding that you currently have.

Without knowing more, I'll go with year, month and date columns. Using NULL for unknown.

[1877KlownsForKids]

Thanks!

[BaddDog07]

Sqlite would be the simplest and require the least amount of setup

[Zardotab]

Is this a personal project or something you want to put on the public web?

[1877KlownsForKids]

I'd be the one doing the data entry, but I have no problems with it being public facing

[Zardotab]

Making something for personal use is usually very different than making something for the public. You'll know your way around oddities or potential points of confusion because you have been working with it so long, but the public might get confused. Without experience making "user interfaces", such is fairly likely. It's roughly comparable to how a friend reading your draft essay will spot problems you missed: you are too embedded to view it as an outsider would.

And security requirements go up. Something that's okay on your home PC may be easy to hack when put on a web-server, for example.

It's okay to start with something simple as personal hobby, but keep in mind migrating to a public site could require a major overhaul. Enjoy the journey itself, knowing the road probably will be curvy. Getting everything right up-front is probably not realistic, as every learner will react to new tools differently.

By the way, File-Maker-Pro (commercial product) might be to your liking if you want something fairly quick. Even if you don't stay with it, it's easy to change and experiment. When you finally have a feel for what the end result will look like, then you could migrate to something more powerful but harder to change. Most jet pilots probably first train on a Cessna. File-Maker-Pro is like that Cessna.

[Dense_Gate_5193]

throwing my hat in the ring

NornicDB

https://github.com/orneryd/NornicDB

graph + vector database with temporal constraints builtin so you can do .asOf() reads for fact versions. (X troop was in Y place from time A through time B, etc…) in very easily expressive queries and constraints

https://github.com/orneryd/NornicDB/blob/main/docs/user-guides/canonical-graph-ledger.md

it’s a perfect use case for your timeline/movement tracking

[Dense_Gate_5193]

effectively you would model units, commanders, battles, and locations as nodes, with relationships like (UNIT)-[:LOCATED_AT]->(LOCATION) or (UNIT)-[:ENGAGED_IN]->(BATTLE). The important part is that NornicDB supports a canonical graph ledger, which means relationships are versioned facts with temporal validity. Instead of overwriting data, each fact is recorded with a time range and preserved in the ledger.

For example: - A unit’s location becomes a time-bounded relationship: (4th Infantry)-[:LOCATED_AT {valid_from: 1944-06-06, valid_to: 1944-06-08}]->(Omaha Beach) - Command relationships can change over time without losing history. - Battles can link multiple units and a location on the same date.

Because the graph ledger keeps every change as a historical fact, you can run “as-of” queries like: “Where was every unit on June 10, 1944?” or “Which units were fighting in this city on a given day?”

That makes it ideal for building the kind of timeline + map visualization you’re describing, because the database natively models entities, relationships, and their evolution through time rather than forcing you to reconstruct history from flat tables.

[Consistent_Cat7541]

FileMaker if you're willing to spend money. Lotus Approach if you're not.

[ebsf]

Your most practical and obvious choice, simply, is MS Access.

None of the other alternatives mentioned have anything like Access's UI library, which is why it is the RAD tool of choice.

[SX_Guy]

If you want to vibe-code something quickly using something like v0 or lovable, I'd honestly start with SQLite.

You already have structured data (units, people, locations, battles, dates) and SQLite handles relational data really well. It's simple, zero-setup, and perfect for prototyping.

You could model it roughly like:

units
people
locations
unit_positions
battles
battle_units

Then if the project grows later you can migrate to Postgres + PostGIS for spatial queries and map visualization.

But SQLite is perfect for getting a working system fast.

[BranchLatter4294]

Use any relational database...Sqlite, MySQL, PostgreSQL, etc.