tag:blogger.com,1999:blog-375697951860081841.post29975498318993336..comments2008-08-25T17:55:37.141+01:00Jonathanhttp://www.blogger.com/profile/17336586681878234499noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-375697951860081841.post-49996728563473688732008-08-25T17:55:00.000+01:002008-08-25T17:55:00.000+01:00Since even one complaint, regardless of the day, negates the bonus, you could probably just have a single complaint flag on the employee entry, which is set to true as soon as the first complaint of the month comes in. Then you only need to check against that rather than against each individual day.<BR/><BR/>Similarly, you might implement counters for &quot;Days over $300&quot; and &quot;Days late&quot;. Instead of compiling them one by one and then summing them at the end, you just add one to the counter fields. Then your query for a bonus becomes a simple:<BR/><BR/>SELECT * FROM employee<BR/>WHERE greater_than_avg &gt;= 15<BR/>AND days_late &lt; 5<BR/>AND complaints = 0<BR/><BR/>This, of course, assumes that you don&#39;t need the specific days that things happened for other record-keeping, but even if you do store them elsewhere, this touch of de-normalization can increase performance a bit (as long as you have a LOT of employees... otherwise the difference will probably be negligible).<BR/><BR/>That said, I am inclined to agree with Roland that this is probably a bit more specific business logic than you want in the database.Joe Izenmanhttp://www.sitecrafting.comnoreply@blogger.comtag:blogger.com,1999:blog-375697951860081841.post-15559578139055398372008-08-25T17:52:00.000+01:002008-08-25T17:52:00.000+01:00Hi, your solution looks a bit like a star schema. You should read about Dimensional Modeling (DM) which is a data-mining paradigm led by Ralph Kimball. I haven't read enough about it but I think it may be applicable to this sort of problem. <BR/><BR/>See also:<BR/>"A Dimensional Modeling Manifesto"<BR/>http://www.dbmsmag.com/9708d15.html<BR/><BR/>"The Data Warehouse Toolkit" by Ralph Kimball and Margy Ross<BR/>http://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247/Bill Karwinhttp://www.blogger.com/profile/13004667086865377598noreply@blogger.comtag:blogger.com,1999:blog-375697951860081841.post-74278299434138680362008-08-25T10:33:00.000+01:002008-08-25T10:33:00.000+01:00Hey Roland<BR/><BR/>Yes, there would be a cost anyway to calculate it and I should have mentioned it in the post. My point was that it would be a one-time pre-paid cost.<BR/>* One-time meaning - if 3 different managers want to see the data (lets say sales, accounting and human resource), then you wouldn't need to process it again.<BR/>* Pre-paid meaning - you would process it before you would ask to see it, probably at the same time you inserted the data. If the time to insert then becomes unbearable, then this solution would be counter-productive.<BR/><BR/>It very much depends on how you use the data. If you insert rarely and view often, then this would benefit you more.<BR/><BR/>Regarding storing conditions, in my example I meant to use "sold more then average" which is more flexible. I will make the change now.<BR/><BR/>As always Roland, I appreciate your feedback.<BR/><BR/>Best Regards<BR/>JonathanJonathanhttp://www.blogger.com/profile/17336586681878234499noreply@blogger.comtag:blogger.com,1999:blog-375697951860081841.post-59113200727522947092008-08-25T08:38:00.000+01:002008-08-25T08:38:00.000+01:00Hi!<BR/><BR/>"...Then whenever you want to see the results of who will get bonus, you can query that table. This could save you that processing and waiting time we talked about earlier.<BR/><BR/>Depending on how fast you would like your response to be when generating that list, you could use this method which does take up space but saves on processing time..."<BR/><BR/>Well, wouldn't you have to take the cost of generating that list into account? So that at any rate, the consideration to put the conditions into the database would not save any costs, merely change the moment when the cost is suffered?<BR/><BR/>Personally, I would only store conditions for business rules into the database if I would want the flexibility of changing the rules. And if I would do it, I would probably only store the raw constants that drive the logic, not the outcome. If you precalculate the outcome of the sub-rules ("earned 300$ or more", "less than 5 times late", "0 complaints") then you lose any hope of calculating the bonus in case the rules changed. For example, if you decide to change the rule "earned 300$ or more" to "earned 350$ or more" then you will have to either update the flags for the last month, or settle for unjustly shelling out the bonus. If on the other hand you store the amount that must be earned ("300" and then "350") than any calculation: "earned ${bonus-earning} or more" will still work regardless of the actual value.Roland Boumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.com