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.
type
, does it not? Would you mind expanding on what you meant? If a user wants to add a newtype
"type c" to acategory
table (the user interface table, not the database table), my current database design allows for this by inserting intotype
the values( null, category_id, "type c", sortOrder )
. – Decent Dabbler May 1 '16 at 14:33item_type_value.value
), e.g.integer
: you can't add "type c" of typevarchar
. If you design it for column typevarchar
, then 1) storing numbers as would be unoptimized 2) adding a 3rd type that stores more complex data may violate the 1st NF. – try-catch-finally May 1 '16 at 14:59