c# - Storing a Dictionary<int,string> or KeyValuePair in a database

Question

I wanted to see what others have experienced when working with types like List<> or Dictionary<> and having in turn storing and retrieving that data?

Here's an example scenario: users will be creating their own "templates", where these templates is essentially a collection of Dictionary, e.g. for user1, values are (1, Account), (2, Bank), (3, Code), (4, Savings), and for user2, values (unrelated) could be (1, Name), (2, Grade), (3, Class), and so on. These templates/lists could be of varying length but they will always have an index and a value. Also, each list/ template will have one and only one User linked to it.

What types did you choose on the database side?

And pain-points and/or advice I should be aware of?

Answer 1

List or any collection's representation in databases are supposed to be tables. Always think of it as a collection and relate it to what a database offers.

Though you can always serialize a collection, i do not suggest it since updating or inserting records, you'd always update the whole record or data whereas having a table, you'd only have to query for the KEY wherein Dictionary, you already have it.