Data model or database system to use for large data tracker with custom user tags and fields
I want to create an application that allows keeping track of items and associated fields. For example, I may want to keep track of how much coffee I have drunk, and so I would create a coffee entry, and could post updates such as 1 cup of coffee that was 8 oz, 100mg of caffeine, bought from a particular location, and drunk at such a time. I also want to keep track of every update so that I may look at each one later. However, other items I want to keep track of may have completely different fields (like books have titles and could be ranked in terms of enjoyment).
I want to be able to then tag/categorize the data, and then run queries/reports on this data. For example, I may want to see how much I have spent on food in a certain range of time. This would query all items with the food tag, filter based on time fields that they'd all have, and sum up the cost field for all of them.
I was thinking I could do this with a de-normalized relational database with all the item updates in one table. If I had default fields built into that table, such as the ones I said, plus maybe a few spaces for extra custom fields, I think I could do reasonably fast queries. One drawback is adding a new tag to an item would involve copying many records. That could be fixed if there were a fixed number of tag columns, and new tags could just be added but this would limit the number of tags an item had.
开发者_StackOverflowIs there a better relational data model for this that has a better balance of dynamic fields, fast querying, and fast insertion/modification? I was wondering if there's a non-relational database platform that would work better, too. I looked at graph databases and wondered if that would be a good idea since it seems more dynamic, but I don't imagine it being any faster.
I've decided to either use IndexedDB (seems abstract and doesn't use tables), or have a system where I create tables per user, per category. Items in a given category will tend to have the same fields, so there shouldn't be too much wasted space for that, and I could also keep a sum record for each item for all the numerical fields.
精彩评论