Search code examples
sqlc#-4.0business-objects

Extending business model and store it in database

I have a (kinda general) question about C# 4.0, MS SQL and a business object generated via ADO.NET entity data model.

Let's say I got the MS SQL Table Foo with the rows:

  • ID uniqueidentifier
  • TITLE nvarchar(20)
  • DESCRIPTION text
  • ADDITIONALDATA image

My initial idea was to serialize custom data (the extended properties and their values) to the ADDITIONALDATA field.

But now the question - where can I specify those additional properties? Inside a configuration file (i.e. web.config)? Or is there some other / standard way to achieve this?

Solution

  • Can you provide further details on what exactly you're trying to store here? If you have some serialized object in "ADDITIONALDATA" it's going to make it next to impossible to use any other tool to access your data - for example, a reporting tool.

    In most cases, you should be modeling what is actually being stored in the database. Properties can (roughly) equate to columns in the database most of the time. This allows you to decouple your database and your application to some degree. Otherwise, your application is going to be the only thing that knows how to read the database.

    EDIT:

    You could use the Entity-Attribute-Value model, although there are a lot of potential pitfalls with that approach. The other possibility is to store the data as XML in a column. Again, that's not without problems though. While you can at least search in the XML using SQL's XML functions, it won't be great performance wise. The problem is that you're trying to come up with a generic solution for a problem that's not fully fleshed out. Any approach that you take is going to have problems because of that. If I had to pick, I'd probably go with the EAV model, as much as I hate it, with XML a close second.