Date: Tue, 17 Jun 2003 09:37:10 -0400 (EDT)
From: Andrew Perrin <clists@perrin.socsci.unc.edu>
To: Jason Godden <jasongodden@optushome.com.au>
Cc: Mattias Kregert <mattias@kregert.se>,
	pgsql-general@postgresql.org
Subject: Re: Request for advice: Table design
In-Reply-To: <200306171906.10381.jasongodden@optushome.com.au>
Message-ID: <Pine.LNX.4.53.0306170936050.8304@perrin.socsci.unc.edu>
References: <021301c334ab$633b79c0$09000a0a@kregert.se>
	<200306171906.10381.jasongodden@optushome.com.au>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

I agree with Jason. There's no reason to use several tables for data that
are essentially the same in character; that's the point of a relational
database to begin with!  I also agree about the orderhistory table: use it
to store cumulative updates, linked one-to-many with the orders table, so
you can get the complete history of an order as well as the latest status
very easily.

Best,
Andy Perrin

----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
clists@perrin.socsci.unc.edu * andrew_perrin (at) unc.edu


On Tue, 17 Jun 2003, Jason Godden wrote:

> 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@postgresql.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>