From: Mike Mascari <mascarm@mascari.com>
To: Mattias Kregert <mattias@kregert.se>
Cc: pgsql-general@postgresql.org
Subject: Re: Request for advice: Table design
Date: Tue, 17 Jun 2003 11:32:31 -0400
Message-ID: <3EEF348F.2020308@mascari.com> (raw)
In-Reply-To: <021301c334ab$633b79c0$09000a0a@kregert.se>
References: <021301c334ab$633b79c0$09000a0a@kregert.se>
Mattias Kregert wrote:
> Good morning, list!
> I am in the middle of a project and i am just about to decide
> how to lay out the table(s) for work orders. I had been
> planning to use one table, but now i'm not so sure
> that it would be optimal.
By "optimal", do you mean "logically consistent"?
> 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.
That shouldn't affect logical design, unless you know you will be
querying the database in a manner which cannot use indexes.
> 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...
The question cannot be answered without a fully defined requirements
document from the customer.
Do you need to know only what the current status of a workorder is? If
yes, one table might do. Otherwise, you need a history table. For each
change in the the status, does the customer require the same
attributes of the transaction to be recorded? If yes, then a two-table
workorder/orderhistory table might suffice. If no, then a separate
relation for each transaction type will be required. etc. etc.
For example,
Does the transition between "planned orders" and "ready for invoicing"
involve the same sort of attributes as the transition from
non-existence to "new orders"? Or, if the data involved is different,
does the customer not care? And has stated so explicitly?
Without such information, a design would be pure speculation.
HTH,
Mike Mascari
mascarm@mascari.com
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: mascarm@mascari.com, mattias@kregert.se, pgsql-general@postgresql.org
Subject: Re: Request for advice: Table design
In-Reply-To: <3EEF348F.2020308@mascari.com>
* 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