January 21, 2010 at 12:20 pm
I am looking at a problem which would involve users uploading lists of records with various field structures into an application. The 2nd part of this would be to also allow the users to specify fields to capture information.
This is a step beyond anything ive done up to this point where i would have designed a static RDMS structure myself. In some respects all records will be treated the same so there will be some common fields required for each. Almost all queries will be run on these common fields.
My first thought would be to dynamically generate a new table for each import and another for each data capture field spec.Then have a master table with a guid for every record in the application along with the common fields and then fields that specify the name of the table the data was imported to and name of table with the data capture fields.
Further information (metadata?) about the fields in the dynamically generated tables could be stored in xml or in a 'property' table.
This would mean as users log into the application i would be dynamically choosing which table of data to presented to the user, and there would be a large number of tables in the database if it was say not only multiuser but then multitennant.
My question is are there other methods to solving this kind of varaible field issue, im i going down an unadvised path here?
I believe that EAV would require me to have a table defining the fields for each import / data capture spec and then another table with the import - field - values data and that seems impracticle.
January 21, 2010 at 2:29 pm
I've seen pretty similar business specifications.
Somebody asked to implement some "flexible field" alike functionality - "flexible fields" are an Oracle Applications feature.
Here is how it works.
You design your database in term of tables and "basic" columns on each one of them - as you said indexes are on those "basic" columns" and most queries are filtering by those "basic" columns.
Here is the trick, you define in each table a set - actually two sets of "flexible fields" which are nothing but a set of 10 NUMBER and 10 VARCHAR2(4000) columns AND, you also define a catalog where users can name and activate each one of those columns.
So... the final structure has only the tables you have designed, each one of them with the "basic" columns you defined PLUS the "flexible field" columns that users have full control on.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 22, 2010 at 1:49 pm
Thanks for your reply it is just the kind of info im after.
Ive a few more questions about this design...
By catalogue do you mean working with the rdbms metadata or do you mean simply adding another table to the database with structure akin:
[id],,[databasefieldname],[userfieldname]
Would it then be the front end application's job to query the 'catalogue' to retrieve the actual field name from the users field names to generate sql when returning data?
What would the performance comparison be between a table with say 50 fields, each record using 10 of them but different ones, be compared to a table with just 10 fields, for a very large number of records?
Regards
January 22, 2010 at 7:50 pm
gary-951320 (1/22/2010)
By catalogue do you mean working with the rdbms metadata or do you mean simply adding another table to the database
The idea is to build your own "catalog", in a simple version a single table describing each "flex field" in each table would do.
gary-951320 (1/22/2010)
Would it then be the front end application's job to query the 'catalogue' to retrieve the actual field name from the users field names to generate sql when returning data?
A little more complex than that. This kind of solution calls for writting a middle layer that would intepret what the user wants then submit the actual query against the data base. The same way you have to build you own "catalog" you have to build sort of an "app server".
gary-951320 (1/22/2010)
What would the performance comparison be between a table with say 50 fields, each record using 10 of them but different ones, be compared to a table with just 10 fields, for a very large number of records?
A flexible solution would always add overhead; the answer to the big question of "how much overhead" relates to how good or bad the "solution" performs in terms of translating the user request in an actual query.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply