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