哪个是跟踪工具的更好的数据库架构？

I have to generate a view that shows tracking across each month. The ultimate view will be something like this:

我必须生成一个显示每月跟踪的视图。最终的观点将是这样的:

| Person | Task | Jan | Feb | Mar| Apr | May | June . . .
| Joe | Roof Work | 100% | 50% | 50% | 25% |
| Joe | Basement Work | 0% | 50% | 50% | 75% |
| Tom | Basement Work | 100% | 100% | 100% | 100% |

|人|任务| Jan |二月|三月|四月|五月|六月。。 |乔|屋顶工作| 100%| 50%| 50%| 25%| |乔|地下室工作| 0%| 50%| 50%| 75%| |汤姆|地下室工作| 100%| 100%| 100%| 100%|

I already have the following tables:

我已经有以下表格:

Person
Task

I am now creating a new table to foreign key into the above 2 tables and i am trying to figure out the pros and cons of creating 1 or 2 tables.

我现在正在创建一个新表到外键到上面的两个表,我试图找出创建1或2个表的利弊。

Option 1:

Create a new table with the following Columns:

使用以下列创建一个新表:

Id
PersonId
TaskId
Jan2012
Feb2012
Mar2012
Apr2013

Option 2:

have 2 seperate tables

有2个单独的表

One table for just

只有一张桌子

Id
PersonId
TaskId

and another table for just the following columns

和另一个表只有以下列

Id
PersonTaskId (the id from table above)

PersonTaskId(上表中的id)

MonthYearKey
MonthYearValue

So an example record would be

所以一个例子记录就是

| 1 | 13 | Jan2011 | 100% |

| 1 | 13 | 2011年1月| 100%|

where 13 would represent a specific unique Person and Task combination. This second way would avoid having to create new columns to continue over time (which seems right) but i also want to avoid overkill.

其中13表示特定的唯一人员和任务组合。第二种方法将避免不得不创建新列以继续(似乎正确)但我也想避免矫枉过正。

which would be a more scalable way to have this schema. Also, any other suggestions or more elegant ways of doing this would be great as well?

这将是一种更具可扩展性的方式来拥有此架构。此外,任何其他建议或更优雅的方式这样做也会很棒?

2 个解决方案

#1

You can have a m2m table with data columns. I don't see a reason why you can't just put MonthYearKey, MonthYearValue on the same table with PersonId and TaskId

您可以拥有一个包含数据列的m2m表。我没有看到为什么你不能将MonthYearKey,MonthYearValue与PersonId和TaskId放在同一个表上的原因

Id
TaskId
PersonId
MonthYearKey
MonthYearValue

It's possible too that you would want to move the MonthYearKey out into their own table, it really just comes down to common queries and what this data is used for.

你也可能希望将MonthYearKey移到他们自己的表中,它实际上只归结为常见查询以及这些数据的用途。

I would note, you never want to design a schema where you are adding columns due to time. The first option would require maintenance all the time, and would become very difficult to query also.

我会注意到,您永远不想设计一个由于时间而添加列的模式。第一个选项需要一直维护,并且也很难查询。

#2

Option 2 is definitely more scalable and is not overkill.

选项2肯定更具可扩展性并且不会过度。

Option 1 would require you to add a new column every month and simple date based queries of your data would not be possible, e.g. Show me all people who worked at least 90% in any month last year.

选项1要求您每月添加一个新列,并且无法对数据进行基于日期的简单查询,例如:告诉我去年任何一个月至少工作90%的人。

The ultimate view would be generated from a particular query or view of your data.

最终视图将从特定查询或数据视图生成。

#1