I'd know if there's any open-source software with API aimed to handle a (virtually) infinite number of tables of semi-structured data.
What I already know
Relational databases
We all know that the relational databases typically handles a finite number of tables with a (virtually) infinite number of rows. We have MySQL for example for that.
A typical example for this would be store the table of clients of a company... there could be millions of clients.
Spreadsheets
Nevertheless when it comes to data that is "finite in nature", but it's semi-structured dynamically-changing data, users tend to store "shorts lists of things" in places like Excel or Google Spreadsheets.
A typical example for this can be a corner-shop whose owner does not have any software at all, and tracks their daily income into an spreadsheet. And makes a new sheet month by month.
Or an office of architects and they have an spreadsheet to track what CAD software licenses they bought and when to renew them.
Document databases
We could consider that if the "shape of the rows" varies frequently but not all rows are updated in format, one could consider Mongo or ElasticSearch to store just spare documents.
As the table is not aimed to grow infinitely, each table could be stored in a JSON document using an array of arrays.
CSV + editors
CSV is simple yet embarrassing when you have some data and want to version the data. Any tech guy can edit CSV by hand but when you give it to some employee, he should see it "as a user" and "see grids" and not "comma-separated double-quoted rows of text".
Fortunately but some free CSV editors are there to make the life easier.
Problem to solve / Purpose
I want to track "everything" in a small company in tables. For example the tariffs that each provider sends us over email in PDF (but then "translated" to the form of a table). They update tariffs 3 or 4 times per year, there may be 10 to 100 tables per document, we can deal with about 100 providers.
I mean, each of the aout 3000 or 4000 tables we manage every year is different from each other table.
Managing them as 3000 or 4000 excel files, or google spreadsheets or CSV files is complicated, specially when it comes that you want to assign an ID to each table and then "reference the tables".
The tariffs are "static write-once read-many" documents. On the other side the alternative usage (for example a user at home just willing to track "things at home" might have some dozens of tables (list of books we have at home, list of banks I work with, list of service-providers like water or gas that charges me every month, list of friends that have visited our home during last 2 years) all those lists are both: changing slowly + not going to grow to infinite in size.
In all those already-explored cases the thing is:
Relational databases are not "thought" from the point of view that "tables" are "the documents", but that "rows of the tables" are the documents and therefore creating thousands of tables dynamically and changing their field arrangement dynamically is not exactly for what they wehre built for. I can't imagine a sysop allowing users to arbitrarily create any number of tables by using the application.
On the other hand, all other three methods (spreadhseets, document-oriented databases holding a full-table per object, or CSVs) all suffer from the same problem: The tables hold the data but there's not any centralized way of handling the "metadata" of those tables (assign Ids to the tables, list all existing tables, reference some tables from the other tables, keep track of creation-date, etc. The one that is the most near could be google spreadsheets, but again, handling 10.000 google sheets linked together is not handy.
I would like to know if there's any tool that brings the advantages of all those all together:
a) Being able to "freely" change the format of the tables as you do in spreadsheets without having to think an "alter table" each time. b) Being able to "identify" each table with some kind of table-ID allowing cross-references. c) Being usable for any employee even if she's not a techie. d) Thought for having a number of tables growing infinitely.
More over, it should additionally have either "write-protection" (a tariff is a tariff, and when entered, should never modified, if a new update arrives, it's a new table, not the previous edited) or alternatively if it allows edition, it should have revision control.
Objetive requirements
- It MUST be open-source.
- It SHOULD provide some kind of human-interface for the non-tech to be able to write new tables or read existing ones.
- If MUST provide an API so if you do a web application for example with PHP you can recall that data from the tables to be displayed or whatever.
- If SHOULD be docker-friendly.
- It either MUST be implement a WORM storage (suitable for tariffs) or either MUST implement a versioning system (suitable for slow-changing lists) or both (ideally configurable).
The only solution I can think now is to make some "separated documents" (spreadsheets, CSVs or elasticsearch) and make "by hand" a software to administer all of them, index them, assign them the IDs, etc.
But I feel that I'm not the first one in the world wanting this.
Any software around there?
