安排员工 - 要使用什么数据结构？ [英] Scheduling Employees - what data structure to use?

问题描述

我正在为我的软件开发公司编写一个简单的员工约10-20人的计划软件。经过一些考虑，我决定在Python，Ruby或PHP + Postgres / MySQL DB中编写一个网络应用程序。在设计数据库模型的时候，我开始怀疑什么数据结构对于这种应用程序来说实际上是最好的。

I'm trying to write a simple employee Scheduling software for about 10-20 people in my software development company. After some consideration I settled on writing a web app in Python, Ruby or PHP + Postgres/MySQL DB. While designing database models I began to wonder what data structure would actually be the best for that kind of application.

显示月视图的应用示例与此类似：

Example of app showing the month view would be similar to this:

 OCTOBER    1 2 3 4 5 6 7 8 9 ...
John Apple  M M A A N N O O O ...
Daisy Pear  O O O M M A A N N ...
Steve Cat   A A N N O O O M M ...
Maria Dog   N N O O O M M A A ...

其中M - >为早班班; A - >下午班等（信件可以更改为代码）

where M -> for Morning shift; A -> Afternoon shift etc. (letters can be changed to codes)

什么数据结构或数据库设计是最好的？
我正在考虑为每个用户存储类似于 - >MMAANNOOOAAMMNNAAOO ...的字符串（最多31个字符 - > 1个字符，1天）月表将包含每个员工的这样的字符串。

What data structure or database design would be the best for this? I was thinking about storing strings (max of 31 characters -> 1 char , 1 day) similar to -> "MMAANNOOOAAMMNNAAOO..." for each user; Month table would contain such strings for each employee.

你会建议什么？

推荐答案

p>我会去三桌Kimball明星（Date，Employee，Schedule），因为迟早会被要求创建（要求）报告。谁工作最多的夜晚？谁工作最多的周末？谁永远不会在周末工作？为什么我星期五下午总是预定一周的哪一天是某些员工最有可能不会出现？ etc等...

I would go with three-table Kimball star (Date, Employee, Schedule), because sooner or later you will be asked to create (demanding) reports out of this. Who worked most nights? Who worked most weekends? Who never works weekends? Why am I always scheduled Friday afternoon? On which day of a week are certain employees most likely not to show up? Etc, etc...

表将是：

TABLE dimDate（KeyDate ，FullDate，DayOfWeek，DayNumberInWeek，IsHoliday，...更多在这里）

您可以预先填写dimDate表10年，或许这样可能需要调整IsHoliday列不时。

TABLE dimDate (KeyDate, FullDate, DayOfWeek, DayNumberInWeek, IsHoliday,... more here)
You can pre-fill dimDate table for 10 years, or so -- may need to tweek the "IsHoliday" column from time to time.

员工表也很少变化（相对较少）。
TABLE dimEmployee KeyEmployee，FirstName，LastName，Age，... more here）

Employee table also changes (relatively) rarely.
TABLE dimEmployee (KeyEmployee, FirstName, LastName, Age, ... more here)

日程表是你填写的地方工作时间表，我也提出了每个班次的HoursOfWork，这样可以方便地在报告中聚合小时，例如：John Doe去年在假期工作多少小时？

Schedule table is where you would fill-in the work schedule, I have also suggested "HoursOfWork" for each shift, this way it is easy to aggregate hours in reports, like: "How many hours did John Doe work last year on holidays?"

TABLE factSchedule（
）KeySchedule， - 代理PK
ACK KeyDate， - FK to dimDate表
< keyEmployee， - FK to dimEmployee table
ACK Shift， - shift number（退化维度）

HoursOfWork， - 班次中的工作时数

）

你可以将KeyDate，KeyEmployee和Shift组合在一起，而不是使用代理KeySchedule一个复合主键，以确保您不能在同一天同一班次安排同一人。如果使用代理键，请在应用程序层检查此项。

查询时，如下所示连接表：

Instead of having the surrogate KeySchedule, you could also combine KeyDate, KeyEmployee and Shift into a composite primary key to make sure you can not schedule same person on the same shift the same day. Check this on the application layer if the surrogate key is used.
When querying, join tables like:


SELECT SUM（s.HoursOfWork）

FROM factSchedule AS s
ERY JOIN dimDate AS d ON s.KeyDate = d.KeyDate

JOIN dimEmployee AS e ON s.KeyEmployee = e.KeyEmployee

WHERE

e.FirstName ='John'AND e.LastName ='Doe'
和D .Year = 2009 AND d.IsHoliday ='Yes';

如果使用MySQL，可以使用MyISAM进行存储引擎，并实现外键（FK）为仅逻辑 - 使用应用程序层来处理引用完整性。

If using MySQL it is OK to use MyISAM for storage engine and implement your foreign keys (FK) as "logical only" -- use the application layer to take care of referential integrity.

希望有所帮助。

Hope this helps.

这篇关于安排员工 - 要使用什么数据结构？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！