Database design  
Author Message
BeroniInf





PostPosted: Fri Jan 11 09:08:22 CST 2008 Top

SQL Server Developer >> Database design

This is a multi-part message in MIME format.

------=_NextPart_000_000E_01C854A3.6A856A70
Content-Type: text/plain;
charset="big5"
Content-Transfer-Encoding: quoted-printable

Dear All

I have a question on the database design, please help me.
I am trying to design a database for a ERP system and I responsible on =
the sales invoice module.

Now, there have a report, which need to display the payment overdue, of =
each customer.=20

However, if I sum all payment transaction of sales order (because a =
order can be settled with multi payment) and substracted with the total =
amount of sales order, the processing time will be taken longer, on the =
other hand, if i prepare a field, which store the settled amount of a =
sales order in it's table, it will take less processing time, but if =
there have anything wrong when updating that field, the value of that =
field will be different when I sum all payment transaction.

If you were me, what will you do?

Thanks
------=_NextPart_000_000E_01C854A3.6A856A70
Content-Type: text/html;
charset="big5"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dbig5">
<META content=3D"MSHTML 6.00.6000.16587" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>Dear All</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>I have a question on the database design, please =
help=20
me.</FONT></DIV>
<DIV><FONT size=3D2>I am trying to design a database for a ERP system =
and I=20
responsible on the sales invoice module.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Now,&nbsp;there&nbsp;have a report, which need to =
display=20
the&nbsp;payment overdue, of each customer. </FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>However, if I sum all payment transaction of sales =
order=20
(because a order can be settled with multi payment) and substracted with =
the=20
total amount of sales order, the processing time will be taken longer, =
on the=20
other hand, if i prepare a field, which store the settled amount of a =
sales=20
order in it's table, it will take less processing time, but if there =
have=20
anything wrong when updating that field, the value of that field will be =

different when I sum all payment transaction.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>If you were me, what will you do?</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Thanks</FONT></DIV></BODY></HTML>

------=_NextPart_000_000E_01C854A3.6A856A70--

SQL Server54  
 
 
Tom





PostPosted: Fri Jan 11 09:08:22 CST 2008 Top

SQL Server Developer >> Database design It really depends on what the priorities are and how much you can tolerate
the disadvantages of one vs. the other. You should be able to set up a
prototype with a representative amount of data and then load test it. I'd
start with doing the summing of payments and subtracting those from the
order total. If that's good, then you don't have to worry about data
integrity issues. However, if performance is suffering, then you can try
the approach of updating amount paid. That bit can be done via a trigger.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau




Dear All

I have a question on the database design, please help me.
I am trying to design a database for a ERP system and I responsible on the
sales invoice module.

Now, there have a report, which need to display the payment overdue, of each
customer.

However, if I sum all payment transaction of sales order (because a order
can be settled with multi payment) and substracted with the total amount of
sales order, the processing time will be taken longer, on the other hand, if
i prepare a field, which store the settled amount of a sales order in it's
table, it will take less processing time, but if there have anything wrong
when updating that field, the value of that field will be different when I
sum all payment transaction.

If you were me, what will you do?

Thanks

 
 
ºa¤Ö





PostPosted: Fri Jan 11 09:21:26 CST 2008 Top

SQL Server Developer >> Database design yes, that's good, I forgotten I can use trigger to update the amount paid
value.

Thanks Man~


> It really depends on what the priorities are and how much you can tolerate
> the disadvantages of one vs. the other. You should be able to set up a
> prototype with a representative amount of data and then load test it. I'd
> start with doing the summing of payments and subtracting those from the
> order total. If that's good, then you don't have to worry about data
> integrity issues. However, if performance is suffering, then you can try
> the approach of updating amount paid. That bit can be done via a trigger.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>


> Dear All
>
> I have a question on the database design, please help me.
> I am trying to design a database for a ERP system and I responsible on the
> sales invoice module.
>
> Now, there have a report, which need to display the payment overdue, of
> each
> customer.
>
> However, if I sum all payment transaction of sales order (because a order
> can be settled with multi payment) and substracted with the total amount
> of
> sales order, the processing time will be taken longer, on the other hand,
> if
> i prepare a field, which store the settled amount of a sales order in it's
> table, it will take less processing time, but if there have anything wrong
> when updating that field, the value of that field will be different when I
> sum all payment transaction.
>
> If you were me, what will you do?
>
> Thanks
>