3

I am working on an application which will require one or more additional fields to be added to a table in order to track user defined information. This additional info is only used for reporting purposes(Crystal Reports), and will have no effect on the behavior of the application. The data for this field is populated from an outside application.

What would be the best way to handle this additional information? Here are some options based off of other SO answers:

  1. Entity-Attribute-Value (would this be overkill? Seems like there are many critics of EAV)
  2. Add additional column to table (not sure how Entity Framework would like this)
  3. Create a new Table for each UDF and use primary of parent table to link
Jason
  • 1,226
  • 12
  • 23
  • Having implemented a relatively efficient EAV system, I wrote a bit about them here: https://sqlblog.org/blogs/aaron_bertrand/archive/2009/11/19/what-is-so-bad-about-eav-anyway.aspx – Aaron Bertrand Feb 22 '12 at 21:27
  • are we talking many new fields for many tables or just a couple new fields for one table? How big is this database currently and you expect it to grow much? – northpole Feb 22 '12 at 21:28
  • @northpole Most likely one or two fields max added to a single table. But could grow depending on client. – Jason Feb 22 '12 at 21:30
  • @AaronBertrand - great article, I enjoyed that. However, it does not appear that the OPs application is an SaaS solution in need of hot changes. If it is only two fields to a single table, do you still think EAV is a good solution for all the overhead needed? – northpole Feb 22 '12 at 21:35
  • @northpole no, but I posted before I knew that information. – Aaron Bertrand Feb 22 '12 at 21:42
  • @AaronBertrand - fair enough, I just wanted to know the opinion of someone with an obvious knowledge of EAV, hopefully I was not coming across as snotty. – northpole Feb 22 '12 at 21:48
  • I still think EAV is manageable, since "only one or two additional columns" will almost certainly turn into 8, or 10, or 30 down the road, depending on the up-front investment. But without more specifics and actually fully understanding the system I'd have a hard time pushing for it. – Aaron Bertrand Feb 22 '12 at 21:51
  • @AaronBertrand in this scenario there won't ever be a need for more than one or two additional UDFs. Based off this, what would you suggest? – Jason Feb 22 '12 at 23:49
  • Create the columns now? You can always rename them later and/or created computed columns with the yet-to-be-determined names once the names are determined. – Aaron Bertrand Feb 22 '12 at 23:50
  • @AaronBertrand, thanks for your link, it's very helpful. – Sefier Tang Oct 20 '12 at 12:44
  • possible duplicate of [How to design a database for User Defined Fields?](http://stackoverflow.com/questions/5106335/how-to-design-a-database-for-user-defined-fields) – Neil McGuigan Aug 06 '13 at 20:32

1 Answers1

0

If I understand the requirement correctly, you will need a datapoint to save information that would come from an external application and its structure is undefined at design time. if that is correct, then I would suggest using an xml datatype. by choosing this, you will not need to redesign your database in future when new key value pairs are inserted. Crystal reports should be easily able to include an xsl for this column.

Hope this help and good luck

Krishna
  • 2,451
  • 1
  • 26
  • 31