Smokes, Whiskey and .NET

ritSegessager · Apr 28, 2012

Hello everyone, I am quite new to the wonderful world of database design, and I would be most appreciative if any gurus in this field could point me in the right direction. I apologize in advance for the somewhat fanciful scenario, but it is the only way I can make my problem clear. Suppose that there is a company, "Good Kid Industries", that wants to create a website using the .Net framework. They have a set of rules that defines an international standard "The Good Kid Standard". This standard can be applied to any child under 10 years old. The rules can be summarized as follows: 1) No kid shall consume more than 20 cookies for any 12 day period. Realizing that these are bleak modern times they also declare: 2) No kid shall smoke more than 7 cigarettes for any 6 day period. and 3) No kid shall drink more than 40 oz of whisky for any 10 day period. Now Good Kid Industries has many, many parents who want to partake in the program. Each parent enters in their child's activities on an hourly basis for the day (for the sake of this example the parent's are omniscient when it comes to their children's activities ... hey, I told you it was a fanciful example). The day is broken up into hours. This data is stored in a database and consists of the child's name, and how many cookies, cigarettes and whisky they consumed each hour. Now, my question. When it comes to designing the website and database, what is the most efficient way to determine which children have run afoul of the "Good Kid Standard". It would be nice if for each kid we could cut up the logs into 12, 10 and 6 day chunks and just query how many cookies, drinks or cigarettes respectively they have consumed in this period. But this won't work since the standard is for ANY 12, 10 and 6 day period. Meaning if we cut it into chunks, if Johnny had 18 cookies just before the 12 day period ended and 10 cookies the very next day, he would have consumed 28 cookies in 2 days. Clearly he has failed to be a "Good Kid", but if we were just checking every 12 day block he would pass since in the first block he would have consumed 18 cookies and in the next only 10. So it seems that in order to determine compliance, the webserver has to drag out all of the records for a particular period. Then for every single HOUR of that period, go back 12 days to that hour and determine the cookies consumed. THEN for every single hour of that period go back 10 days to the hour and determine the amount of whisky consumed. THEN for every single hour of that period go back 6 days to the hour and determine the number of cigarettes consumed. This seems to be incredibly costly in processor work for the server, especially if several parents want to determine for a period of months whether or not their child meets the "Good Kid Standard". I am sure I must be looking at the solution to this problem the wrong way. Can anyone give me any pointers here? Thanks in advance Dan First I'm not sure that my answer makes sense Second I feel like I'm being asked to do your homework. Do you need help with something or would you like someone just to engineer a product for you for free? Third this question has nothing to do with ASP, Databases or .Net. Finally, the best solution to your problem IS to break it up into 12, 10 and 6 day chunks. Oh, I know, you say you can't..because if I have 12 days, is it days 1,2,3,4,5,6 or days 2,3,4,5,6,7 or days 3,4,5,6,7,8...but I think you can. Determine whether a child has been naugthy or not each day and store the results...VOILA..i believe that's your solution. In other words, store every possible result. example: Parent enters information for child 12 times in the day (n) (I'll assume parents are only doing this 12 times a day). When 1am roles by, determine if child is naught by looking look at days n-1, n-2, n-3, n-4, n-5 and n-6. Store in database @from_date = n-6, @today = n, @isNaught = ? While you still have a range, your upper bound is defined, thus solving your problem. I should also point out that you'd be surprised at how fast TSQL will execute on MS SQL. Even if parents entered information 24 times a day, that's still only about 10 000 loops, which you could probably cut down with some fancier SQL. I don't know about you, but my computer can run 10 000 loops quite easily. If this is for a class, the lesson is simple. If you think something is too processor intensive, the solution is often hard drive room. The trick is finding the balance which will be the least expensive. Since 6 day rules are harder to calculate, maybe you should store all their information, while dynamically calculating 10 and 12 day rules. I might be totally off....I might be indeed... Karleither you are asking us to: a) do your homework project b) do your project for work (with cleverly re-named entities of course) my suggestion would be to do it yourself. if i made some terribly wrong assumptions then i apologize in advance.Thanks for your reply Karl, I can see how this would seem like a homework example, but I assure you it isn't. I just renamed my present business problem and was posting it not so someone could write it for me but rather a) Whether my concerns over processor time, particularly on a web server were valid and b) If my approach to the problem was completely off base, if someone could nudge me in the direction of the right approach. At the risk of sounding (even more) like a dunderhead you suggest: Since 6 day rules are harder to calculate, maybe you should store all their information, while dynamically calculating 10 and 12 day rules. Q1: Why are 6 day rules harder to calculate than 10 or 12 day rules? Q2: What exactly do you mean by storing all of the information for 6 day rules? DanBecause there are more calculations to be made for 6 day rules. Simple example: you want to look at it over a 13 day span. With a 6 day rule, it could be any of 7 possibilities..whereas a 12 day rule would only have 2 possibilities. 1,2,3,4,5,6 2,3,4,5,6,7 3,4,5,6,7,8 4,5,6,7,8,9 5,6,7,8,9,10 6,7,8,9,10,11 7,8,9,10,11,12 8,9,10,11,12,13 or 1,2,3,4,5,6,7,8,9,10,11,12 2,3,4,5,6,7,8,9,10,11,12,13 so if you are trying to achieve a balance between processor and storage usage, you might consider storing all posibility for 6 day rules on a per day basic, while dynamically calculating 12 day rules on request. More precisly what I mean about storing all the information, is that each day, you store start=1,end=6, wasBad= ? start=2,end=7, wasBad=? start=3,end=8, wasBad=? start=4,end=9, wasBad=? start=5,end=10, wasBad=? start=6,end=11, wasBad=? start=7,end=12, wasBad=? start=8,end=13, wasBad=? which would allow you to quickly determine whether a child was bad or not. You may even find that reversing that logic may yield a better ratio...in you'll be storing less, and you'll be doing just a bit more calculations. Hope that clears it up. Karl Thanks very much Karl! Your answers really helped! Dan

Smokes, Whiskey and .NET

ritSegessager

New Member