0

An entity "schoolyear" has configurable visible week days which are visible or not in a time planner.

I see now 2 posibilites to save the visible week days.

Either I make an N:M relation: N schoolyear has M VisibleWeekDays

Or I put the visible weeks days in a comma separated field: "0,2,5"

The numbers are the enum value from the DayOfWeek enum.

Should I really go this overhead way of create another 2 tables (junction + VisibleWeekDay table) just to hold in one field the index of a day?

How would you save that visible week days?

Elisabeth
  • 20,496
  • 52
  • 200
  • 321

1 Answers1

5

Comma separation in a DB field hurts my feelings. Consider a bitmask:

[Flags]
public enum DayOfWeek
{
   Undefined = 0,
   Mon = 1,
   Tue = 2,
   Wed = 4,
   Thu = 8,
   Fri = 16,
   Sat = 32,
   Sun = 64
}

DayOfWeek bitmask = DayOfWeek.Mon | DayOfWeek.Wed | DayOfWeek.Sat;

This way you maintain sanity and query-ability.

To query this in SQL:

DECLARE @bitmast INT;
SET @bitmask = 64 | 1 | 16; -- sun, mon, fri

SELECT * FROM tbl
WHERE DayOfWeekBitMask & @bitmask = @bitmask;

To query this with LINQ to Entities:

int bitmask = DayOfWeek.Sun | DayOfWeek.Mon |DayOfWeek.Fri;
var query = Context.tbl.Where(r => (r.DayOfWeekBitMask & bitmask) == bitmask);

To persist:

int bitmask = DayOfWeek.Sun | DayOfWeek.Mon |DayOfWeek.Fri;
var item = Context.tbl.First();

item.DayOfWeekBitMask = bitmask;
Context.SaveChanges();
Paul Fleming
  • 24,238
  • 8
  • 76
  • 113
  • Funny I also thought about kind of a bit mask and an xml field... How do you store and query the sunday,monday and friday from and to the DayOfWeek that would be really helpfull to get the whole idea! – Elisabeth Mar 21 '13 at 20:40
  • @Elisa I've added examples of update, and select using entities and select using SQL. – Paul Fleming Mar 21 '13 at 20:45