Custom Fields for a Form representing an object

I have an architectural question concerning custom fields in a view for an object. Let's say you have a User Object with some basic information like firstname, lastname, ... that can be used by all customers.

Now, often we get a question from a customer to add couple of custom fields typical for their domain. Our solution now is an xml data column where key value pairs are stored. This has been ok so far, but now we'll have to find a more architectural solution.

For instance, now, a customer wants a dropdown where it can select the value for its custom field. We could still store the selected value in the xml data column, but where do we store all those dropdown values...

I know that in sharepoint you can also add custom fields like dropdowns and I was wondering how to deal with this best. I want to avoid creating custom tables for customers, or having a table with 90 columns (10 basic and then 10 for each customer), ...

You get the idea, it should be generic and be able to deal with all sorts of problems in the future.

What I was thinking abou开发者_StackOverflow中文版t is a Table UserConfiguration where each record has a Foreign Key to the Customer (Channel in our database), then a column FieldName, a column FieldType and a column Values. The column values should be an xml type column, because for a dropdown, we'll need to add multiple values. Also, each value can have extra data attached to it (not just a name). The other problem then is how to store the selected value. I don't like the idea of having foreign keys to xml in my database (read somewhere that Azure can't handle this all to well). Do you just store the name of the value (what if the value were to disappear out of the xml?)?

Any documentation, links on this kind of problems would also be great. I'm trying to find a design pattern that deals with this kind of problem in the database.

I want to answer your question in two parts:
1) Implementing custom fields in a database server
2) Restricting custom fields to an enumeration of values

Although common solutions to 1) are discussed in the question referenced by @Simon, maybe you are looking for a bit of discussion on what the problem is and why it hasn't been solved for us already.

• databases are great for structured, typed data
• custom fields are inherently less structured
• therefore, custom fields are more difficult to work with in a database
• some or many of the advantages of using a database are lost
  • some queries may be more difficult or impossible
  • type safety may be lost (in the database)
  • data integrity may no longer be enforced (by the database)
  • it's a lot more work for the implementers and maintainers

As discussed in the other question, there's no perfect solution.
But these benefits/features still need to be implemented somewhere, and so often the application becomes responsible for data integrity and type safety.
For situations like these, people have created Object-Relation Mapping tools, although, as Jeff Atwood says, even using an ORM could create more problems than it solved. However, you mentioned that it 'should be generic and be able to deal with all sorts of problems in the future' -- this makes me think an ORM might be your best bet.

So, to sum up my answer, this is a known problem with known solutions, none of which are completely satisfactory (because it's so hard). Pick your poison.

To answer the second part of (what I think is) your question:
As mentioned in the linked question, you could implement Entity-Attribute-Value in your database for custom fields, and then add an extra table to hold the legal values for each entity. Then, the attribute/value of the EAV table is a foreign key into the attribute-value table.

For example,

CREATE TABLE `attribute_value` ( -- enumerations go in this table
    `attribute` varchar(30), 
    `value` varchar(30), 
    PRIMARY KEY (`attribute`, `value`)
);

CREATE TABLE `eav` ( -- now the values of attributes are restricted
    `entityid` int, 
    `attribute` varchar(30), 
    `value` varchar(30), 
    PRIMARY KEY (`entityid`, `attribute`), 
    FOREIGN KEY (`attribute`, `value`) REFERENCES `attribute_value`(`attribute`, `value`)
);

Of course, this solution isn't perfect or complete -- it's only supposed to illustrate the idea. For instance, it uses varchars, and lacks a type column. Also, who gets to decide what the possible values for each attribute are? Can these be changed at any time by the user?

I'm doing something similar for a customer. I've create a JSON FieldType which holds the entire JSON stream of a complex object and a String containing the FQTN (FullQualifiedTypeName) of my C# model class.

By using custom New-, Edit- and Display-Forms we'd ensured that our custom objects are rendered the correct way for best user experience.

To promote fields from the complex C# model to the SharePoint list, we've build something like Microsoft did in InfoPath. Users are able to select Properties or MetaData from the Complex C# type, which will be automatically promoted to the hosting SharePoint list.

The big advantage of JSON is, that its smaller than XML and easier to work with in the web world. (JavaScript...)

When you let the users create the data models, I would recommend looking at an document database or 'NoSQL' since you want exactly that, to store schemaless data structures.

Also, sharePoint stores metadata the way you mentioned (10 columns for text, 5 for dates etc)

That said, in my current project (locked in SharePoint, so Framework 3.5 + SQL Server and all the constraints that follow) we use a somewhat similar structure as below:

Form
 Id

Attribute (or Field)
 Name
 Type (enum) Text, List, Dates, Formulas etc
 Hidden (bool)
 Mandatory
 DefaultValue
 Options (for lists)
 Readonly
 Mask (for SSN etc)
 Length (for text fields)
 Order

Metadata
 FormId
 AttributeId
 Text (the value for everything but dates)
 Date (the value for dates)

Our formulas employ functions such as Increment: INC([attribute1][attribute2], 6) and this would produce something like 000999 for the 999th instance of the combined values for attribute 1 and attribute 2 for a form, this is stored as:

AttributeIncrementFormula
 AtributeId
 Counter
 Token

Other 'formulas' (aka anything non-trivial) such as barcodes are stored as single metadata values. In the actual implementation, we would have something like this:

var form = formRepository.GetById(1);
form.Metadata["firstname"].Value

Value above is a readonly property that decides whether we should get the value from Text or Date and if some additional transform is required. Note that the database here is merely a storage, we hold all the domain complexity in the application.

We also let our customer decide which attribute is the form title for example, so if firstname is the form title, they'll set an in-memory param that spans the entire application to be something like Params.InMemory.TitleAttributeId = <user-defined-id>.

I hope this gives you some insight on a production impl of a similar scenario.

This is really more of a comment than an answer, but I need more space than SO will allow for comments, so here 'tis:

I think your UserConfiguration table approach is good, and would suggest only abstracting the "type" and "value" pieces of your design a bit more:

• Since your application will need to validate user input, each notion of "type" will have an associated piece of evaluation logic. Obviously the more of this you can abstract into data the easier it will be to keep your code small. Enumerated lists are a good start, but if your "validator" logic can be extended to handle pattern matching for text strings and Boolean logical expressions (e.g. to describe/enforce constraints on input values), then you can express pretty much any "type" of input that your application may need to handle in terms of (relatively) simple "atoms" that you can map naturally to DB tables.

• When storing a user-specified value, you can either store the "raw" data (e.g. in JSON) and a foreign key to the associated "type", or you can add an lookup/cache system that assigns an integer to each new value that is encountered by the system ("novelty" can be checked by checking a hash of the "raw" data, for example). The latter approach obviously scales better if you're expecting lots of data duplication (which of course you would in the case of a multiple-choice menu).