Thursday, March 8, 2012

Best Practice

Hi all,
We have a claims table in out database.
It has A child table which would hold different financial ammount with
regards to the claim loss, eg material dammage, third party, towing etc.
Each catergory of loss is a new row.
I am trying to see which is the best way of showing and storing the total
loss, whihc would be a combined summ of loss types for that particular
claim.
Should I summ theses amounts and store it into a column (perhaps TotalLoss)
of the parent (Claims) table.
Or should I be looking at another stratergy
Thanks
RobertUnless you have a performance issue, I would not store the calculation at
all. Compute it when needed in a select statement.
If you do have a performance problem and have exhausted all other approaches
to fix it, then you can store the sum in the claims table. You would then
have to make sure that it was always correct by using triggers to
re-claculate it every time a change was made to one of the loss rows.
"Robert Bravery" <me@.u.com> wrote in message
news:%23CZkw0KKGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> We have a claims table in out database.
> It has A child table which would hold different financial ammount with
> regards to the claim loss, eg material dammage, third party, towing etc.
> Each catergory of loss is a new row.
> I am trying to see which is the best way of showing and storing the total
> loss, whihc would be a combined summ of loss types for that particular
> claim.
> Should I summ theses amounts and store it into a column (perhaps
> TotalLoss)
> of the parent (Claims) table.
> Or should I be looking at another stratergy
> Thanks
> Robert
>|||Hi Dave,
Thanks For the response
At the moment I don't have any performance issues, yet.
I have thought of the trigger option, but as I understand, the best option
is to compute it when needed.
Thanks
Robert
"Dave Frommer" <anti@.spam.com> wrote in message
news:OdQViULKGHA.668@.TK2MSFTNGP11.phx.gbl...
> Unless you have a performance issue, I would not store the calculation at
> all. Compute it when needed in a select statement.
> If you do have a performance problem and have exhausted all other
approaches
> to fix it, then you can store the sum in the claims table. You would then
> have to make sure that it was always correct by using triggers to
> re-claculate it every time a change was made to one of the loss rows.
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:%23CZkw0KKGHA.2040@.TK2MSFTNGP14.phx.gbl...
total
>|||The only reason I see for storing a total at the claim level would be if
there was a concept of "loss reserve" or that amount which has been
allocated toward a claim. You can implement a 'view' that joins the claim
table with the claim line table and sums a total for all coverages.
"Robert Bravery" <me@.u.com> wrote in message
news:%23CZkw0KKGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> We have a claims table in out database.
> It has A child table which would hold different financial ammount with
> regards to the claim loss, eg material dammage, third party, towing etc.
> Each catergory of loss is a new row.
> I am trying to see which is the best way of showing and storing the total
> loss, whihc would be a combined summ of loss types for that particular
> claim.
> Should I summ theses amounts and store it into a column (perhaps
> TotalLoss)
> of the parent (Claims) table.
> Or should I be looking at another stratergy
> Thanks
> Robert
>|||HI JT,
Yes youre right in some aspect. I would probably store the first estimate,
but that would be an only one first time thing
The rest of the time, I suppose could be done via a proper formated select
statement.
Thanks for the help in clearing the mind
Robert
"JT" <someone@.microsoft.com> wrote in message
news:u5QMYxMKGHA.1032@.TK2MSFTNGP11.phx.gbl...
> The only reason I see for storing a total at the claim level would be if
> there was a concept of "loss reserve" or that amount which has been
> allocated toward a claim. You can implement a 'view' that joins the claim
> table with the claim line table and sums a total for all coverages.
> "Robert Bravery" <me@.u.com> wrote in message
> news:%23CZkw0KKGHA.2040@.TK2MSFTNGP14.phx.gbl...
total
>

No comments:

Post a Comment