Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a C#/SQL Server program that sometimes needs to store data. The data could be a response from a web service, a result of a database query, or any number of other things. There's no way of knowing before the data is stored how many fields it might have or what the data structure might be. We have this kind of painful table we're using for this... four columns and lots of rows. An example of the data might be easier than an explanation.

InstanceID RowID PropertyName PropertyValue
1          1     Property1    Value1
1          1     Property2    Value2
1          1     Property3    Value3
1          2     Property1    Value1
1          2     Property2    Value2
1          2     Property3    Value3
2          1     OtherProp1   Value1
2          1     OtherProp2   Value2
2          2     OtherProp1   Value1
2          2     OtherProp2   Value2

These values will then be pulled back and fed into a dictionary object, which can be updated, then the fields will be fed back into the database. This can be painful to code against, and also requires a lot of inserts which can make it very slow.

I can't think of a better way of doing this, but I feel like there must be one. Any advice?

share|improve this question
1  
I believe that this is, unfortunately, the solution. You might consider asking to have this migrated (please don't repost) to the dba.stackexchange site where they are likely to have a deeper knowledge of the problem and answer (or for that matter, the question may already be asked on that site - it would be something to check). – user40980 Jan 31 '13 at 17:07
3  
Like the comment above I believe you already have the best solution using a tradition relational database. You are not duplicating data or wasting empty fields. If you had a magic wand to go back in time a document database such as couchdb or mongodb seems like it would be a great fit. – Mike Jan 31 '13 at 17:12

If you don't need to make queries against the data, you could just store all of that in a single column that is in XML-format. Like

<properties>
    <property name="property1">Value1</property>
    <property name="property2">Value2</property>
</properties>

If you do need to make queries against the key value pairs you have couple of options. If you are using SQL Server it has support for xml columns so you can use XPath in your queries. You can also create indexes for XPaths. Other database engines might have similar features as well

You could also use a separate search engine like ElasticSearch which would index your content.

share|improve this answer
11  
Some people, when presented with a problem, think "I know, I'll use XML." <Problem:Worsening> <Problem:TimeDescription>Now</Problem:TimeDescription> <Problem:Posessive>they have</Problem:Posessive> <Problem:Quantity>many, many</Problem:Quantity> <Problem:WorseningDescription>more problems</Problem:WorseningDescription></ProblemWorsening> – Mason Wheeler Feb 18 '13 at 14:29
1  
@MasonWheeler: While I agree with your general sentiment and personally even wage my little holy war against XML misuse, I think this is a viable solution, because for this particular scenario on this very stack, XML has first class support. (Although I would question why it was added in the first place). – back2dos Feb 18 '13 at 18:03
4  
XML is like violence: if it does not solve your problem, you are not using enough of it. – Dan Lyons Feb 18 '13 at 19:14
4  
Well, you could always use JSON instead of XML because it is hip and new ;) – palto Feb 20 '13 at 12:34
2  
JSON is also more concise and uses fewer bytes to convey the same information than XML does. ;) – Craig Apr 17 '13 at 18:12

SQL has not been designed for this scenario, but document and key-value stores have been. Have you considered using one of those?

For example MongoDB has a C# driver with Linq support (although I doubt you will actually need that). You can simply store all the "dynamic data" in one mongodb document per SQL entity.

Another alternative would be redis, simply mapping unique entity identifiers to hashes that contain the corresponding key-value-data.

share|improve this answer

I am going to second the xml solution. I would also say that in LedgerSMB we are looking at doing JSON which is now natively supported under PostgreSQL for extended key/value pairs. I would also point out that currently this can be done in SQL Server by creating some TSQL processing functions. See http://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

However, given the complexity using non-native JSON components, I would say use XML which is the next best thing.

share|improve this answer

You're presently taking the traditional approach for this need within a relational database.

One alteration to consider is strongly-typed columns for the values - as in Option 4 from this post: http://stackoverflow.com/questions/1126783.

share|improve this answer

I have a number of applications that use/store data in a similar manner. I'm not sure exactly where your "pain" is coming from in this instance as it's usually a fairly simple model to follow.

One thing that may help is creating a custom table datatype in SQL that has Name and Value fields. Then calling your stored proc you can simply pass something like InstanceID, RowID, and a table of your custom type containing x number of name-value pairs.

This makes the interface for your calls consistent regardless of what is actually getting passed.

Something else to potentially look at may be creating static objects in a class to cache that. Manage it through methods in that class, and provide async calls to SQL to keep it updated if need be.

share|improve this answer

You should either use a NoSQL database and convert your objects to JSON or you could use NCache and simply serialize your C# objects and store them as is.

If you need to query against those individual values then you should use its SQL capabilities or tag/group your keys since NCache is more than just a simple .net key value store. Read here

store = NCache.InitializeCache("somecache");
DotNetObject dotNetObject = new DotNetObject();
store.Insert("key", DotNetObject);
// ... 
dotNetObject = store.get("key"); //voila you have the object deserialized
share|improve this answer
    
This answer is a combination of other previous answers. It does not add anything significant. – Adam Zuckerman Apr 26 at 3:48
    
Umm, no. You're wrong imho. Two answers are proposing to store in XML and then store it in SQL Servers. One answer is proposing MongoDB but without traditional SQL capabilities since OP is using the SQL server. One answer is partially supporting JSON. I am proposing to store the .net objects(serialized) and then perform search using SQL over it. – Basit Anwer Apr 26 at 4:07

protected by gnat Apr 25 at 11:51

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.