在数据库中存储每周目标 [英] storing weekly targets in database

问题描述

我有以下要求

 销售主管：Bob 
 
 Week1 Week2 Week3 ... .............. Week52 
 Prod1 10 15 12 ................. 14 
 Prod2 20 14 10。 ................ 17 
。 。 
。 。 
。 。 
  

销售主管将每周设置每位销售人员的目标。
销售人员可以通过与设定目标相似的网格，每天输入每个产品的实际销售额，例如

编辑

在上述案例主管已经设置10个单位的目标，第1周现在，销售官将每天进入销售1,2,0,1,3,2 = 9（实际销售第1周），所以针对10单位的目标他在第一周销售了9个单位。


我已经创建了员工和产品表。任何人都可以指导关于如何在数据库中存储天和周的最佳实践，并且可以记录目标的实际销售额。

我认为将数据存储在下表中

  EmpSales（EmployeeID，ProductID，SaleTarget，Actual Sale，Date，WeekNo，Month）
  

提前感谢

期间（periodId，startDate，endDate ，weekNo，Month，Year）

，然后添加

empSales（EmployeeId，ProductId，SaleTarget，ActualSale， periodId）。

这有点更灵活（您可以轻松地引入不同的时间跨度，并使相对周期字段为空，或定义一些规则，一个标准周），有较少的冗余（注意如何将月份和星期从empSales表中移除），它允许您进行报告和计算（btw，您没有包括年份字段，是有一个原因？）

统计资料应该更容易，因为假设您按天排序销售，那么在间隔之间加总更容易，除非你想复制周字段。

请注意，您可以在不同的重叠时段轻松地创建目标。

例如，您可以为11月22-28日设定每周目标（我使用的是星期一开始的欧洲惯例）

因此：

 期间：
 
 periodId | startDate | endDate | weekNo | Month |年| 
 0030020 | 22-NOV-2010 | 28-NOV-2010 | 43 | November | 2010 | 
 0030026 | 26-NOV-2010 | 26-NOV-2010 | null | November | 2010 | 
 
 empSales：
 
 EmployeeId | ProductId | SaleTarget | ActualSale | periodId | 
 567689 | 788585 | 58 | 42 | 0030020 | 
 567689 | 788585 | 28 | 32 | 0030026 | 
  

请注意，员工567689错过了他的每周目标，但成功地超过了他的黑色星期五目标。 >

Btw，在这个例子中，我想你最好删除empSales表，将其重命名为empTargets：

empTargets（EmployeeId，ProductId，SaleTarget，periodId）。

因为实际销售很容易通过UDF计算或放置在视图中，它只是一个

  select sum（items_sold）
 from sales 
其中sales.employeeId = empTargets。 employeeId和
 sales.ProductId = empTargets.ProductId和
在empTargets.startDate和
之间的sales.saleDate empTargets.endDate）
  

因此无需将其直接存储在表中（事实上，如果返回的项目或其他未来更正，它可能会成为一个负担）。

i have the following requirement

Sales Officer: Bob

             Week1 Week2  Week3 ................. Week52
    Prod1     10    15    12    .................    14
    Prod2     20    14    10    .................    17
    .                                                 .   
    .                                                 .
    .                                                 .

Sales supervisor will set the targets for each sales officer on weekly basis. Sales officer may enter actual sales on daily basis for each product through a similar grid against the set targets e.g.
Edit
In the above case Supervisor has set target of 10 units for week 1 Now the sales Officer will enter the sales on daily basis as 1,2,0,1,3,2=9(Actual Sale for Week 1) so against the target of 10 unit he has sold 9 units in week one.

I have already created Employee and Product tables. Can any one guide about the best practice about how to store days and weeks in database against which the targets are stored and actual sales can be recorded.

I am thinking storing data in following table

EmpSales (EmployeeID,ProductID,SaleTarget,Actual Sale,Date,WeekNo,Month)

Thanks in advance

解决方案

Personally I would go for a more generic "period" table.

period(periodId,startDate,endDate,weekNo,Month,Year)

and then add

empSales(EmployeeId,ProductId,SaleTarget,ActualSale,periodId).

This is a bit more flexible (you can easily introduce different time spans and either make the relative week field null, or define some rule that maps the period on a "standard" week), there is less redundancy (note how the month and week have been moved away from the empSales table) and it allows you to do reporting and calculation (btw, you didn't include a Year field, is there a reason?).

Tallying up stuff should be easier, because assuming you have sales sorted by day, summing these up between intervals is easier unless you want to duplicate the "week" field all over the DB.

Note also that you can easily have targets on different, overlapping periods.

Example, you can set a weekly target for week 22-28 November (I am using the European convention of having the week start on monday) and have a special one-day period set on Black Friday

So:

Period:  

 periodId|startDate  |endDate    |weekNo|Month    |  Year|
  0030020|22-NOV-2010|28-NOV-2010|  43  |November | 2010 |
  0030026|26-NOV-2010|26-NOV-2010| null |November | 2010 |

empSales:

 EmployeeId|ProductId|SaleTarget|ActualSale|periodId|
     567689|   788585|      58  |       42 | 0030020|
     567689|   788585|      28  |       32 | 0030026|

Note how Employee 567689 missed his weekly target but managed to go over his Black Friday target.

Btw, while working on this example I think you better drop the "empSales" table, renaming it to "empTargets":

empTargets(EmployeeId,ProductId,SaleTarget,periodId).

because the Actual Sales is easily calculated on the fly either with a UDF or placed in a view - after all, it's just a

select sum(items_sold) 
from sales 
where sales.employeeId = empTargets.employeeId and 
      sales.ProductId= empTargets.ProductId and 
      sales.saleDate between empTargets.startDate and 
                             empTargets.endDate)

so no need to store it directly in the table (in fact it could become a burden in case of returned items or other future corrections).

这篇关于在数据库中存储每周目标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！