I'm hoping this question isn't too broad or too prone to opinionated answers, since I could really use some pointers.
I'm trying to come up with a way to persist tabular, spreadsheet-like1, data in a backend. Since the data could very well end up being rather fuzzy (not easily identifiable as being identical, even though the user may have intended it to be), I'm having doubts about persisting it in a relational SQL database.
Users will be able to create multiple tables of this kind:
+====================================================+ | category | +==============================+==========+==========+ | item | type A | type B | +==============================+==========+==========+ | name | value | value | +------------------------------+----------+----------+ | name | value | value | +------------------------------+----------+----------+
The data will merely be used for representational purposes, to give an overview of categorized items and there respective type values. No relational business logic between cell-data will be implemented.
Per table, users will be able to define a certain custom amount of
type columns. Furthermore, there will not be too many constraints on the permitted cell data, except for the
value cells, which will be numeric2. Lastly, users will also be able to migrate
item rows and
type columns between tables.
Originally, my idea was to persist this in a relational SQL database, which, simplified, pretty much comes down to this:
category ------ id PK name item ------ id PK category_id FK category(id) name sortOrder type ------ id PK category_id FK category(id) name sortOrder item_type_value ------ id PK item_id FK item(id) type_id FK type(id) value
The main reasons I'm having starting to have doubts about persisting this in a relational database is that even though a user may intend
type A in one table to mean the same as
type-a in some other table, I'll never be certain. And since I don't want to restrict the user too much (nor do I want to burden myself with too complicated an interface, like letting users first define a
type, which they can then select from a drop-down, for instance, or ask whether they intended
"type-a" to mean the same as
"type A"), I doubt this lends itself particularly well for a relational setup, other than perhaps defining relations about which cells belongs to which tables, etc.
If one is to assume aforementioned lenient constraints, does it make sense to look for an alternative persistent storage mechanisms than a relational SQL database?
If so, could you suggest a particularly well-suited persistence mechanism for this type of data? NoSQL or XML, perhaps?
1) Perhaps spreadsheet-like is a bit misleading; it will simply be tables where users will be able to insert scalar values (strings and/or numbers) in cells. Nothing more; no formulas, calculations, etc. "Spreadsheet-like" pertains to the fact that users will be able to edit the cell-data inline. That's where the similarity basically ends.
2) The numeric constraint isn't actually that important either. It's predominantly mentioned to give a little bit of context.