I'm writing a system that allows users to store values against each week in October and November and each day in December - starting from the first Monday. I'm holding the raw data entered by the users in a transactional set of tables (3NF I believe). Administrators will be pulling regular reports of that data. As a result, each time the user makes a save I want to extract the data into a denormalised MI table, to make the reporting faster, and help reduce locking on the transactional tables.
Bearing in mind that different years have different numbers of weeks in Oct and Nov, and different number of days in Dec (because we start from first Monday), what is a good design for the MI table? So, something like:
OctW1 OctW2 .. NovW4 NovW5 DecMon1 DecTues2 ... DecMon5 DecTue5
- creating a separate table for each year (tblMI_2012, tblMI_2013 etc), so the schema for each year is only defined once, and all the fields will be populated on each row.
- creating a column for each possible value (so 31 fields for all the December dates) and only populating them when saving values for the respective calendar year.
Which pattern is the best?
Also, should I be also saving the date with the value (effectively doubling the column count)
OctW1 OctW1Date OctW2 OctW2Date etc
Apologies if this is really obvious, my first foray into creating an reporting table.