8

We are developing a .NET application with a SQL Server back-end. The client requests the ability to dynamically add custom attributes to entities after the application has been deployed.

As suggested in a similar question we could create a table which would then contain a row for each custom attribute value (Entity-attribute-value model). However, we are considering allowing end users to actually modify the table (also suggested in the same question) i.e. adding and removing columns.

(Edit: as noted in the comments, DDL wouldn't be executed directly by users or the application, but through stored procedures ensuring that everything runs smoothly)

Main reasons are:

  • Improved performance/searchable attributes
  • The attributes are almost always required to appear as columns e.g. in a data grid in the user interface or when extracting data for further processing in Excel/PowerPivot.
  • Data is strongly typed (as opposed to storing all attribute values as varchar)
  • A simplified data model

Are there any caveats that we should be aware of?

Things that come to mind are:

  • Backup/restore operations that might be unable to handle the changing data structure
  • Dependent objects (such as views) that aren't properly updated to reflect these changes (a dependent view would have to perform a select * from table in order to include any added columns).
  • ...

Any input regarding this approach is greatly appreciated.

Community
  • 1
  • 1
bernhof
  • 6,219
  • 2
  • 45
  • 71
  • 2
    This is probably one case where a denormalized [Entity-attribute-value](http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) model where the attributes are stored as rows would actually make the most sense. – mellamokb Jan 22 '13 at 16:39
  • 2
    Yes, please don't let users throw a monkey wrench into your database. At least with EAV you can have some control over what havoc they're wreaking... https://sqlblog.org/blogs/aaron_bertrand/archive/2009/11/19/what-is-so-bad-about-eav-anyway.aspx – Aaron Bertrand Jan 22 '13 at 16:44
  • @AaronBertrand, thanks for the link, some good points there. However, he mentions the same drawbacks that caused us to explore the non-EAV approach. We wouldn't allow users to execute DDL directly. A couple of stored procedures would serve as monkey wrench repellent, ensuring that add/remove operations run smoothly. Additionally, only qualified users would have permission to execute these. – bernhof Jan 22 '13 at 16:59
  • Which drawbacks specifically? For one, you don't have to store all attributes as `varchar`. You can have as many discrete data types as you need. As for the "simplified data model" you are going to introduce complexity somewhere, why is the data model a worse place for that than any other? (And I'd argue that the model isn't all that complex anyway.) – Aaron Bertrand Jan 22 '13 at 17:02
  • 4
    Also about the comment about the view, you can't just create your view to `SELECT *` and expect things to magically work themselves out. SQL Server caches the metadata so when you add a column the view won't suddenly start showing the new column until you either use `sp_refreshview`, `sp_refreshsqlmodule` or re-generate the view. I don't think I need to mention all the other bad things that come with `SELECT *`... – Aaron Bertrand Jan 22 '13 at 17:04
  • 1
    I asked a question on here a while back about [different options for designing a database with User Defined Fields](http://stackoverflow.com/q/5106335/302677). It's not a duplicate or an answer to the question you posted, however you might find some of the information there useful. – Rachel Jan 22 '13 at 17:04
  • @AaronBertrand: I guess "simplified" could be replaced with "less awkward". What bugs me about EAV is that it is basically a workaround to achieve something which the RDBMS already supports natively (if done properly). This seems especially true when you start adding columns or even tables for each attribute data type. – bernhof Jan 22 '13 at 22:12
  • @Rachel I was searching for a question like that, but must've used the wrong keywords :) Thanks for the link, *very* relevant! – bernhof Jan 22 '13 at 22:25

1 Answers1

2

I work with a third party application that handles this in a variety of ways:

  1. Most tables have a 'custom' version of the table with various fields to hold generically named data types: Number1, Date26, Text3, etc.). So there is Company and CompanyCustom that have a 1-1 relationship.

  2. Lists are created on a table that has a ListID (and a corresponding way for users to setup the schema) and foreign key to link to a main table. This table has several generic columns like #1.

  3. create your own tables

  4. create your own views and stored procedures and register them in the application. These datasets can them be attached to data grids and/or used in custom reports.

There is an interface to the user can label their columns as they see fit( i.e. Text1 = "Blah Blah Blah"). There are plenty of wasted fields in this situation (although my company has managed to use most of the fields including Money47) and it is not ideal for performance, you can't beat the near limitless flexibility we have.

The key here is how much is this client willing to pay for this capability along with the on-going support? If you let them create custom fields on an existing table and they decide they want to change the data type that won't convert smoothly, are they going to expect you to shuffle and convert it?

We could hire a full-time programmer for what we pay for this system. SalesForce.com and similar sites have this capability. I don't think you want to get into this for a one-off client app. They may as well pay you to keep updating the app in the long-run.

JeffO
  • 7,957
  • 3
  • 44
  • 53
  • If I understand you correctly, the "custom" attributes (Number1, Date26 etc.) existed from day one and were just sitting there, waiting to be populated with data? I am not a big fan of this approach although it *does* eliminate some of the immediate drawbacks of physically modifying a table. – bernhof Jan 22 '13 at 22:41
  • Yes, those fields came with the app. I agree, it is not ideal. – JeffO Jan 23 '13 at 04:13
  • Thanks for your input. We are going to avoid the dynamic approach for now and implement new attributes per request instead. – bernhof May 24 '13 at 18:13