storing weekly targets in database

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

This one is really easy in pure Relational modelling terms. I do not see the need for "denormalisation" of any kind.

Sales Data Model.

If you are unfamiliar with the Standard for modelling Relational databases, the IDEF1X Notation may be helpful.

Pure 5NF; full Declarative referential Integrity; no Nulls, no Update Anomalies; no GROUP BYs; pure Date arithmetic.

• The SaleTarget is compared against SaleActual by projection, and may be in the same result set.

• If you have Monthly and Annual Sales accounting, the extension required is a common calendar table with a bit of control or structure; eg. similar to Week, including rows for each Month and Year. Just let me know, and I will update the model.

• I say 5NF because that is the minimum I provide in order to eliminate Update Anomalies, and most modellers are familiar with it. But if it does not scare you off, the two Sales tables are actually Sixth Normal Form.

• This allows full Pivoting (weeks or months across the top; Products or Employees down the side; vice versa; any combination) without temporary tables or complex SQL. (Just ask.)

I think it may even be self-explanatory, but I will supply the Verb Phrases which spell out the Business Rules, only because there are three Parents involved in each:

• Each Employee is scheduled SaleTarget of Product for Week
• Each Product is scheduled SaleTarget By Employee for Week
• Each Employee did SaleActual of Product on Day
• Each Product did SaleActual by Employee on Day

Comparison

1. I should have mentioned. Notice there is no vertical (rows) or horizontal (columns) duplication. When columns are duplicated eg, StartDate and EndDate, you have broken 3NF (introduced Functional Dependencies), and introduced an Update Anomaly. The EndDate in any row, is the StartDate in the next row (that, minus 1 second counts as a dupe, is a contrivance); when updating, now two rows instead of one have to be changed. More important, this structure is so simple (it is not a Time Series, or "temporal" requirement), the EndDate is not required.

Response to Comments

2. The Data Model has been updated to include Month and Year requirements. You now need a Check Constraint on SaleTarget to ensure that DateType is W for week. Loading the Date table is simple, you do not need the nonsense code (manually repeated cut-and-paste) that is posted on SQLTeam; they are famous for being stupid and sub-standard.

3. The SaleActual table now contains Daily, Weekly, Monthly, and Annual values. Which of course, you summarise programmatically on the first day of each Week, Month, Day. First add the new row to Date.

4. 5NF is prety much the minimum required for standard compliance these days, so you need to get used to it. Basically there was a lot of argument among the academics (plus places like Wikipedia posting completely incorrect entries) of the NFs between 3NF and 5NF. The short and sweet definition of 5NF is that it is what 3NF was intended to be, with zero data duplication, zero Update Anomalies (no duplicated columns to be updated transactionally).

5. Forget about 6NF for now. Any table that is in 6NF, is in 5NF (and 4NF and BCNF and 3NF). Just treat the two Sales tables as 5NF. When you have to write a pivoted report, say an year from now, that's when you will realise the value of this structure.

I personally would store the targets and actuals in separate rows, and most probably in separate tables:

Targets: EmployeeId, PeriodId, ProductId, TargetValue

Sales: EmployeeId, PeriodId, ProductId, SalesValue

In fact, in an integrated system, the second table is usually unnecessary (assuming that you have a complete sales recording system, this should be a projection/view of the actual recorded sales - with appropriate assignment of employee, period and product based on the model of that subsystem).

In order to fit your calendar requirements, I would almost certainly have a date table which will allow you to ensure all your various business rules for definitions of weeks and months without complex date logic. Determining periods and aggregating is then just facilitated with joins to the calendar table.

So the ActualSales would look something like this (with just a generic Period table, which might itself be a period and date table):

SELECT sp.EmployeeId
       , p.ProductId
       , pd.PeriodType
       , pd.PeriodId
       , SUM(id.Quantity * id.UnitProce) AS TotalSales
FROM Invoice AS i
INNER JOIN InvoiceDetail AS id
    ON id.InvoiceId = i.InvoiceId
INNER JOIN Employee AS sp
    ON sp.EmployeeId = i.SalesPersonId
INNER JOIN Product AS p
    ON id.ProductId = p.ProductId
INNER JOIN Period AS pd
    ON pd.StartDate <= i.InvoiceDate
    AND pd.EndDate > i.InvoiceDate
GROUP BY sp.EmployeeId, p.ProductId, pd.PeriodType, pd.PeriodId

In this case, data would be duplicated if you had overlapping periods (like daily, weekly, monthly), so you would need to aggregate ONLY one type of period - that's why I've specifically included it in this example view although it's redundant here.

I expect a generic Period table would look like:

PeriodId
PeriodType
StartDate
EndDate

This would be prepopulated with the various periods you want to report on:

'Q', 1/1/2010, 4/1/2010
'M', 1/1/2010, 2/1/2010
'M', 2/1/2010, 3/1/2010
'M', 3/1/2010, 4/1/2010
'W', 1/3/2010, 1/10/2010
'W', 1/10/2010, 1/17/2010
etc.
'D', 1/1/2010, 1/2/2010
'D', 1/2/2010, 1/3/2010
etc.

It makes very little sense to worry about holidays except that you probably aren't going to assign a target if they aren't working and this is mainly about managing the assignments so that they are presumably realistic. You can have a calendar table of days with various flags

Calendar
DateId
Date
IsHoliday

Then you can include that when you join to count the number of holidays/weekends in a period etc.

This is typically an accounting/business thing, but you may want to look into standardizing your calendar. For instance, in media buys for TV advertising, they make each "quarter" equal and make each "month" standardized - 4 weeks, 4 weeks, 5 weeks. Obviously they make exceptions for holiday and special TV events, but this helps to smooth out the accounting and compare like periods more easily.

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).