Storing Data from Forms without creating 100's of tables: ASP.NET and SQL Server

Let me first describe the situation. We host many Alumni events over the course of each year and provide online registration forms for each event. There is a large chunk of data that is common for each event:

1. An Event with dates, times, managers, internal billing info, etc.
2. A Registration record with info about the payment and total amount charged per form submission
3. Bio/Demographic and alumni data about the 1 or more attendees (name, address, degree, etc.)

We store all of the above data within columns in tables as you would expect.

The trouble comes with the 'extra' fields we are asked to put on t开发者_高级运维he forms. Maybe it is a dinner and there is a Veggie or Carnivore option, perhaps there is lodging and there are bed or smoking options, or perhaps there is an optional transportation option. There are tons of weird little "can you add this to the form?" types of requests we receive.

Currently, we JSONify any non-standard data and store it all in one column (per attendee) called 'extras'. We can read this data out in code but it is not well suited to querying. Our internal staff would like to generate a quick report on Veggie dinners needed for instance.

Other than creating a separate table for each form that holds the specific 'extra' data items, are there any other approaches that could make my life (and reporting) easier? Anyone working in a simialr environment?

This is actually one of the toughest problem to solve efficiently. The SQL Server Customer Advisory Team has dedicated a white-paper to the topic which I highly recommend you read: Best Practices for Semantic Data Modeling for Performance and Scalability.

You basically have 3 options:

• semantic database (entity-attribute-value)
• XML column
• sparse columns

Each solution comes with ups and downs. Out of the top of my hat I'd say XML is probably the one that gives you the best balance of power and flexibility, but the optimal solution really depends on lots of factors like data set sizes, frequency at which new attributes are created, the actual process (human operators) that create-populate-use these attributes etc, and not at least your team skill set (some might fare better with an EAV solution, some might fare better with an XML solution). If the attributes are created/managed under a central authority and adding new attributes is a reasonable rare event, then the sparse columns may be a better answer.

Well you could also have the following db structure:

Have a table to store custom attributes

AttributeID
AttributeName

Have a mapping table between events and attributes with:

AttributeID
EventID
AttributeValue

This means you will be able to store custom information per event. And you will be able to reuse your attributes. You can include some metadata as

AttributeType
AllowBlankValue 

to the attribute to handle it easily afterwards

Have you considered using XML instead of JSON? Difference: XML is supported (special data type) and has query integration ;)

quick and dirty, but actually nice for querying: simply add new columns. it's not like the empty entries in the previous table should cost a lot.

more databasy solution: you'll have something like an event ID in your table. You can link this to an n:m table connecting events to additional fields. And then store the additional field data in a table with additional_field_id, record_id (from the original table) and the actual value. Probably creates ugly queries, but seems politically correct in terms of database design.

I understand "NoSQL" (not only sql ;) databases like couchdb let you store arbitrary fields per record, but since you're already with SQL Server, I guess that's not an option.

This is the solution that we first proposed in ASP.NET Forums (that later became Community Server), and that the ASP.NET team built a similar version of in the ASP.NET 2.0 Membership when they released it:

Property Bags on your domain objects

For example:

Event.Profile() or in your case, Event.Extras().

Basically, a property bag is a serialized collection of data stored in a name/value pair in a column (or columns). The ASP.NET 2.0 Membership went the route of storing names in a semi-colon delimited list, and values in the same:

Table: aspnet_Profile Column: PropertyNames (separated by semi-colons, and has start index and end index) Column: PropertyValues (separated by semi-colons, and only stores the string value)

The downside to that approach is it is all strings, and manually has to be parsed (even though the membership system does it for you automatically).

Recently, my current method is I've built FormCollection and NameValueCollection C# extension methods that automatically serialize the collections to an XML result. And I store that XML in the table in it's own column associated with that entity. I also have a deserializer C# extension on XElement that deserializes that data back to the collection at runtime.

This gives you the power of actually querying those properties in XML, via SQL (though, that can be slow though - always flatten out your read-only data).

The final note is runtime querying: The general rule we follow is, if you are going to query a property of an entity in normal application logic, then you move that property to an actual column on the table - and create the appropriate indexes. If that data will never be queried directly (for example, Linq-to-Sql or EF), then leave it in the XML Property Bag.

Property Bags gives you the power of extending your domain models however you like, without having to modify the db schema.