To: pgsql-general@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: Request for advice: Table design
Date: Tue, 17 Jun 2003 10:48:15 +0200
Lines: 38
Message-ID: <bcmkkf$mjt$1@main.gmane.org>
References: <021301c334ab$633b79c0$09000a0a@kregert.se>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de-AT;
	rv:1.3) Gecko/20030312
In-Reply-To: <021301c334ab$633b79c0$09000a0a@kregert.se>

Mattias Kregert schrieb:
> 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...
> 

 From my experience, I'd go for two tables orders and order_history. 
Then move orders which are no longer used from orders to order_history. 
E.g. you could keep only the last two years in orders, and move 
everything else over to order_history. Thus you'll never have more than 
300-400k rows in the orders table, which isn't much at all. The only 
drawback is, that your application needs to be aware of that, in order 
to be able to offer reporting on historic orders.

Using different tables for different order statuses is not a good idea, 
you will eventually run into problems with that.

Thomas