Database design

I have to design choices
Here's the situation (OLTP):
I have a WorkOrder table that could contain millions of record and a
WorkOrderId primary key. The record is inserted and then updated once
by a technician. The workorder is created when the user access a web
page and then updated when the user save the work order. The work
order is almost never canceled after it's creation.
The choices
1) I do all my processing directly in the table.
2) I have a temp table which is commited to the real table when the
technician saves the work orders.
What do you think? Are all these OLTP order systems working with temp
tables. Is there some things I should consier like having no indexes
on a temp table so insert is faster and response time is better. I
have a low number of user and not many concurrent requests but a lot of
data can accumulate over time.
Thanks for your time.
ObIt may not be as complicated as you apparently think. Just present the Web
page to the user - collect all then info there and then when the user goes
to save it, do an insert and you're done.
But have I missed something?
You talk about "all my processing directly in the table" or "do all my
processing..."
What processing is that? I couldn't imagine what it could possibly be that
would require any database involvement (temp table or otherwise) prior to
the user saving the new work order.
We could provide you with more specific guidance if you tell us what all
this processing is that you refer to that is apparently required on creation
of new work orders.
-HTH
"oberyn" <oberyn1martell@.yahoo.com> wrote in message
news:1141966631.129590.181350@.p10g2000cwp.googlegroups.com...
>I have to design choices
> Here's the situation (OLTP):
> I have a WorkOrder table that could contain millions of record and a
> WorkOrderId primary key. The record is inserted and then updated once
> by a technician. The workorder is created when the user access a web
> page and then updated when the user save the work order. The work
> order is almost never canceled after it's creation.
> The choices
> 1) I do all my processing directly in the table.
> 2) I have a temp table which is commited to the real table when the
> technician saves the work orders.
> What do you think? Are all these OLTP order systems working with temp
> tables. Is there some things I should consier like having no indexes
> on a temp table so insert is faster and response time is better. I
> have a low number of user and not many concurrent requests but a lot of
> data can accumulate over time.
> Thanks for your time.
> Ob
>|||ok I tried to simplify my situation to much. What I have is a work
order that can be modified multiple times before saving. Right now we
have a flag that represents the state of the work order each time it is
modified. And the same flag is set to completed when the work order is
saved. This state managing could all be done in an object and commited
to the database when the user saves the work order but we decided to
put it in the database to persist the state for up to 1 day because we
don't to want to have many pending asp.net sessions persisted (sql
server session state) all day and the technician has to be able to
resume the work order at any time during the day.
Should I have some sort of Cart table to hold the work orders and
commit them to the WorkOrder table when the flag is set to completed or
inserting and updating the main table (work order) with millions of
record is ok.?
Jordan wrote:
> It may not be as complicated as you apparently think. Just present the Web
> page to the user - collect all then info there and then when the user goes
> to save it, do an insert and you're done.
> But have I missed something?
> You talk about "all my processing directly in the table" or "do all my
> processing..."
> What processing is that? I couldn't imagine what it could possibly be that
> would require any database involvement (temp table or otherwise) prior to
> the user saving the new work order.
> We could provide you with more specific guidance if you tell us what all
> this processing is that you refer to that is apparently required on creati
on
> of new work orders.
> -HTH
>
>
> "oberyn" <oberyn1martell@.yahoo.com> wrote in message
> news:1141966631.129590.181350@.p10g2000cwp.googlegroups.com...|||Instead of a flag in your main table, why don't you have a
WorkOrderStatus table? Minimize UPDATEs, and simply INSERT new rows to
the Status table; when you want to find out what status a WorkOrder
has, join it to the Status table and retrieve the most recent status?
Stu|||Okay, then given this additional information I would go with the
recommendation earlier by Stu. His recommendation makes a lot of sense
because each of your "work order" thingies can each exist in multiple states
over time. Each of those states goes into a "Status" table which gives you
the benefit of knowing a date/time (if you record that, of course) of each
status change. A status "flag" column in your WorkOrder table would not
permit you to record or know the history of the order. Plus, from a data
modeling point of view an [order] is just an [order] regardless of its
current status ([current status] is just an attribute). Modeling an
[incomplete order] as a separate entity from a [completed order] would
likely be a misconceptualization of the data.
Finally and alternatively, you could persist the state data in a cookie (and
set it to expire after 24 hours) on the client and only store completed
orders in the database. But if you do this then you lose out on the status
history info and have a more fragile system to support (e.g., users with
cookies disabled, switching from one computer to another thoughout the day,
etc).
-HTH
"oberyn" <oberyn1martell@.yahoo.com> wrote in message
news:1141998248.065606.52840@.z34g2000cwc.googlegroups.com...
> ok I tried to simplify my situation to much. What I have is a work
> order that can be modified multiple times before saving. Right now we
> have a flag that represents the state of the work order each time it is
> modified. And the same flag is set to completed when the work order is
> saved. This state managing could all be done in an object and commited
> to the database when the user saves the work order but we decided to
> put it in the database to persist the state for up to 1 day because we
> don't to want to have many pending asp.net sessions persisted (sql
> server session state) all day and the technician has to be able to
> resume the work order at any time during the day.
> Should I have some sort of Cart table to hold the work orders and
> commit them to the WorkOrder table when the flag is set to completed or
> inserting and updating the main table (work order) with millions of
> record is ok.?
>
> Jordan wrote:
>|||I disagree. Finding the "latest status" for a workitem would require
either a "group by" or some flag to indicate which row is the latest
which frequently causes problems and requires an insert to also do an
update.
As I see it, a WorkItem only has 2 status 'Complete' and 'In Process'
and therefore will work fine with just the status on the main table.
If properly indexed, SQL server will have no problem updating 1 row out
of millions. If however, the status implies some sort of workflow then
storing each status, as well as other relative audit data historically
may have some value.
You should never rely on storing state in anything other than the
database because servers and computers can be rebooted and any objects
lost, cache cleared etc...|||Differences of opinion, I guess. As I get more experience with
database design, I've come to realize that the only constant thing in
design that you can truly rely on is that the user's specifications
will change the day after you roll something out :) I try to store as
much information as I can, and a bit flag doesn't tell me how long
something has been in that status, nor does it give me any sense of
direction (e.g., how do you handle "re-opened" tickets?). In other
words, even if the user doesn't mention workflow, I try to design
around that.
It has been my experience, though, that INSERTS are a lot less
expensive than UPDATE's, regardless of your indexes. I'd be
interested in understanding what problems you've had when dealing with
GROUP BY, and finding the latest row; it might influence some of the
decisions I'm making on a new project.
Stu|||RE:
<<Finding the "latest status" for a workitem would require either a "group
by" or some flag to indicate which row is the latest>>
Not true at all. If a date column in the Status table defualt value
automatically inserted when the status row is inserted (representing the
date/time the row was inserted), then it is real easy to get the latest
status without any GROUP BY clause - e.g., SELECT TOP N ... ORDER BY
date_column DESC.
RE:
<<a WorkItem only has 2 status 'Complete' and 'In Process'>>
You have no way to know that - and even if it were true today the
requirements could change tomorrow. What if there were 5 steps towards
completion - each of significance to the business. You would of course argue
that an int column could store a flag to indicate which of the 5 steps the
work order is at. The problem there is that you fail to record the
historical information about when the status changed, the employee who
changed the status, etc. Your little flag column really misses a lot of
potentially valuable information.
RE:
<< You should never rely on... >>
You should never say never. Really. If you are correct, then why are there
so many options for storing or managing state (database, View_State,
Application, Session, cookies, client-side objects persisted to file on the
client via XML (easily facilitated in .NET). There's a lot more to life (and
state) than databases.
-J
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142017670.700641.290070@.i39g2000cwa.googlegroups.com...
>I disagree. Finding the "latest status" for a workitem would require
> either a "group by" or some flag to indicate which row is the latest
> which frequently causes problems and requires an insert to also do an
> update.
> As I see it, a WorkItem only has 2 status 'Complete' and 'In Process'
> and therefore will work fine with just the status on the main table.
> If properly indexed, SQL server will have no problem updating 1 row out
> of millions. If however, the status implies some sort of workflow then
> storing each status, as well as other relative audit data historically
> may have some value.
> You should never rely on storing state in anything other than the
> database because servers and computers can be rebooted and any objects
> lost, cache cleared etc...
>|||Ok the issue is a lot clearer for me now. Thank you all for your
answers.