Re: Request for advice: Table design

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(at)mascari(dot)com