Calculated values and database design.

[Stefanos]

Hi everybody,

I need some help on calculated values in my database design.

I'm creating an accounting / business management application with the
usual modules like A/R, A/P, Inventory, etc.

I don't know how to handle calculated values in fields like "Customer
Balance", "Inventory Item Qty on Hand", "Inventory Item Qty Last cost"
and other similar.

I don't want to create fields in the database to store these values
but I want to create them on the fly instead. Another thing I prefer
to do is use code in my application (vb.net) not T-SQL to do this
(stored procedure or views), because this way I'm moving business
logic into SQL; so far all the logic is located in a Business Rules
DLL.

My question is how can I do that and at the same time achieve "easy"
joins.

For example:
Every time I retrieve some customer's info I need his balance. Let's
say if my query is:

SELECT CustomerID, CustomerName, Address from Customers
Where CustomerID = ‘xxxxxx'

How can I retrieve the balance, when is calculated from other fields
in other tables, such as Invoices, Credit Memos and Cash Receipts?

I thought to use views and add a join to the above query. Is that good
or bad, performance wise?

What other options I have?

Looking forward for your answer.

Thanks in advanced.
Stefanos

[David Portas]

Business logic belongs server-side if it involves summarizing data.
Otherwise you might as well use a file-server database like Access. You
don't want to have to retrieve all the data just to add it up at the client.

I can only guess at your table structure and keys but maybe this example
will help:

SELECT C.customerid, C.customername, C.address,
SUM(T.trans_amo unt) AS balance
FROM Customers AS C
JOIN Transactions AS T
ON C.customerid = T.customerid
AND C.customerID = 'xxxxxx'

--
David Portas
SQL Server MVP
--

[David Portas]

OOPS! That wasn't very good!

Correction:

SELECT C.customerid, C.customername, C.address,
SUM(T.trans_amo unt) AS balance
FROM Customers AS C
JOIN Transactions AS T
ON C.customerid = T.customerid
AND C.customerID = 'xxxxxx'
GROUP BY C.customerid, C.customername, C.address

--
David Portas
SQL Server MVP
--

[Stefanos]

David,

Thanks for your reply.

I'm following a "fat-client" implementation.
I agree that business rules belong to the server or in my case the
"fat client".
I don't want to put any logic on the database in the form of sprocs or
similar;
I have enough layers in my design already and I don't want to be tied
in a particular RDBS (currently using MS SQL 2000).

A query like the one you posted is what I've thinking to do, but I
hoped maybe someone has a better solution.
Queries like these are very hard to create in multiple joins.

In your example the table "Transactio ns" isn't enough.

I have to get totals from:
* InvoiceHeader
* CreaditMemoHead er
* CashReceiptsHea der

Imagine now if I have to get the balance in a query that has another
2-3 joins on it.

Stefanos

"David Portas" <RE************ *************** *@acm.org> wrote in message news:<Op******* *************@g iganews.com>...

OOPS! That wasn't very good!

Correction:

SELECT C.customerid, C.customername, C.address,
SUM(T.trans_amo unt) AS balance
FROM Customers AS C
JOIN Transactions AS T
ON C.customerid = T.customerid
AND C.customerID = 'xxxxxx'
GROUP BY C.customerid, C.customername, C.address