User Defined Fields [Archive]

cdwf

01-04-02, 16:32

I work primarily with Oracle, but this question is relevant to just about any RDBMS, I would think.

I have a customer wanting an 'extremely flexible, scalable' database design. Of course, they already have the solution...design a standard relational database, but then allow for users to add their own fields as they need new things to be stored in the database.

The customer doesn't have details about how the RDBMS will work with these fields, just saying that the database experts can figure it out.

Essentially, they talk about tables describing relationships, with the records in the tables holding 'metadata' about the data as well as the data itself. (not a good description, I know, but that's what I have right now...)

My concerns are centered on things like how to handle FK's, indexing, ad-hoc query tools, how the optimizer will work, etc.

I see out on the web that there are many COTS type products that advertise customized or user-defined fields, but I haven't seen any white papers or other treatises on this idea.

Wondering if anyone could share some insight here.

thanks!

Manish

01-05-02, 17:09

The following is not a serious attempt to provide any insight. I read your post and went to watch a TV soap. This is something I just wondered about while watching Tv, and typing it out... :) May be totally out of context for your purpose...still...do have a read.

Database Parts:

1. Core DB Tables (The tables everyone uses; universal)

2. User Tables (users define the tables, fields,etc. and manage the data)

(2) can be implemented in a hierarchical, Network way:

2.1 Tables/Indices maintaing which users have which tables (simplest example: user "Manish" has tables "manish1","cool1", "wow1"). You can allow Total Heirarchy, even maintaining multiple indices for users on the basis of their departments, ranks, alphabetical names,etc.etc.

2.2 Now instead of each table specfying the metadata itself, you can have a separate table created for each user table. e.g. If the user creates the table "cool", store the metadata information in the table "metadata-cool". This way it'd be a snap to store the metadata information in a Standardized way. The Standard Table meta data format may contain Field Names in the actual table, a description (text) of each field, and any other notes, or any information you'd like to store for optimization.

2.3 Now if you have separate tables for meta data information, why not have indices based on meta data too?

So finally you have these minimum structures

A. Tables/Indices pointing to user tables -- mainly pointing to tables created by a particular user. These indices are heirachical. These point to C.

B. Tables/Indices pointing to user metadata tables -- mainly pointing to tables containing a specific metadata information. These may/may not be heirachical. These can even define relations between the current user's data and any bindings to another's data. These point to C.

C. Tables containing metadata information about the tables created by the users. These (should) be in a standard format -- necessary for B. These point to D.

D. Tables containing actual data entered by users. These are the tables managed by the users themselves.

I guess the above, if implmented after much though and design, will be scalable (with optimizations of course) and very flexible.

Tables C can even contain any references back to the Core DB Tables and define any bindings/contraints difficult to put in the user database tables D themselves.

Thats the simplest solution I can propose....do tell me if this is too simple for your purpose...or completely out of context...? :)

ddevaux

07-28-03, 13:28

I was wondering if anyone has ever come up with a good solution for allowing the user to add custom fields to the DB? Has anyone implemented such a solution? It seems many ERP, CRM, etc. solutions allow for this kind of thing, but have not been able to find any explanation of how this is done.

Some approaches I'm considering:

1) Have n fields, UDF_1, UDF_2, etc. each a varchar(2000) in each table that must allow user-defined fields that the user can than use for whatever they want. Elsewhere, have metadata defining what these fields are actually used for. This allows the schema to remain constant, but limits the # of user-defined fields.

2) Have a single text or LONG CUSTOM_FIELDS field in each table which can be used to store XML representing all the user-defined fields for the table. "Base" fields would be stored in the traditional manner w/ actual table fields. Ex, a USER table might have USER_ID, FIRST_NAME, LAST_NAME, and CUSTOM_FIELDS fields. Say a military client wants to track the user's rank. Well, the CUSTOM_FIELDS field would now store something like <rank>Private</rank>. This allows for an unlimited number of user-defined fields. but makes the data hard to use for searching and sorting.

3) Associate with each table a _CUSTOM table to store user-defined fields. Ex if we have USER table, we would have a USER_CUSTOM table which has a user-added RANK field. Requires the schema to actually be changed to allow custom fields, and requires a join between the BASE and _CUSTOM table when retreiving data, and two potential INSERTS or UPDATES instead of one.

Thoughts?

-Dave DeVaux