Re: Request for advice: Table design

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(at)kregert(dot)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?