Re: Invoices

On Thu, May 03, 2001 at 10:06:05PM +0200, Ludwig Meyerhoff wrote:
> Hallo!
>
> Maybe this is a bit off-topic, as this problem is more a "design"-one, but
> I wanted to write a web-application write invoices more easy. I wonder if
> it was a good idea to try this using Postgres or if it was better to write
> the data of each invoice in a separate file in a separate directory.
>
> I doubt it was a good idea to put all the data into one database like
>
> create table invoices(
>   invoice integer primary key,
>   datum date,
>   customer integer references customers,
>   clerk integer references clerks
> );
> create table invoicedata(
>   invoice integer references invoices,
>   item integer references services,
> );
>
>
> as this would mean all the information of all invoices is stored in one
> table, meaning the table will grow to a HUGE size making queries very
> slow.

That depends on what you think HUGE is and slow is.

Best thing to do is make a good estimation of how many
invoices you are planning to keep in the table and then
go generate a script to create a dummy table with that
many rows in it and test the reponse times.  Use EXPLAIN
command to find out what is slowing your queries and make
sure you have created indexes on the columns that your
queries use. ( Don't forget to run VACUUM on your DB after
you do the inserts)

It makes sense sometimes to split something like this into
two tables with identical stucture.  One table would be the
invoices that you use most commonly and the other an archive
of invoices that you move delete from the first table when you
don't need them in most of your query results. (Like invoices from a year ago)

> On the other side I doubt following solution will be a good idea, too!
>
> create table invoices
> (
>   invoice integer primary key,
>   datum date,
>   customer integer references customers,
>   clerk integer references clerk
> );
> create table INVOICENUMBER
> (
>   item integer references services,
>   amount integer,
> );
>
> as this will create a HUGE number of tables and I think it was not a good
> idea to give users permission to create new tables.
>
>
> Maybe someone can help?
>
>
> Saluti!
>
> Ludwig
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)