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@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?