From: | "Mattias Kregert" <mattias(at)kregert(dot)se> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Request for advice: Table design |
Date: | 2003-06-17 08:35:19 |
Message-ID: | 021301c334ab$633b79c0$09000a0a@kregert.se |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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.
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.
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...
Any suggestions? Real world examples?
/M
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2003-06-17 08:48:15 | Re: Request for advice: Table design |
Previous Message | SZŰCS Gábor | 2003-06-17 08:29:34 | adddepend and partial indexes |
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2003-06-17 08:48:15 | Re: Request for advice: Table design |
Previous Message | weigelt | 2003-06-17 08:01:02 | Re: Can the frontend get notifications from the postgres server? |