Storing a dictionary <int, string> or KeyValuePair in a database

Question

I wanted to see what others experienced when working with types such as List <> or Dictionary <> and, in turn, store and retrieve this data?

Here's an example scenario: users will create their own “templates”, where these templates are essentially a collection of vocabulary, for example. for user1 the values ​​are: (1, Account), (2, Bank), (3, Code), (4, Savings), and for user2 the values ​​(unrelated) can be (1, Name), (2, (3, class) etc. These templates / lists may have different lengths, but they will always have an index and a value, in addition, each list / template will have one and only one user associated with it.

What types did you choose on the database side?

And pain points and / or tips that I should know about?

Answer 1

Regarding types within a collection, there is a fairly unambiguous mapping between .Net types and SQL types: SQL Server Data Type Mappings . You need to worry most about string fields:

• Will they always be ASCII values ​​(0 - 255)? Then use VARCHAR . If they may contain characters other than ASCII / UCS-2, use NVARCHAR .
• What is their maximum length?

Of course, sometimes you may need to use a slightly different numeric type in the database. The main reason would be that if the int option was selected on the application side because it is "simpler" (or, as I said) than Int16 and byte , but the values ​​will never exceed 32,767 or 255, then you most likely you should use SMALLINT or TINYINT respectively. The difference between int and byte from the point of view of memory in the application layer can be minimal, but it affects the physical storage, especially as the number of lines increases. And if it's not clear, “exposure” means slowing down requests, and sometimes increasing money, when you need to buy more SAN space. But the reason I said “most likely to use SMALLINT or TINYINT ” is because if you have Enterprise Edition and line compression or page compression is enabled, then the values ​​will be stored in the smallest data type that they will fit into .

As for getting data from the database, it is just a simple SELECT .

As for storing this data (at least from the point of view of their effective use), well, this is more interesting :). A good way to migrate a field list to SQL Server is to use table parameters (TVPs). They were introduced in SQL Server 2008. In this answer, I posted a code sample (C # and T-SQL) on a very similar question: Pass Dictionary <string, int> to the T-SQL stored procedure . There is another example of TVP in this question (accepted answer), but instead of using IEnumerable<SqlDataRecord> it uses a DataTable , which is an unnecessary copy of the collection.

EDIT: As for the recent update of the question, which indicates the actual data that is being stored, they should be stored in a table like:

 UserID INT NOT NULL, TemplateIndex INT NOT NULL, TemplateValue VARCHAR(100) NOT NULL 

The PRIMARY KEY should be (UserID, TemplateIndex), as this is a unique combination. There is no need (at least not with the specified information) for the IDENTITY field.

The fields TemplateIndex and TemplateValue will be transferred to TVP, as shown in my answer to the question that I linked above. UserID will be sent on its own as a second SqlParameter . In the stored procedure, you will do something similar to:

 INSERT INTO SchemaName.TableName (UserID, TemplateIndex, TemplateName) SELECT @UserID, tmp.TemplateIndex, tmp.TemplateName FROM @ImportTable tmp; 

---

And just so that this is stated explicitly, unless there is a special reason for this (which should include never, ever the need to use this data in any queries, so that this data is actually only a document and not are more useful in queries than PDF or image), then you should not serialize it in any format. Although, if you were prone to this, XML is a better choice than JSON, at least for SQL Server, since there is built-in support for interacting with XML data in SQL Server, but not for JSON.

Answer 2

The list or any collection view in the databases should be tables. Always think of it as a collection and relate it to what the database offers.

Although you can always serialize a collection, I don’t offer it from the moment you update or insert records, you should always update the entire record or data, while having a table, you will need to request only the KEY in which you already have the Dictionary.