Extending business model and store it in database

Question

Hey Guys

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?

Answer 1

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.

Answer 2

So a couple of things to note with your design first of all if you plan on serializing some Data and placing it in a column (ADDITIONALDATA) the column should not be of type image. If you use an XML type you would be able to store the data and you would be able to query off of it though querying off of it would be a bit of a pain.

As noted by Tom H. your design has some flaws in it. You will find that most of the time you will see 3 tables used for what you are trying to accomplish.

1. Table to store Foo.
2. Table to store Foo Attribute Types. Foo_Att_ID,Foo_Att_Name
3. A Table to store the Values for the additional Foo Attribute Type. Foo_ID,Foo_Att_ID,Value