Is this an alright way to design a DB schema for a task scheduling application?

Question

I'm making a to-do list thingy in my spare time for learning etc. I'm using SQL Server Compact 3.5 along with Entity Framework for data management. It is a desktop application, meant to be used by a single person.

I have close to no knowledge with database stuff, and am focusing my energies more on the UI side of things.

I was going along merrily implementing CRUD of tasks, when I thought it would be nice to have some scheduling for the tasks. Begin task in future, repetitions daily/weekly/monthly/yearly/custom etc.

I went on to try to design my DB to accomodate this with my limited knowledge and poof, I end up with like 14 new tables. I then searched online and found posts pointing to sysschedules on MSDN. All accomplished in one table. I lowered my head in shame and tried a puny attempt to improve my design. I got it down to 10 tables while including some stuff I liked from the sysschedules table.

This is my (simplified) schema now(explanation below image):

A Task can have a SchedulingInfo associated with it. I forced OO into this, so SchedulingInfo is an abstract type which has various 'subclasses'.

TimeOfDayToStart_Ticks represents the time to start... since I don't want to store it as a datetime.

The subclasses:

• CustomSchedule: Used to allow a task to run some day, or a set of days, in the future.
• IntervalSchedule: eg. Run everyday, or every 3 days, or every 4 hours, etc.
• Monthly/Yearly-Schedule: Set of days to run every month/year
• MonthlyRelativeSchedule: I stole this from the sysschedules thing. Holds a set of days that conform to things like every second(Frequency) Saturday(DayType), or the last weekday of the month, etc. (See previously mentioned link to see full explanation).

My code will retrieve a list of ScheduleInfo, sorted by NextRun. Dequeue a ScheduleInfo, instantiate a new Task with relevant details, re-calculate NextRun based on the subclass of ScheduleInfo, save the ScheduleInfo back to the DB.

I feel weird about the number of tables. Will this affect performance if there are like thousands of entries? Or is this just like yucky design, full of bad practices or some such? Should I just use the single-table approach?

Answer 1

Yes, I think your table flood will have a negative impact on performance. If YearlySchedule and the other stuff are derived entities from the base entity SchedulingInformation and you have separate tables for base and derived properties you are forced to use Table-Per-Type inheritance mapping which is known to be slow. (At least up to current version 4.1 of EF. It is announced that the generated SQL for queries with TPT mapping will be improved in the next release of EF.)

In my opinion your model is a typical case for Table-Per-Hierarchy mapping because I see four derived entity tables which only have a primary key column. So, these entities add nothing to the base class (except their navigation properties) and would only force unnecessary joins in queries.

I would throw these four classes away and also the fifth - IntervalSchedule - and add its single property Interval_Ticks to the SchedulingInformation table.

The four ...Specifiers tables could all refer then with their foreign keys to the SchedulingInformation table.

So, this would result in:

• Five tables: SchedulingInformation and 4 x *Specifiers
• One abstract base entity: SchedulingInformation
• Five derived entities: *Schedule
• Four entities: *Specifier

Each of the *Schedule entities (except IntervalSchedule) has a collection of the corresponding *Specifier entity (one-to-many relationship). And you map the five *Schedule entities to the same SchedulingInformation table via Table-Per-Hierarchy inheritance mapping.

That would be my primary plan to try and test.