6

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?

Muhammad Akhtar
  • 51,913
  • 37
  • 138
  • 191
Alex
  • 75,813
  • 86
  • 255
  • 348
  • 1
    Frankly, I think you're asking for trouble by asking individual users their requirements on-the-fly. For example, if you have 10 people that need the same information but name it 10 different ways, you're going to have a lot of complexity for very little benefit. It might be better to meet with the users and come to a consensus rather than doing things ad-hoc. – Michael Todd Jun 07 '09 at 05:13
  • You're 100% right. I'm stuck with that requirement though :( – Alex Jun 07 '09 at 05:15
  • I have accomplish this in many application, When you implement this type of requirement, its very difficult, but this will give more flexibility to user to add custom fields – Muhammad Akhtar Jun 07 '09 at 06:20

3 Answers3

11

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.

thomasrutter
  • 114,488
  • 30
  • 148
  • 167
  • Given your requirement, this is probably the most straightforward way to do things. Good luck. – Michael Todd Jun 07 '09 at 05:16
  • 1
    Ah yes, the EAV anti-pattern. – Neil McGuigan Apr 12 '13 at 06:50
  • 1
    More info [here](http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) and [here](http://programmers.stackexchange.com/questions/93124/eav-is-it-really-bad-in-all-scenarios) – thomasrutter Apr 12 '13 at 14:20
  • 2
    This is an anti-pattern if used for anything other than *user*-defined custom fields. If this is how you, the *developer*, are going to be creating your own database fields, it's definitely the wrong way to go about it because you'd be needlessly making your database more complex and less efficient. This technique is for when you absolutely need your users to be able to create custom fields without altering the database structure (ie ALTER TABLE). – thomasrutter Apr 14 '14 at 00:46
1

Your best options are:

  1. Allow the user to alter their own database schema, perhaps by uploading a module or running a script.

  2. 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

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
1

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

Muhammad Akhtar
  • 51,913
  • 37
  • 138
  • 191
  • 2
    Though that solution seems "ugly", it's much better in terms of searchability. Doing it the way described in the accepted answer makes it very expensive (in terms of performance) to search for many fields. – Erich Kitzmueller Jun 07 '09 at 06:28