如何存储数据表（或列表＆LT; KeyValuePair＆LT; INT，对象＆gt;＆gt;中或词典）的数据库？ [英] How to store a data table (or List<KeyValuePair<int,Object>>, or Dictionary) in database?

问题描述

根据实体框架，我们正在努力基本上包括引用的列表，以 OtherObject 称为一个对象，它有一个ID数据的零件模型PostgreSQL数据库。我们尝试存储的数据是为MyObject ，由一个编号和两个列表（与最大长度在 OtherObject 一个或多个引用和 OtherObject S的量的12）。因此，我们想存储的数据（在例如一个单独的表和列），其关联到这

Depending on Entity Framework and PostgreSQL database we are trying to model a part of data which basically consist of a list of references to an object called OtherObject which has an ID. The data we try to store is a MyObject and consists of an Id, and two lists (with a maximum length of 12) of one or more references to the OtherObject and the amount of OtherObjects. So we want to store data (in for example a separate table and columns) which correlates to this:

FirstPattern:
OtherObject with ID 1, 10 times
OtherObject with ID 4, 12 times

SecondPattern:
OtherObject with ID 2, 2 times
OtherObject with ID 3, 4 times
OtherObject with ID 4, 11 times

要做到这一点，我们想到了一个的ICollection KeyValuePair <​​/ code>第将是适当的。这样，我们有一个为每个键（ OtherObject ）。

To do this we thought an ICollection of KeyValuePairs would be appropriate. That way we have a list that has a value (an amount) for each Key (the OtherObject).

该模型基本上NOWS如下所示：

The model nows basically looks as follows:

public class MyObject
{
    public int Id { get; set; }
    public IDictionary<OtherObject, int> FirstPattern { get; set; }
    public IDictionary<OtherObject, int> SecondPattern { get; set; }
}

现在的问题是，我们怎么能可能这个店的ICollection KeyValuePair <​​/ code>第数据库？我们也想知道这是否是做到这一点的最好办法。

The question is, how can we possible store this ICollection of KeyValuePairs in the database? We are also wondering if this is the best way to do this.

我们希望听到它，如果你有一个（更好）的方式存储在数据库中这一数据！感谢您的关注。

We hope to hear it if you have a (better) way to store this data in a database! Thanks for your attention.

推荐答案

有特别适合为词典商品作为一个整体两种类型：的 hstore 和的 JSON - 或者上级大多 jsonb 在Postgres的9.4 +

There are two types especially suited to store dictionaries as a whole: hstore and json - or the mostly superior jsonb in Postgres 9.4+.

Postgres的也有一个专门的 XML 数据类型（如<一个href=\"http://stackoverflow.com/questions/27065244/how-to-store-a-data-table-or-listkeyvaluepairint-object-or-dictionary-in/27066058#comment42644341_27065244\">mentioned由DJ KRAZE 评论），但我宁愿挑选的前三个选项之一。 XML是比较详细和更复杂的（不是说令人费解），可能是矫枉过正你的目的。

Postgres also has a dedicated xml data type (as mentioned in the comments by DJ KRAZE), but I would rather pick one of former three options. XML is comparatively verbose and more complex (not to say convoluted) and may be overkill for your purpose.

如果你从数据库中要的是存储和检索整个字典，这些都是不错的选择。结果
在此dba.SE相关答案细节和链接：

If all you want from the DB is to store and retrieve the whole dictionary, these are good options.
Details and links in this related answer on dba.SE:

• Is有此数据库结构的名字吗？

• Is there a name for this database structure?

您还可以找到各地的利弊广泛讨论的的 EAV （实体 - 属性 - 值）存储在关系数据库中。

You'll also find extensive discussion of pros and cons around eav (entity-attribute-value) storage in relational databases.

如果您想从数据库中其他的东西，比如关系完整性，外键或其他各种限制，轻松获得单独的值，最小的存储大小，简单的指标等，我建议使用专用的一个或多个表（S）（< A HREF =htt​​p://en.wikipedia.org/wiki/Database_normalization相对=nofollow>标准化）列。

If you want other things from the DB, like relational integrity, foreign keys or various other constraints, easy access to individual values, minimal storage size, simple indexes etc. I suggest one or more table(s) with dedicated (normalized) columns.

从我收集，MyObject来（ M ）持有提及OtherObject（ 0 ）。每个 M 有关，（24） 0 ，每个 0 涉及0-n的 M - 这可以在古典N为实现：M关系。以下是详细说明：

From what I gather, "MyObject" (m) holds a collection of references to "OtherObject" (o). Each m is related to (24) o and each o is related to 0-n m - which can be implemented in a classical n:m relationship. Here are detailed instructions:

• How实施PostgreSQL中的许多一对多的关系？

• How to implement a many-to-many relationship in PostgreSQL?

这篇关于如何存储数据表（或列表＆LT; KeyValuePair＆LT; INT，对象＆gt;＆gt;中或词典）的数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！