Custom Fields for a Form representing an object

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 IdAttribute (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) OrderMetadata 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.