From: SZUCS Gábor <surrano@mailbox.hu>
To: pgsql-general@postgresql.org
Subject: Re: Request for advice: Table design
Date: Tue, 17 Jun 2003 18:22:17 +0200
Message-ID: <030601c334ed$16070c40$0403a8c0@fejleszt4> (raw)
References: <021301c334ab$633b79c0$09000a0a@kregert.se>
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?
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: surrano@mailbox.hu, pgsql-general@postgresql.org
Subject: Re: Request for advice: Table design
In-Reply-To: <030601c334ed$16070c40$0403a8c0@fejleszt4>
* 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