From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-general@postgresql.org
Subject: Re: Request for advice: Table design
Date: Tue, 17 Jun 2003 10:48:15 +0200
Message-ID: <bcmkkf$mjt$1@main.gmane.org> (raw)
In-Reply-To: <021301c334ab$633b79c0$09000a0a@kregert.se>
References: <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spam_eater@gmx.net, pgsql-general@postgresql.org
Subject: Re: Request for advice: Table design
In-Reply-To: <bcmkkf$mjt$1@main.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox