How to store a data table in database?

2
votes

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?

1
c#asp.net-mvcentity-frameworkpostgresqlmodel
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
Columns that correlate. I'll update the question. – Erwin Rooijakkers
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
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. – Erwin Rooijakkers
Now I now that it is called a dictionary I found a similar SO-question with answer here which provides several solutions, among others a hstore in PostgreSQL. – Erwin Rooijakkers

1 Answers

2
votes

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: