Normalisation vs If it works just do it!

Hi All,

As an accomplished web devver of many years using ASP and ASP.NET in conjunction with Access and SQL Server, I am a bit pedantic on the rules of good data structures.

Specifically the two main rules of data redundancy and normalisation.

The latter dictates at the lowest level that a data table should NOT contain a field that can be gleaned from one or a combination of others.

I have a problem with this now, I am building a betting system which will take the odds given, plus the stake placed and calculate the winnings or losses accordingly.

There is an added complication in that not all profit is calculated the same way, as a horse can also be 'placed' which does the same calculation as for profit, but then quarters it, so one single select statement won't do.

I could calculate this at data entry stage on a per entry basis and simply store in a Profit/Loss field and keep the value for each bet, however I know this is not the correct thing to do!

My other alternative [and the correct method] is to do this calculation at data request time, but that would involve the use of a cursor or loop in the SP.

I am aware of the huge resources a cursor can consume and I am not sure which is worse, using a cursor or ignoring the normalisation procedures.

So the question is this, what would you do here?

Since I may not be the same SQL Server expert as I am a programmer, is there an alternative way of reading all the bets and doing these calcs on SQL server and bang them back to ASP as a self contained recordset with all the profit/losses calculated for each bet?

Each bet as a unique EntryID and there is a field called Result which stores 'Win, Place or Loss' accordingly.

Thanks in advance of any help/opinions.

:)I'm a bit confused... When I use the term "place" to describe a horse racing bet, I mean that I expect (and am wagering that) the horse will finish second. While it is unusual to do, you can "box" a single horse, meaning that you expect them to place in the top 3, but you retrieve your original wager plus twenty percent of what the return would have been for a correct bet (win, place, or show), which sounds something like your description of a "place" bet.

If you can describe what you really want, I'm sure that someone here on the forum can show you how to code it!

-PatP|||Hi,

Thanks for the response, the actual calculations is not the issue e.g. the odds for a place bet, that is already known.

The problem concerns how to read through the table of placed bets while calculating the profit/loss as we iterate through each record.

The arithmetic is not the issue here, its the method of retrieval so that each record will have a field containing a profit or loss amount, put there by SQL as it reads through each item.

Thanks.|||Performance is the key in my work. If running the cursor is going to slow down the operation then having a little redundency isn't bad.|||My thought would be to do an UPDATE using the CASE to control which computation you use (main or place). Clean, fast, no cursor... What more could you ask?

-PatP