I need to allow users to add new fields to a record, e.g. if there is a Contact record, a user may want to add a "SSN" numeric field and a "Birthdate" date/calendar field. They would do this through the UI of course.
Those fields should then be available for entry for all contact records.
Considering that my application runs for many users concurrently (not a single-company deployment etc.) and theoretically everyone could add their own custom fields, what would be the best practice to store this information in a database, especially when it needs to be searchable?
We add almost in our all application/products additional attribute/field support for given flexibility to user
Like we have a product category, In the category, customer can define additional attribute of any product
what we are doing in the DB level is:
Category Table have some additional column like: Text1Att, Text2Att...for text value support, Num1Att, Num2Att... for Number value support, Date1Att, Date2Att... for datetime value support, ID1Att, ID2Att... support for ID from other table like you can add dropdown, listbox,...
here all the column have String datatype.
what we store here is
we will store meta information here, like for Text1Att meta is
SSN;textbox;50;true;false;Null;
Caption of field;Control Type;Max length;is Required field;is Custom validation required; Custom Validation message;
birth place;textbox;100;true;true;Invalid Value;
Same for Numeric field ...
for date meta information will look like
birth date;Calendar control;true;true;Invalid Date;
Caption of field; Calendar control or can be other;is required;is Custom Validation; Custom Validation message;
What are doing in product table is add same number of column and have datatype text1Att,.. is varchar, num1Att have numeric, date1Att have datetime, ID1Att have int
What we are doing GUI side is : In category definition page add these attribute and build meta information at runtime and store in category table
On the other hand when we define product in category, meta information will be read and traverse from category table and populate in product definition page like other fields.
if u need further help, I can provide you images so that you will better understand how can be done this.
we are experience and analyze, this is much flexible approach