Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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). –  MichaelT Jan 31 '13 at 17:07
1  
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

4 Answers 4

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
7  
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
    
@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
2  
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
2  
Well, you could always use JSON instead of XML because it is hip and new ;) –  palto Feb 20 '13 at 12:34
1  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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