2

Or List<KeyValuePair<int,Object>>, or a dictionary.

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

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; }
}

How can we possible store this ICollection of KeyValuePairs in the database?
Is there a (better) way?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user2609980
  • 10,264
  • 15
  • 74
  • 143
  • 1
    So is your database really just storing a `string` value? *OtherObject with ID 1, 10 times*. Or is it actually broken down into columns that correlate to those results? – Greg Nov 21 '14 at 15:55
  • Columns that correlate. I'll update the question. – user2609980 Nov 21 '14 at 16:00
  • 1
    have you considered storing all of the data that you need as an XML field in the database.. ? I think there is a similar question asked on `SO` check this posting as well it's pretty simplehttp://programmers.stackexchange.com/questions/185446/better-way-of-storing-key-value-pairs-in-the-database – MethodMan Nov 21 '14 at 16:00
  • Thanks. XML is a solution, but we are looking for something more elegant. I updated the question with the fact that the length of the list with keyvaluepairs is between 0 and 12. Ah, and a list of keyvaluapairs is called a dictionary. :-) That is our structure. – user2609980 Nov 21 '14 at 16:03
  • Now I now that it is called a dictionary I found a similar SO-question with answer [here](http://stackoverflow.com/questions/21052318/storing-a-dictionarystring-string-in-the-database) which provides several solutions, among others a `hstore` in PostgreSQL. – user2609980 Nov 21 '14 at 16:07

1 Answers1

2

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 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:

You'll also find extensive discussion of pros and cons around (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.

Normalized table layout

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • We do want easy access to the individual columns, since they need to be update-able and information needs to be displayed on the screen from the XML. We have a maximum of 24 of those with a maximum length of about 100 characters per XML-string. Would you recommend separate tables for each `OtherObject`? Or just store it as XML? Entity Framework has no build in support for dictionaries so now I was looking at XML-strings and parsing them (like [this example](http://stackoverflow.com/questions/8973027/ef-code-first-map-dictionary-or-custom-type-as-an-nvarchar#8973093)). What would you recommend? – user2609980 Nov 21 '14 at 16:43
  • Thanks a lot for the elaborate answer btw! – user2609980 Nov 21 '14 at 16:44
  • Make it *one* table to hold all instances of an entity: If I understand your situation correctly, you are looking at three tables. I'll add a bit to the answer ... One more question: "MyObject" and "OtherObject" are instances of the same type (same attributes, fit in the same table, each can reference each other) or different by nature? – Erwin Brandstetter Nov 21 '14 at 16:49
  • Yeah if we use XML or JSON it will be *one* table that has all the data. It needs to be updateable, but I don't think that converting to and from JSON will have a huge performance issue, and it does not really matter since most will be done with JavaScript, and only on the server will it be converted to dictionaries. I think one table with IDs and two JSON's is the best idea! – user2609980 Nov 21 '14 at 16:53
  • Hmm, do you know how to install PostgreSQL 9.4 on Linux Mint and Mac? :-) Is it finished? – user2609980 Nov 21 '14 at 17:03
  • @user2609980: Well, practically finished. [RC1 was released just yesterday.](http://www.postgresql.org/about/news/1555/) It will take a couple more weeks until the final release is out. For production I would wait and stick with pg 9.3 for the time being. The switch from `json` to `jsonb` will be simple, most functions and operators are identical. Also, libraries and frameworks may take some time to implement new features. For development I would look at pg 9.4 starting now .. – Erwin Brandstetter Nov 21 '14 at 17:07