这是为任务调度应用程序设计数据库模式的好方法吗？
我在业余时间制作了一个待办事项列表，用于学习等。我使用 SQL Server Compact 3.5 和 Entity Framework 进行数据管理。它是一个桌面应用程序，仅供一个人使用。
我对数据库方面几乎一无所知，我的精力更多地集中在 UI 方面。
当我认为对任务进行一些调度会很好时，我正在愉快地实现任务的 CRUD。将来开始任务，每天/每周/每月/每年/自定义等重复。
我继续尝试设计我的数据库以适应我有限的知识和噗，我最终得到了大约 14 个新表。然后我在网上搜索并找到了指向 MSDN 上的 sysschedules 的帖子。所有这些都在一张表中完成。我羞愧地低下头，尝试着改进我的设计。我将其减少到 10 个表，同时包括 sysschedules 表中我喜欢的一些内容。
这是我现在的（简化的）架构（解释如下图）： 
一个任务可以有一个SchedulingInfo关联它。 我强迫 OO 这样做，所以 SchedulingInfo 是一个抽象类型，它有各种“子类”。
TimeOfDayToStart_Ticks 表示开始时间...因为我不想将其存储为日期时间。
子类：
- CustomSchedule：用于允许任务在未来的某一天或一组天运行。
- IntervalSchedule：例如。每天运行一次，或每 3 天运行一次，或每 4 小时运行一次，等等。
- 每月/每年计划：设置每月/每年运行的天数
- MonthlyRelativeSchedule strong>：这是我从 sysschedules 中偷来的。保存一组日期，这些日期符合每秒（频率）星期六（DayType）或该月的最后一个工作日等（请参阅前面提到的链接以查看完整说明）。
我的代码将检索 ScheduleInfo 列表，按 NextRun 排序。将 ScheduleInfo 出队，实例化一个具有相关详细信息的新任务，根据 ScheduleInfo 的子类重新计算NextRun，将 ScheduleInfo 保存回数据库。
我对桌子的数量感到奇怪。如果有数千个条目，这会影响性能吗？或者这就像令人讨厌的设计，充满了不好的做法或类似的东西？我应该只使用单表方法吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的，我认为你的表洪水会对性能产生负面影响。如果
YearlySchedule和其他内容是从基本实体SchedulingInformation派生的实体，并且您有单独的基本属性和派生属性表，则必须使用 Table-Per-众所周知，类型继承映射速度很慢。 （至少到 EF 的当前版本 4.1。据宣布，将在下一个版本的 EF 中改进使用 TPT 映射查询生成的 SQL。）在我看来，您的模型是 Table-Per 的典型案例-层次结构映射，因为我看到四个派生实体表只有一个主键列。因此，这些实体不会向基类添加任何内容（除了它们的导航属性），并且只会强制查询中不必要的连接。
我将丢弃这四个类以及第五个类 -
IntervalSchedule- 并将其单个属性Interval_Ticks添加到SchedulingInformation表中。四个
...Specifiers表都可以通过它们的外键引用SchedulingInformation表。因此，这将导致：
SchedulingInformation和 4 x*SpecifiersSchedulingInformation*Schedule*Specifier每个
*Schedule实体（IntervalSchedule除外）都有相应的集合*Specifier实体（一对多关系）。然后，您可以通过 Table-Per-Hierarchy 继承映射将五个*Schedule实体映射到同一个SchedulingInformation表。这将是我尝试和测试的主要计划。
Yes, I think your table flood will have a negative impact on performance. If
YearlyScheduleand the other stuff are derived entities from the base entitySchedulingInformationand 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 propertyInterval_Ticksto theSchedulingInformationtable.The four
...Specifierstables could all refer then with their foreign keys to theSchedulingInformationtable.So, this would result in:
SchedulingInformationand 4 x*SpecifiersSchedulingInformation*Schedule*SpecifierEach of the
*Scheduleentities (exceptIntervalSchedule) has a collection of the corresponding*Specifierentity (one-to-many relationship). And you map the five*Scheduleentities to the sameSchedulingInformationtable via Table-Per-Hierarchy inheritance mapping.That would be my primary plan to try and test.