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?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
Have a table that stores the field names and types.
field_ID INT
field_name VARCHAR
field_type ENUM('int','float','text','richtext')
Have a table that stores a link to an entry in the record table, a link to an entry in the field table, and the field value.
fieldvalue_fieldID INT fieldvalue_recordID INT fieldvalue_value BLOB
Making it searchable is another challenge – you would need to grab any searchable content out of that fieldvalue_value and index that. This would be database-specific. In MySQL you could make that a TEXT value and add a MySQL FULLTEXT index on it.
Method 2
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
Method 3
Your best options are:
- Allow the user to alter their own database schema, perhaps by uploading a module or running a script.
- Use an XML field, and a database that supports indexes and querying on the contents of that field
These are recommended by Martin Fowler, here: http://martinfowler.com/bliki/UserDefinedField.html
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0