建议数据库表设计，估计表

I am new to database design (have read a couple of tutorials though).

我是数据库设计的新手（尽管已阅读了几本教程）。

In my program I need to store records of time estimates. An estimate for a workpackage is consist of estimates in hours for 36 timeperiods (month).

在我的程序中，我需要存储时间估计的记录。工作包的估计值包括36个时段（月）的小时估计值。

My plan is to have the following columns:

我的计划是拥有以下列：

• id INTEGER (Primary key)
• id INTEGER（主键）
• workpackageId INTEGER (Foreign key)
• workpackageId INTEGER（外键）
• competendId INTEGER (Foreign key)
• compendId INTEGER（外键）
• M36 SMALLINT UNSIGNED
• M36 SMALLINT UNSIGNED
• M35 SMALLINT UNSIGNED
• M35 SMALLINT UNSIGNED
• M34 SMALLINT UNSIGNED
• M34 SMALLINT UNSIGNED
• .....
• .....
• M1 SMALLINT UNSIGNED
• M1 SMALLINT未签名
• created DATETIME
• 创建了DATETIME
• modified DATETIME
• 修改了DATETIME

M1 to M36 is actually estimations of work time for a period of one month. A workpackage that can be implemented in one calender month time will only have estimates in the M1 column. A workpackage that would at a minimum require 6 calender month will have estimates in the M1-M6 columns.

M1至M36实际上是对一个月的工作时间的估计。可以在一个日历月份时间内实施的工作包仅在M1列中具有估计值。一个至少需要6个月日历的工作包将在M1-M6栏中进行估算。

I might need to get even more data in there and add separat values for MIN (LIKELY) MAX estimates seperately for each timeperiod.

我可能需要在那里获得更多数据，并为每个时间段单独添加MIN（LIKELY）MAX估计值的分离值。

I am sure there is a smarter way to do this but I dont know how...

我相信有更聪明的方法可以做到这一点，但我不知道如何......

Any suggestions would be greatly appriciated...

任何建议都会受到很大的关注......

Best regards // Claes

最好的问候// Claes

2 个解决方案

#1

This would be my approach

这将是我的方法

Table Jobs
JobID -- Don't just use ID
workpackageId 
competendId 
CreatedOn
ModifiedOn


Table TimePeriod
TimePeriodID
TimePeriodDesc -- this is your M1, M2, etc

Table Times
TimeID
JobID  -- fk to Jobs
TimePeriodID -- fk to TimePeriod
EstimateType -- Min, Max, etc
EstimateValue INT -- pop your estimate in here
CreatedOn  -- No modify, just use the most recent created for each to allow audit history

#2

I guess M1-26 are some estimations... You might want to extract it to another table, which will give you possibility to be more adjustable on that.

我猜M1-26是一些估计...你可能想把它提取到另一个表，这将使你有可能更加可调。

See this example to get what I'm talking about and adjust it to your needs:

请参阅此示例以获取我正在谈论的内容并根据您的需求进行调整：

Table WRKPKGS

• id INTEGER (Primary key)
• id INTEGER（主键）
• workpackageId INTEGER (Foreign key)
• workpackageId INTEGER（外键）
• competendId INTEGER (Foreign key)
• compendId INTEGER（外键）
• wrk_time_id INTEGER (Foreign key)
• wrk_time_id INTEGER（外键）

Table TIMEPERIODS

• id INTEGER (Primary key)
• id INTEGER（主键）
• name VARCHAR
• 名称VARCHAR
• estimated_time SMALLINT UNSIGNED
• estimated_time SMALLINT UNSIGNED

Table TIME_WRKPKGS

• id (Primary key)
• id（主键）
• id_time
• id_time
• id_wrkpkg
• id_wrkpkg

This is many to many relation which clarifies your database schema. Now when you will want to add for example M20 estimation you will need to do the following:

这是许多关系，它阐明了您的数据库架构。现在，当您想要添加例如M20估算时，您需要执行以下操作：

1. Add job to WRKPKGS (remember id)
2. 添加工作到WRKPKGS（记住id）
3. Add TimePeriod with name M20 (remember id)
4. 添加名称为M20的TimePeriod（记住id）
5. Add linking record in TIME_WRKPKGS (use ids generated above)
6. 在TIME_WRKPKGS中添加链接记录（使用上面生成的ID）

And you have what you want. Now modifying this is easy - if you want to add min/max estimations, you just extend the Timeperiods table and you're done

你有自己想要的东西。现在修改这个很容易 - 如果你想添加最小/最大估计，你只需要扩展Timeperiods表就可以了