I am in search of a database construction that enables me to change what fields every desk has ‘on the fly’. It’s in all probability simpler to elucidate by instance.
I am writing an utility the place the consumer defines they need to retailer in a template:
Template title: coffees drunk A. Date coffeeDateTime B. String CoffeeBrand C. int coffeeScore
They then use the applying to report varied situations – so have 5 coffees
Date Model Rating 10th Kenko 7 11th Nescaf 5 12th Kenko 8 12th Keurig 9 13th Nescaf 10
They then determine they need to retailer the model of espresso as properly so change template to
Template title: coffees drunk A. Date coffeeDateTime B. String CoffeeBrand C. int coffeeScore D. String Type
and report a pair extra coffees
Date Model Rating Type 10th Keurig 7 11th Keurig 5 12th Keurig 8 12th Keurig 9 13th Kenko 2 14th Kenko 2 Instantaneous 15th Keurig 10 Bean To Cup 15th Nespresso 10 Pod
Realistically there wont be greater than 30 fields per template.
Realistically solely working with 10,000s rows not 100,000s + per template.
Realistically max 100 templates.
Need the power to question rapidly on any of the values in a template to permit information visualisation and filtering.
The answer have to be appropriate for an app deployed to Android and iOS.
Just a few options come to thoughts:
- One desk per template, add / rename / take away columns as template is adjusted
- One desk mapping templateID to recordID, one other desk with recordID, attributeName, attributeStringValue. The place I retailer every attribute worth for a report in key worth pairing in SQL db.
- Create one desk per template with 31 columns – templateID, Attribute1, Attribute2, … , Attribute30, one other desk with mappings for these attributes relying on template for desk
- Similar as above however one desk for whole dataset, and an additional column for templateID – then pertains to a template desk as above.
I’ve a good quantity of expertise in Java and Oracle, however solely working with present databases, not ever labored with one from the design stage – would actually respect concepts on what greatest apply right here is, and what a very good implementation would appear to be.
I’ve learn a small quantity about NoSQL however am not sure if it fits my wants – in the event you assume it’s may you level me to a very good useful resource to find out about it and find out how to greatest implement?