Re: Database design and triggers... - Mailing list pgsql-general

Hi Brandon,

Thank you for your response.

Yes, I see what you mean when you say that I can list Qty in signed
integers and use a view. Good point.

But I'm not sure as to using a view vs using a permanent table updated
by triggers. It seems we are dealing with the clasic tradeoff between
speed & storage space.

A permanent table would duplicate data and even though it would toll
write operations I suspect it would be faster than users bringing up a
view every time they wish to execute a transaction.

Any Sugestions as to which way I should go on the tradeoff and why?

Thanx!

"Brandon Aiken" wrote:
> Actually, I don't think you need Table 2 at all.  Rather, you can fairly
> easily incorporate all the functionality of CurrentCountTable into Table
> 2 and then use a query or a VIEW.
>
> Say you have these two tables.  [I'm not the best at data modeling yet,
> so I wouldn't necessarily take these verbatim.  I'm still a newbie.
> Listed here is pseudo-SQL.]
>
> TABLE "Transaction"
> (
>   "TransactionID" serial,
>   "OperationID" integer,
>   "OperationType" char(15) NOT NULL,
>   "ClientID" integer NOT NULL,
>   "TransactionDate" date NOT NULL DEFAULT 'epoch',
>   "UserID" char(15) NOT NULL,
>   PRIMARY KEY ("TransactionID"),
>   UNIQUE ("OperationID")
> )
>
> TABLE "TransactionItem"
> (
>   "TransactionItemID" serial,
>   "OperationID" integer NOT NULL,
>   "PartID" integer NOT NULL,
>   "LotID" integer NOT NULL,
>   "Qty" integer NOT NULL,
>   PRIMARY KEY ("TransItemID"),
>   FOREIGN KEY ("OperationID")
>       REFERENCES "Transaction" ("OperationID")
>       ON UPDATE CASCADE ON DELETE RESTRICT,
>   UNIQUE ("OperationID", "PartID")
> )
>
> Now, when you store Qty, you store a positive number if the inventory
> increases and a negative number if it decreases.
>
> Now, you can use a query or create a VIEW based on this query:
>
> SELECT "OperationID"
>    , "ClientID"
>    , "TransactionDate"
>    , "PartID"
>    , "LotID"
>    , "Qty"
>    , "UserID"
> FROM "Transaction" NATURAL JOIN "TransactionItem";
>
> Alternately, you can continue to store the Qty as an unsigned integer
> and then use this query/VIEW:
>
> SELECT "OperationID"
>    , "ClientID"
>    , "TransactionDate"
>    , "PartID"
>    , "LotID"
>    , CASE
>         WHEN "OperationType" = 'Incoming' THEN "Qty"
>         WHEN "OperationType" = 'Outgoing' THEN (-1 * "Qty")
>      END
>    , "UserID"
> FROM "Transaction" NATURAL JOIN "TransactionItem";
>
> As far as speed, speed is always an issue.  PostgreSQL is going to
> perform better than Access, but don't use good performance as a crutch
> for bad design.
>
> As far as normalization, it is possible to take it too far.  There is a
> time when de-normalizing a database will significantly improve its
> performance even if it involves duplicating data.  4NF is not the goal
> of DB design, having a usable database is.  Knowing when and how to
> de-normalize is much more difficult than learning to design a normalized
> data model.
>
>
> --
> Brandon Aiken
> CS/IT Systems Engineer
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> romantercero@gmail.com
> Sent: Thursday, September 07, 2006 7:09 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Database design and triggers...
>
> Hi Brandon, thanks for answering.
>
> The information regarding a transaction is stored on two separate
> tables due to normalization. The first table stores general information
> regarding the transaction like Transaction number, date, customer ID,
> type of transaction, userID etc... The second table stores the details
> of the purchase like the products and quantities that the transaction
> affected. Like this:
>
> Table 1:
> Operation#     Type_of_operation    Client#        Date    UserID
> 1           Inbound                    10        1/1/2000
> Paul37
> 2            Outbound                    10        1/2/2000
> Steve04
>
> Table 2:
> Operation#    Part#   Lot#           Qty
> 1        X    a        10
> 1        Y    ds1        9
> 1        Z    54ad        7
> 2        X    a        10
>
> Table 2 has Table 1's Operation field as a Foreign key.
>
> Now, to obtain a current count of  Part X we have to create a temporary
> table on which we can use aggregate functions.
>
> CurrentCountTable:
>
> Operation    Client#    Date    Part#    Lot#    Qty    UserID
> 1    10    1/1/2000    X    a    +10    Paul37
> 1    10    1/1/2000    Y    ds1    +9    Paul37
> 1    10    1/1/2000    Z    54as    +7    Paul37
> 2    10    1/2/2000    X    a    -10    Steve04
>
> Now, on the temporary table called CurrentCountTable we can use an
> aggregate function, The problem is that creating this table is slow
> with INSERT INTO, and so are aggregate functions (On MS Access). So
> Naturally it occurred to me that triggers can keep a permanent version
> of the CurrentCountTable up to date every time some one inserts in to
> Table 1 and Table 2. But it has to be perfect to avoid inconsistencies.
>
> So, are triggers a safe bet? Is using triggers more advisable over the
> temporary table solution because on PSQL speed is not an issue? Or
> should I use views?
>
> Thanks!!! (Hope the tables got listed correctly :-/ )
>
>
>
> "Brandon Aiken" wrote:
> > It's not clear to me how your data is organized or exactly what you're
> > counting.  If I understand you correctly, yes, you could use triggers
> to
> > maintain a table in this manner.  However, why can't you simply use a
> > SELECT query using the SUM() or COUNT() aggregate functions?  If the
> > queries are slow, do some index tuning.
> >
> > --
> > Brandon Aiken
> > CS/IT Systems Engineer
> >
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> > romantercero@gmail.com
> > Sent: Wednesday, September 06, 2006 2:05 PM
> > To: pgsql-general@postgresql.org
> > Subject: [GENERAL] Database design and triggers...
> >
> > Hi everybody. Hope I'm posting in the correct group.
> >
> > My question is part design and part implementation.
> >
> > Since we are creating an inventory system we need to have the clients
> > pull up current inventory. Also, we need to have the past transactions
> > stored for reference and billing. In our previous system in MS Access
> > we accomplished this by adding up all of the transactions stored in
> two
> > tables and generating a temporary table with the latest inventory
> > count. The problem with this approach is that it is slow because the
> > temporary table has to be created every time a user needs to see a
> > report or work on a form. Even when instead of creating a temporary
> > table we use a query it is still slow. With postgreSQL I found out
> > about triggers and I figure that instead of calculating the current
> > inventory count and storing it in a table every time a client needs it
> > I could have a triggers maintain a table with the current count by
> > incrementing or decreasing the amounts each time a transaction is
> > stored in the transaction tables. My worry is that if for some reason
> a
> > trigger were to somehow fail to execute correctly there would be an
> > inconsistency between the transactions table and the current inventory
> > count table and it would have to be calculated from scratch taking in
> > to account all of the past transactions in the transactions table.
> >
> > Are trigger a very safe way to use in the way I describe? Or should I
> > try using views or stick with the temporary table solution we already
> > have?
> >
> > My second part of the question is if there is a tutorial for triggers
> > and stored procedures and what is the difference between Procedures
> and
> > Functions?
> >
> > Thanks Beforehand!
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >        subscribe-nomail command to majordomo@postgresql.org so that
> your
> >        message can get through to the mailing list cleanly
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org