7

At some point in my code, I'm creating a dictionary of type Dictionary<string, string> and I'm wondering what's the best way to store this in a database in terms of converting it to a string and then back to a dictionary.

Thanks.

frenchie
  • 51,731
  • 109
  • 304
  • 510

3 Answers3

8

There are a number of options here.

  • You can go the normalization route and use a separate table with a key/value pair of columns.

  • Some databases provide you with a data type that is similar to what you need. PostgreSQL has an hstore type where you can save any key-value pairs, and MS SQL has an XML data type that can be used as well with some simple massaging of your data before insertion.

  • Without this type of database-specific assistance, you can just use a TEXT or BLOB column and serialize your dictionary using a DB-friendly format such as JSON, XML or language-specific serialization formats.

The tradeoffs are the following:

  • A separate table with key/value columns makes for expensive querying and is a PITA in general, but you get the most query flexibility and is portable across databases.
  • If you use a database-powered dictionary type, you get support in queries (i.e "select rows where an attribute stored in the dictionary matches a certain condition"). Without that, you are left with selecting everything and filtering in your program, but
  • You lose database portability unless you code a middle layer that abstracts this away, and you lose ease of data manipulation in your code (because things "work" as if there was a column in your database with this data).

NoSQL databases that are "document oriented" are meant exactly for this type of storage. Depending on what you are doing, you might want to look at some options. MongoDB is a popular choice.

The proper choice depends on the querying patterns for the data and other non-functional issues such as database support, etc. If you expand on the functionality you need to implement, I can expand on my answer.

GomoX
  • 919
  • 8
  • 21
  • Ok, thanks for the suggestion regarding MongoDB type of storage. Eventually my app will be hosted in Azure, which also has a key/value datastore, and I'll change the app to this storage later. – frenchie Jan 10 '14 at 19:22
  • 3
    I would be very wary of platform-specific KV stores, but that's just me :) – GomoX Jan 10 '14 at 19:23
4

If you really want to store the full dictionary as a single string, then you could serialize your dictionary to JSON (or XML) and store the result to the database.

M4N
  • 94,805
  • 45
  • 217
  • 260
1

You have a few options here. You could serialize the object into XML, or JSON as @M4N mentioned. You could also create a table with at least two columns: one for key and one for value.

It really depends on what your domain models look like and how you need to manage the data. If the dictionary values or keys change (IE rename, correction, etc), and needs to be reflected across many objects that are dependent on the data, then creating a sort of lookup table for that maps directly to the dictionary might be best. Otherwise, serializing the data would be one of the best performing options.

Brandon O'Dell
  • 1,171
  • 1
  • 15
  • 22
  • This won't work: each user will have its own dico so I can't add columns as I add dictionaries. – frenchie Jan 10 '14 at 19:23
  • Ok. Just thought I would throw it out there :) Sounds like serialization is the way to go then. JSON will perform a bit better than XML. If you are using MS SQL though, and have to ever query(via SQL) values in the dictionary, XML might be better as T-SQL supports querying XML, but not JSON. – Brandon O'Dell Jan 10 '14 at 19:35