Re: Request for advice: Table design

Use one table - it's easier to maintain, more relationally correct and a
growth of 150K-200K rows a year should be nothing to PostgreSQL.

2 million rows isn't very many or difficult for PostgreSQL to manage so long
as it is correctly indexed and vacuumed following any really large bulk data
changes. The speed of PostgreSQL won't be an issue either providing you have
allocated enough memory in your pg_hba.conf and you are running on a
reasonable server (ie - at leats 512mb of ram)

You could actually consider three tables though:

CREATE TABLE orders (
orderid SERIAL8 NOT NULL PRIMARY KEY,
...
<misc order record stuff>
) WITHOUT OIDS;

CREATE TABLE orderstatuses (
orderstatus VARCHAR(20) NOT NULL PRIMARY KEY,
statusdesc VARCHAR(200) NOT NULL DEFAULT 'New Status',
) WITHOUT OIDS;

CREATE TABLE orderhistory (
orderid INT8 NOT NULL REFERENCES orders (orderid) ON UPDATE CASCADE ON
DELETE CASCADE,
historyuser VARCHAR(20) NOT NULL DEFAULT current_user,
historywhen TIMESTAMP NOT NULL DEFAULT current_timestamp,
historystatus VARCHAR(20) NOT NULL REFERENCES orderstatuses (orderstatus) ON
UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT orderhistory_pkey PRIMARY KEY (orderid,historywhen)
) WITHOUT OIDS;

... and maybe create a non-unique index on orderid in orderhistory.

This way you can always get the status of an order by selecting the most
recent entry in the orderhistory table pertaining to a particular record and
use a single plpgsql function add/modify data. You could also use a rewrite
rule on a view displaying the most recent record. And now you have an order
history defining an order's state at a point in time.

Your style may differ to mine in terms of id's etc.. I know storing a whole
bunch of varchar's in the orderhistory table and referencing orderstatuses
may be a bit funny but I've always been of the opinion that it's best to use
a key that actually describes the data.

Rgds,

Jason

On Tue, 17 Jun 2003 06:35 pm, Mattias Kregert wrote:
> Good morning, list!
> I am in the middle of a project and i am just about to decide how to lay
> out the table(s) for work orders. I had been planning to use one table, but
> now i'm not so sure that it would be optimal.
>
> The order history will grow with something like 150K-200K rows/year and
> will have to be saved for ten years. The total number of active
> (new+validated+planned+ready_for_invoicing) orders 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"
> etc... 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/maintain a lot of tables and indexes and triggers and stuff... One
> table would mean that a lot of inactive orders would slow down access to
> active rows in the table...
>
> Any suggestions? Real world examples?
>
> /M
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org