Re: Function or Field? - Mailing list pgsql-sql

Do I have to create another table to put this data???
But, Isn't it redundancy? :-/

The question is: For example: I have a "clients" table and I have a "taxes" table that is a chield 
of client. Is more efficient put fields into client table that contains:  -) the count for paid taxes  -) the count for
unpaidtaxes  -) the count for all taxes  -) the last tax expiration date Or is more efficient construct a function that
willcount this field runtime,
 
as a view for example, or a simple function.  -) SELECT count(*) from taxes where client=$1 and not nullvalue(dt_pay);
-)SELECT count(*) from taxes where client=$1 and nullvalue(dt_pay);  -) SELECT count(*) from taxes where client=$1;  -)
SELECTdt_expiration from taxes where client=$1 order by 
 
dt_expiration desc
limit 1;

While having few records in "taxes" table, the function (runtime) work 
right and
in good time, but when the "taxes" table grows I think the function 
will run so
slow...
What is correct???
Construct a Function to count runtime? or Create a Trigger to update the
"clients" fields before all action and use those fields in select???

Thanks

Quoting Joel Fradkin <jfradkin@wazagua.com>:

> You could also make a table with just that data in it so you don't have the
> field in all the records and you don't have to check all the records to see
> what is next.
>
> I am assuming this is some kind of a flag values used in a batch, if you
> just need the last id I use max(id)+1.
>
> Joel Fradkin
>
> Wazagua, Inc.
> 2520 Trailmate Dr
> Sarasota, Florida 34243
> Tel.  941-753-7111 ext 305
>
> jfradkin@wazagua.com
> www.wazagua.com
> Powered by Wazagua
> Providing you with the latest Web-based technology & advanced tools.
> C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
>  This email message is for the use of the intended recipient(s) and may
> contain confidential and privileged information.  Any unauthorized review,
> use, disclosure or distribution is prohibited.  If you are not the intended
> recipient, please contact the sender by reply email and delete and destroy
> all copies of the original message, including attachments.
>
>
>
>
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
> On Behalf Of lucas@presserv.org
> Sent: Monday, May 02, 2005 3:17 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Function or Field?
>
> Hi.
> What is the better way to store the last record for a translation???
> I.E:
>  The data for the last product vendding.
>  What is better:
>  a) Create a field in "product" table and create a Trigger (before insert or
> update into vendding table) to alter this field.
>  b) Create a view or function that check the all venddings (in vendding
> table)
> for the specified product and return the last vendding information?
>
>  a)
>  CREATE TABLE products(
>   id serial primary key,
>   description varchar(50),
>   last_vendding date()        --Is correct to use this field???
>  );
>  CREATE TABLE vendding(
>   id serial primary key,
>   date_ date,
>   product integer references (products)
>  );
>  CREATE TRIGGER TG_change_products_last_vendding_field on table vendding
> BEFORE
> INSERT OR UPDATE FOR EACH ROW EXECUTE procedure
> change_products_last_vendding();
>
>  b)
>   CREATE TABLE products (
>    id serial primary key,
>    description varchar(50)
>   );
>   CREATE TABLE vendding(
>    id serial primary key,
>    date_ date,
>    product integer references (products)
>   );
>   CREATE VIEW last_product_change as SELECT * from vendding order by date_
> desc
> limit 1; --Okay, this view will return the last record and not the last
> record
> for a product... but its a example.
>
> I am asking it becouse I have used CLIPPER(dbase) for my old programs and in
> DBASE the view/check function that will check for each select is not
> functional. And I need to create a field in all table references, but in
> DBASE
> this fields allways broken and I need to recheck it.
>
> Thank you.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>