如何在数据库中存储数据表? [英] How to store a data table in database?
问题描述
或List<KeyValuePair<int,Object>>或字典.
根据Entity Framework和PostgreSQL数据库，我们试图对一部分数据建模，该数据基本上由对名为OtherObject的具有ID的对象的引用列表组成.我们尝试存储的数据是一个MyObject，由一个Id以及两个列表(最大长度为12)组成，该列表包含一个或多个对OtherObject的引用和OtherObject的数量.因此，我们要存储与此相关的数据(例如在单独的表和列中)
Depending on Entity Framework and PostgreSQL database we are trying to model a part of data which basically consists 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
为此，我们认为KeyValuePair中的ICollection是合适的.这样，我们就有了一个列表，其中包含每个键(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 now 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; }
}
如何将KeyValuePair个ICollection的这个ICollection存储在数据库中?
有(更好的)方法吗?
How can we possible store this ICollection of KeyValuePairs in the database?
Is there a (better) way?
推荐答案
There are two types especially suited to store dictionaries as a whole: hstore and json - or the mostly superior jsonb in Postgres 9.4 or later.
Postgres还具有专用的 xml 数据类型，但我宁愿选择前三个选项之一. XML比较冗长和复杂(更不用说复杂了)，对于您的目的而言可能是过大的选择.
Postgres also has a dedicated xml data type, 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.
如果您要从数据库中获得的只是存储和检索整个词典，那么这些都是不错的选择.参见:
If all you want from the DB is to store and retrieve the whole dictionary, these are good options. See:
您还会发现围绕 eav (实体属性值)存储在关系数据库中.
You'll also find extensive discussion of pros and cons around eav (entity-attribute-value) storage in relational databases.
如果您要从数据库中获取其他信息，例如引用完整性，外键或其他各种约束条件，则可以轻松访问各个值，最小的存储大小，简单的索引等.我建议使用专用(标准化)列.
If you want other things from the DB, like referential 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"的引用的集合. (o).每个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:
这篇关于如何在数据库中存储数据表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
