2

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.

  • If find your relation OK as long as all values are of the same type and s long as there won't be a "type c" which violates the 1st normal form. Note: you should not make your code and database design ready for a future that may never come and you should obey database normalization. A document oriented database might be an alternative if the model should be flexible/changable. – try-catch-finally May 1 '16 at 14:20
  • @try-catch-finally I'm a bit confused about "as long as there won't be a type c". My current relational database design allows for this with the table type, does it not? Would you mind expanding on what you meant? If a user wants to add a new type "type c" to a category table (the user interface table, not the database table), my current database design allows for this by inserting into type the values ( null, category_id, "type c", sortOrder ). – Decent Dabbler May 1 '16 at 14:33
  • How do you think that using an alternative persistence technology will resolve your data quality & cleansing problem? – Erik Eidt May 1 '16 at 14:52
  • You design allows a type c, yes. But continue reading my note: if type c actually consists of two values or another type then you might get into trouble. You have do define a column type (for item_type_value.value), e.g. integer: you can't add "type c" of type varchar. If you design it for column type varchar, 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
  • @ErikEidt Good question. I just thought that, since this data quality aspect is rather lenient, the relational aspect would become pretty much irrelevant here, thereby perhaps allowing for a much simpler storage mechanism. Do you think that makes sense? – Decent Dabbler May 1 '16 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.