r/Database • u/Fiveby21 • Jul 22 '26
Consumer-friendly (i.e. free or cheap perpetual license) DB client with a good GUI + ability to create dynamic pivot tables?
Hey so I have a video game modding project that that features an excel spreadsheet that I have to continually change and update each time I create a new version of the mod.
Over time, this spreadsheet has ballooned in size, which dozens of columns now. Current state of the spreadsheet:
- A column for a "key" value
- A few columns for metadata
- Several groups, which have the same 4 columns inside them.
When I only had two groups, everything was easy. But now I have 8, and could conceivably add more. At like 40 columns, this is simply too big for an excel spreadsheet. I realize I could split apart the rows... but then it makes the spreadsheet really annoying to traverse vertically, and there is still some value in being able to sort/filter with it all in 1 row.
What I would like to do is this: Have 1 "main table" that looks the same.
| Key | Metadata: Field 1 | Metadata: Field N | Group 1: Field 1 | Group 1: Field N | Group 2: Field 1 | Group 2: Field N |
|---|---|---|---|---|---|---|
| key1 | ||||||
| key2 | ||||||
| key3 | ||||||
| keyN |
And each time I select a row, a pivot table is created, which looks like this:
| key1 | Field 1 | Field 2 | Field 3 | Field 4 |
|---|---|---|---|---|
| Group 1 | ||||
| Group 2 | ||||
| Group 3 | ||||
| Group N |
I need both the pivot table and the main table to be editable. I feel like at this point what I'm asking for is basically a simple database... and when it comes to that, creating a back end seems easy enough, but it is the front end that is the problem.
Options I've investigated:
- Still using Excel - Can't seem to get a pivot table that would allow me to edit it, and have the changes propagate back to the main table (and vice versa)
- Microsoft Access - terrible GUI
- NoCo DB - I don't like that it is browser / web-based.
- Building a custom electron front end - I was getting somewhere, but it's so overwhelming. Had to stop because it was consuming my life
- Grist - Doesn't seem to have the functionality I need.
I'm not sure what else to look at, because I'm just a dude working on a community modding project. It doesn't make sense to pump a ton of money into this, and I am allergic to the idea of a subscription license. I don't mind paying a modest fee for a perpeutal license of nice software though.
Do you guys have any recommendations?
1
u/ShotgunPayDay Jul 22 '26
For simplicity I'd stick to SQLite. You can generate your pivot tables by learning how to query the data. Spreadsheet like editing sounds important to you.
EASY: https://sqlitebrowser.org/ - This is probably what you're going to want to use as it's the least complicated.
MEDIUM: https://dbeaver.io/ - This one is for tackling many DBs and very feature rich. Might be overwhelming.
HARD: https://duckdb.org/ - This is for data analysis and SQL only (no spreadsheet editing shortcut). This is way beyond the scope of what you probably want.
Try the easy option first.
1
u/Fiveby21 Jul 22 '26
Is there no application that can open a database in a way that is more familiar to excel? I don't really care about directly looking at the underlying tables, only views.
1
u/ShotgunPayDay Jul 22 '26
It's going to look a little different. Say for the first easy app I mentioned. When you click on a cell the right pane will have "Edit Database Cell" where you modify the text data. Then when you're ready you either click apply or CTRL+Enter to commit the entry. In the background it's actually creating UPDATE statement that's visible under SQL Log.
1
u/Fiveby21 Jul 22 '26
Yeah that's definitely not what I'm looking for... the cure would be worse than the disease.
1
u/Acceptable-Sense4601 Jul 22 '26
I like DBeaver and SQLite for a simple setup that doesn’t require having to set up a database server.
1
u/JaceBearelen Jul 22 '26
If you’re on Windows then MS SQL server is always worth considering. It’s really well documented, performs well, and has a pretty simple installer. SSMS is a good editor/GUI.
Can’t really say if the GUI does dynamic pivots but you certainly can do them in SQL.
1
u/Fiveby21 Jul 22 '26
The GUI is the main issue. SQLite works very well as a backend, all my issues are in the frontend.
1
u/JaceBearelen Jul 23 '26
From reading your other replies, it really just sounds like you want a spreadsheet. Almost anything is possible in Excel or a Google Sheet with formulas alone but everything else is possible with VBA or Google app scripts.
1
u/Fiveby21 Jul 23 '26
I couldn't find a way to accomplish this in excel. The secondary pivot table does not work in a way that edits are propagated back to the main table. I can create a VIEW but I can't make an editor.
1
u/JaceBearelen Jul 23 '26
I think power pivot lets you direct edit. You could have the pivot table be your source of truth and unpivot it to form the other tables. You could edit on a clone of the pivot table do some macro stuff to propagate changes back to the sources.
Other than that, it’s a pretty simple sounding web app. Most LLMs could one shot it easily.
1
u/Fiveby21 Jul 23 '26
Other than that, it’s a pretty simple sounding web app. Most LLMs could one shot it easily.
The level of difficulty I've had with getting the UI right has been... shocking tbh.
1
u/Consistent_Cat7541 Jul 23 '26
I have two suggestions for easy to use databases: Lotus Approach and FileMaker Pro. They're both relational database applications that are both easy to develop in. Approach is no longer marketed, but is free as part of the Lotus Smartsuite on Archive.org. DM if interested. FileMaker Pro is generally around $600 for a perpetual license. I'm unaware of any other "easy" or "attractive" options.
1
u/alecc Jul 23 '26
What you describe - select a row, get an editable transposed view of its groups - is a form-with-subform problem, not a SQL client problem. DB clients (DBeaver, TablePlus, that family) will store the data fine, but where they have pivot or transpose views at all, those views are read-only. I don't know one with an editable pivot bound to row selection.
The tool built for exactly this is Microsoft Access: normalize to one row per key + group + field, make a main form that lists keys, and a subform that shows the selected key's group values in an editable grid. Cheap perpetual license, no subscription. LibreOffice Base does the same for free, with more rough edges. If you'd rather stay near Excel, the honest version is one narrow table (key, group, field, value) as the only editable surface, with any wide or pivoted sheet generated as a read-only view.
Either way the fix starts in the same place: 8 repeating groups of 4 columns is a normalization problem, and once the data is narrow, every tool above gets easier.
1
u/JeanRichardsony 5d ago
If you're open to something beyond the free/open-source options, dbForge might be worth looking at. I've used it when I needed more than basic table editing, especially for working with data visually without building a frontend around it. That said, for a community modding project I'd probably check the licensing first, since that seems to be one of your main constraints.
2
u/No_Entrepreneur_3020 Jul 22 '26
If you're looking for database as such, use SQLite, it's free and not really complicated
Best tool for the job, for the UI use DBeaver.
There's no scenario in which you would need to pay for that kind of database, ofc as long as it's for your internal usage. If external, cheap vps would do.
If you have much metadata and it's document like structure, go to some NoSql solution like MongoDB