On Thu, 2016-03-10 at 07:09 -0500, Adam Brusselback wrote:
> Rob,
> I understand that if I were to replicate the logic in that view for
> every use case I had for those totals, this would not be an issue.
> But that would very much complicate some of my queries to the point
> of absurdity if I wanted to write them in a way which would push
> everything down properly. The issue is, that I need that data to be
> able to join to that view from other smaller sets of data and not
> have it calculate the totals for every "header" I have in my system,
> just to throw away 99% of them.
>
> My application is for contract and rebate management, so as David
> said, basically accounting. We allow users to set up contracts to
> sell products to their customers, and then track and verify the
> rebates they get back are correct, and that they're not overpaying.
> The equivalent of the header_total view is used in quite a few
> different places in my application. In one case, the state of one
> object in my system (called a deduction) is derived from 5 different
> data points, 2 of which are from my equivalent of the "header total"
> view. Things like the state for the deduction object are derived
> from a query that I encapsulated inside a view. You can see how this
> proliferates.
>
Hello Adam,
With regards to proliferation, I guess it depends on whether you have
multiple views or multiple tables and which is best suited to your
application. If you have a table of customers all you need is a single
table of transactions where one of the columns is a foreign key
pointing to the customer. Want to know a customer's outstanding balance
-- sum their transactions. You wouldn't have a table just for invoices,
one for credit notes, one for discounts given, one for payments, etc.,
etc. The same goes if you want to know the stock on-hand and stock
available quantities for a given product/warehouse. Do it all via a
view or views, as appropriate. Have a column in the table
transaction_type CHAR(1) NOT NULL so you can put a CASE statement
across it so as to know whether to add or subtract.
In your test case example, if you allow alterations to the values in
the columns rate, quantity or amount, you need triggers in order to
maintain database integrity. Then if somebody with the appropriate
privileges does "ALTER TABLE blah DISABLE ALL TRIGGERS;" your database
integrity goes out the window.
All of the above is completely off your point. I just like to keep it
as straight forward as possible.
My apologies for having a bit of a rant.
Regards,
Rob