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.<br /><strong>Edit</strong><br /> 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 <strong>days</strong> and <strong>weeks</strong> 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 advanceAnswer1:
Personally I would go for a more generic "period" table.
and then add
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
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":
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).Answer2:
This one is really easy in pure Relational modelling terms. I do not see the need for "denormalisation" of any kind.
<a href="http://www.softwaregems.com.au/Documents/Student%20Resolutions/Tassadaque%20Sales.pdf" rel="nofollow"><strong>Sales Data Model</strong></a>.
If you are unfamiliar with the Standard for modelling Relational databases, the <a href="http://www.softwaregems.com.au/Documents/Documentary%20Examples/IDEF1X%20Notation.pdf" rel="nofollow"><strong>IDEF1X Notation</strong></a> may be helpful.
Pure 5NF; full Declarative referential Integrity; no Nulls, no Update Anomalies; no
GROUP BYs; pure Date arithmetic.
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 <strong>Sixth Normal Form</strong>.</li> <li>
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.)</li> </ul>
I think it may even be self-explanatory, but I will supply the <strong>Verb Phrases</strong> which spell out the Business Rules, only because there are three Parents involved in each:<ul><li>Each Employee is scheduled SaleTarget of Product for Week</li> <li>Each Product is scheduled SaleTarget By Employee for Week</li> <li>Each Employee did SaleActual of Product on Day</li> <li>Each Product did SaleActual by Employee on Day</li> </ul>
Enjoy.<h2>Comparison</h2> <ol><li>I should have mentioned. Notice there is no vertical (rows) or horizontal (columns) duplication. When columns are duplicated eg,
EndDate, you have broken 3NF (introduced Functional Dependencies), and introduced an Update Anomaly. The
EndDatein any row, is the
StartDatein 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
EndDateis not required.</li> </ol><h2>Response to Comments</h2> <ol><li>
The Data Model has been updated to include
Year requirements. You now need a Check Constraint on SaleTarget to ensure that
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.
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
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 cesspits like wiki posting completey 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).</li> <li>
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. Donate to my favourite charity or feed a homeless person.</li> </ol>Answer3:
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.