Search Postgresql Archives

Re: Database design and triggers...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx
> [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of
> romantercero@xxxxxxxxx
> Sent: Wednesday, September 06, 2006 2:05 PM
> To: pgsql-general@xxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxx 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux