Message-ID: <030601c334ed$16070c40$0403a8c0@fejleszt4>
From: =?iso-8859-1?Q?SZUCS_G=E1bor?= <surrano@mailbox.hu>
To: <pgsql-general@postgresql.org>
References: <021301c334ab$633b79c0$09000a0a@kregert.se>
Subject: Re: Request for advice: Table design
Date: Tue, 17 Jun 2003 18:22:17 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Mattias,

Here goes how we do it:

order_status (id, name):
    valid order statuses
order (partners, dates, status, etc):
    main data of order
    refs to order_status
order_statuschange (order, timestamp, oldstatus, newstatus, user):
    history of status changes on each order
    refs to order (cascade), order_status
order_mod (order, mod, user, closed, etc):
    separate modifications for an order (only the newest is able to receive
items,
    see below)
    refs to order (cascade)
order_item (order, itemno, archive, archive_from_mod):
    items in each order.
    refs to order (cascade), order_mod
order_notify (order, timestamp, user, operation):
    log and notify table
    does NOT ref to order so delete from order leaves this table alone.

The same structure goes for shipments and invoices. A note: order_mod is a
historical thing we'd like to get rid of, but our hands are always full to
do that, so we let it stay.

G.
------------------------------- cut here -------------------------------
----- Original Message ----- 
From: "Mattias Kregert" <mattias@kregert.se>
Sent: Tuesday, June 17, 2003 10:35 AM


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?