Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

As title says it, i'm looking for a way to store many dictionaries (one per user) of type Dictionary<DateTime,enum> in a database (sql server 2012).

Each dictionary has 366 items inside and it represents a calendar where each day has a certain "status", and preferably i don't want to store each item as a separate record in the DB. Ideas?

update:

With the suggestion @AndyBursh & @Konrad Morawski made, i've also come up with another optimisation. Rather than by default creating a new calendar (represented as Dictionary) for each item the user adds, let them pick a predefined one from a drop down, and if that doesn't work for them, then create a separate calendar. With the ability to reuse a calendar and not storing default values, i would have to deal with a lot less data in the long term.

share|improve this question

closed as too broad by gnat, MichaelT, Kilian Foth, GlenH7, World Engineer Apr 11 at 3:42

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

2  
Why would you not store each (user, datetime, enum) tuple as a record? It's pretty much exactly what a database is really good at. –  AndyBursh Apr 10 at 10:54
    
Why don't you want to store each item as a separate record in the database? That's what databases are for. –  Fiona Taylor Gorringe Apr 10 at 10:54
    
my only reason is because i will always need to get the whole list of items and not each record separately, also each user can have up to thousand of those dictionaries, and a thousand users * ~5 dictionareis * 366 records each = 1 830 000 records in no time –  Aviatrix Apr 10 at 10:57
1  
@Aviatrix 2M records is not a very large number, any database should be able to handle that without problems. –  svick Apr 10 at 12:28
3  
@Aviatrix Sure, think about performance, but don't overdo it. If you have a simple and straightforward solution that can be implemented in five minutes and that will work fine for foreseeable future, use it. Don't spend the whole day thinking about a super-fast and super-complicated solution. –  svick Apr 10 at 12:45

3 Answers 3

up vote 3 down vote accepted

Whilst a database would be great at storing a huge number of (user, datetime, enum) tuples, there is another approach that could be taken.

Let's assume that the majority of datetime values would have a default enum value DEFAULT. Instead of storing all (#users)*366 records, one could store just those records which have an enum value which is not DEFAULT. Handling this in code would be as simple as initialising the full range of datetime values with the DEFAULT enum value, then fetching the records from the database and replacing the enum values of the fetched datetimes.

Dictionary<DateTime, MyEnum> GetDictionary(int UserId)
{
    DateTime startDate = new DateTime();
    Dictionary<DateTime, MyEnum> calendar = 
        (from i in Enumerable.Range(0, 365)
        let dt = startDate.AddDays(i);
        select { date = dt, enumVal = MyEnum.DEFAULT})
        .ToDictionary(k => k.date, v => v.enumVal);

    var rows = this.FetchRows(UserId);
    foreach(SomeRow R in rows)
    {
        calendar[R.datetime] = R.enumValue;
    }
    return calendar;
}
share|improve this answer

Squashing the entire dictionary into one row doesn't really feel right, one of the basic principles of database design is atomicity.

You'd have to serialize it somehow (eg. to Json or even a blob), but this is not a recommendable solution and you won't be able to query the data conveniently.

I see no benefits of the your approach you're trying to take.

my only reason is because i will always need to get the whole list of items and not each record separately, also each user can have up to thousand of those dictionaries, and a thousand users * ~5 dictionareis * 366 records each = 1 830 000 records in no time

Okay, but so what? The sheer amount of data will stay the same, PLUS the overhead of deserialization ("unpacking" your dictonaries) or the inconvenience of having to deal with unusual table design (such as 366 columns).

If you're trying to cut down on the amount of data, consider other options, eg. is one enum value default / more prevalent than the others? If so, I'd only store the "unusual" values in the database and the absence of an entry would be treated as default value for the given user and day. But more context should be given.

share|improve this answer
    
Yes, i'm trying to cut the amount of data stored and your last paragraph and @AndyBursh 's answer makes a lot of sense and might be a viable option, but that will cut my data roughly in half. Which is good and i will have to worry about it later on –  Aviatrix Apr 10 at 11:12

Seriously, the best way is 4 columns:

UserID (Guid or int)
CalendarNumber (smallint)
DayNumber (smallint)
Status (tinyint)

Don't make it more complicated than it needs to be. If you did something like create a table with 366 columns, you'll just kick yourself later when you decide that you actually want to store one extra thing for each day, or you want the Status to be a different data type. Try not to design yourself into any corners.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.