如何在数据库中存储数据表（或List< KeyValuePair< int，Object>或者Dictionary）？ [英] How to store a data table (or List<KeyValuePair<int,Object>>, or Dictionary) in database?

问题描述

根据Entity Framework和PostgreSQL数据库，我们正在尝试对一部分数据进行建模，该部分基本上包含一个名为 OtherObject 的对象的引用列表，该对象具有一个ID。我们尝试存储的数据是一个 MyObject ，由 Id 和两个列表（最大长度为12）对 OtherObject 和$ code> OtherObject 的引用的一个或多个引用。所以我们想要存储与之相关的数据（例如单独的表和列）：

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

为此，我们以为$ code> ICollection KeyValuePair s将是适当的。这样我们有一个列表，每个Key都有一个值（一个金额）（ 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).

现在的模型基本如下所示：

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 在数据库中？我们也想知道这是否是最好的方法。

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 数据类型（如 mentione d在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 there a name for this database structure?

您还将在 eav （entity-attribute-value）存储。

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

如果你想从数据库中的其他东西，如关系完整性，外键或各种其他限制，轻松访问各个值，最小存储大小，简单索引等。我建议一个或多个表（（a）） href =http://en.wikipedia.org/wiki/Database_normalization =nofollow noreferrer>归一化）列。

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） o 和每个 o 与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:

• 如何在PostgreSQL中实现多对多关系

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

这篇关于如何在数据库中存储数据表（或List< KeyValuePair< int，Object>或者Dictionary）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！