Keep pk_Order_ID and OrderNumber separate???

[mluka]

Hi, all.

Still designing an order entry system (Customers, Products, OrderHeaders, OrderDetails tables).

I was initially looking at using the pk_Order_ID (automatic, serial number) as the OrderNumber (the "human-readable" number).

But that doesn't make much sense, does it? The ID (and therefore the order number) would get assigned fairly early in the process, as soon as the Header record gets committed and before all the details have been created. But then, what happens if I decide, halfway through the process, to simply abandon this order and not create it? (In our environment, this does happen regularly; we do this for training purposes as well).

Sure, I would set the status of the order to something like "Abandoned".

But I would be stuck with "holes" in the order number sequence, in the sense that I couldn't use the order number as a reliable indicator of how many invoices were created today, for instance.

Wouldn't it be bettter if I had a second field (OrderNumber), that I would assign myself right at the end of the process, when I know the order is valid, complete, etc. That way, all assigned OrderNumbers would be valid.

For the abandoned orders, I could even delete the header (triggering the automatic deletion of order details, if any, through the relationship). Holes in the sequence of ID numbers? Who cares?

Please let me know if my thinking is right on this. :qwery:

Thanks!

[T-Square]

Your thinking is good--you don't need to use the primary key to determine how many orders are processed in a day.

I would say that if your workflow supports it, then by all means have a field that you set when an order is processed. What I mean is this: if the people taking orders are required to confirm the order in some way, then have them tick a confirmed field.

BUT--if it is adding something to your users' workflow, it might be better to create a calculation field that would test for the existence of something they have to enter anyway, and use THAT as your count.

Basically, you identify a field that your users have to fill out to create a valid order (maybe check for the presence of OrderDetails records linked to the OrderHeaders record), and use that as your flag that an order is "processed". (The calc is as simple as "If(OrderDetails::DetailsID; 1; 0)") <-- caveat: I am sure someone else will tell me how this is wrong, and suggest a better way

My experience is that if you add something to an application just to suit the application's needs, it doesn't get handled reliably. People forget, and mess it up.

HTH,

David

[mluka]

Hi, T-Square; thanks for the response.

Actually, at the end of the order entry process, we will have to click an "Accept" button.

At that time, I would pull the next order number from a parameter table and update the order header with it. And then print out the order, etc.

I guess that makes sense.

Thanks.