Message-ID: <021301c334ab$633b79c0$09000a0a@kregert.se>
From: "Mattias Kregert" <mattias@kregert.se>
To: <pgsql-general@postgresql.org>
Subject: Request for advice: Table design
Date: Tue, 17 Jun 2003 10:35:19 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Good morning, list!
I am in the middle of a project and i am just about to decide how to lay ou=
t the table(s) for work orders. I had been planning to use one table, but n=
ow i'm not so sure that it would be optimal.

The order history will grow with something like 150K-200K rows/year and wil=
l have to be saved for ten years.
The total number of active (new+validated+planned+ready_for_invoicing) orde=
rs would be something like 5K rows.

I am thinking about two solutions:

1. One table, "orders" with a column named "status". New orders, validated =
orders, planned orders, ready for invoicing, and old orders, all in one big=
 table. The status column would be updated a number of times for each order.

2. A number of tables, "new_orders", "validated_orders", "order_history" et=
c... No status column. Order rows would be moved from one table to another.=
 Perhaps i should have only two tables: "orders" and "order_history"?


More tables would mean more indexes (but smaller in size), harder to use/ma=
intain a lot of tables and indexes and triggers and stuff... One table woul=
d mean that a lot of inactive orders would slow down access to active rows =
in the table...

Any suggestions? Real world examples?

/M