33140

storing weekly targets in database

Question:

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 advance

Answer1:

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

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.

<ul><li>

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

</li> <li>

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.

</li> <li>

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, StartDate <strong>and</strong> 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.</li> </ol><h2>Response to Comments</h2> <ol><li>

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.

</li> <li>

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.

</li> <li>

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.

Recommend

  • For each in Array PHP
  • How to get the result sets from tsql's execute?
  • find the date, when value of column changed
  • How to replace in values in spark dataframes after recalculations?
  • Trying to execute a spark sql query from a UDF
  • Applying TRIM() in Pig for all fields in a tuple
  • Apache Pig - nested FOREACH over same relation
  • Automatic E-Mail to Admin when Database is Updated
  • Transactional Replication Questions
  • Apache Spark - registering a UDF - returning dataframe
  • Pandas and Python Dataframes and Conditional Shift Function
  • Select all columns except null or NVL all null
  • PHP emboss with color
  • MySQL trigger to update SQL Server table
  • Renaming NativeScript iOS Application
  • How to replace all nonnumeric values?
  • py2exe cannot import Module from other directory
  • filtering DocumentDb collection from Data Factory using unix-timestamp
  • Development oriented PostgreSQL IDE
  • Join 3 tables with Count
  • Manipulating a dataframe within a Spark UDF
  • ORA-02298 Parent Keys Not Found?
  • SUM() data in a column based on another column data
  • Pre-populated SQLite Database not reading properly in Android Studio
  • How to turn (A, B, C) into (AB, AC, BC) with Pig?
  • Deploying a CodeRush plugin from the Community Site
  • How to override value that appears in a dropdown in the rails_admin gem
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Approximate Order-Preserving Huffman Code
  • Grails calculated field in SQL
  • Is possible to count alias result on mysql
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Cannot Parse HTML Data Using Android / JSOUP
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • How do you join a server to an Active Directory (domain)?
  • Getting Messege Twice Using IMvxMessenger
  • How does Linux kernel interrupt the application?
  • Net Present Value in Excel for Grouped Recurring CF
  • How to load view controller without button in storyboard?