12 Checkboxes vs. 1 Bit Flag thingie

[evanscamman]

I've got an ordering database and I keep adding more and more checkboxes to the Orders table...

Authorized,Processed,Confirmed,Shipping,Received,CheckedIn,
RedFlagged,BackOrdered,etc...

I also have a StatusField where I store a calculated value - giving the Order's current status in a nutshell (calculated to speed up queries)
1 = Authorized
2 = Processed
3 = Confirmed, etc...

Each time the order process moves forwards, it's status has to be reevaluated - sometimes this is done in a query, other times in VBA.

My question is:
Would I be better off to shrink all these check boxes down to one Long, define some constants, and use the whole bit flag approach?

I have the feeling that this is more elegant, but would I get into trouble when i try to use the constants within a query?
Would I end up with 42 levels of Iif statements?
(I never call functions from within queries anymore because they are so slow!)

Thanks for your input!
Evan

 

[datAdrenaline]

>> Would I be better off to shrink all these check boxes down to one Long <<

Nope ... you'd be better off normalizing your database. Which means you would have a child table that records the date and the status of the parent record, then in a query you can always get the latest status, plus you have a history of statii ...

tblParentRecords
ParentID (Autonumber, PK)
<other fields>

tblParentRecord_Statii
ParentStatusID (Autonumber, PK)
ParentID (Number/Long, FK to tblParentRecords.ParentID)
StatusID (Number/Long, FK to tblStatii.StatusID)
ValidThrough (Datetime, Date the status goes 'old', the 'current' status would have a Null in this field, I use the After_Update event of the form {possibly a subform} used to input the statii to update the ValidThrough field of the previous status, that way the users do not have to maintain that data)

tblStatii
StatusID (Autonumber, PK)
Status (Text,50) {values like: Authorized,Processed,Confirmed,Shipping,Received,C heckedIn}

... Or ... you just need ONE field that is a Foreign Key to a table that has a list of valid statii (from tblStatii), then the record is updated to show the current status.

....

But ... BIT fields are really not part of the Access/VBA world, although I have used them with success, but you are ALWAYS tied to a User Defined Function or an expression to evaluate whether or not your BIT is toggled or not, since JET does not perform BITwise comparisons. If you are interested, I have posted a utility that has some bitwise procedures in it ....

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1408366

I have attached a copy of it to this post in case you are are not a member of that site ...



...

If you would like some links on normalization, I can get some of those too ...

 

[Banana]

Just to second Brent- whenever you find yourself needing to add more fields, something is amiss with the database design because adding new rows should be the normal rule, rather than adding more fields.

The only use I found within Access/VBA world was to manage a denormalized report derived from normalized structure or where I need to run through possible permutations (which is actually quite rare in actual use- more useful for my own experiments testing stuff, but I can't think offhand of where I would need to run through possible permutations for an actual set of data....)

 

[evanscamman]

I think you are both right. The more I think about it, the more un-normalized I realize it was. The seperate Status table would also provide a trail for the order, and allow the status to take steps backwards, etc...

datAdrenaline - the bitwise procedures you post will be very helpful!

Thanks a bunch,
Evan

 

[gemma-the-husky]

it does depend to some extent on exactly what information you are trying to progress

if you use a status table then the assumption would be that there is a sequence or order to the statuses ie for statuses 1 thru 99, status n+1 is ALWAYS more advanced than status n. (possibly doesnt have to be, but it shouldnt be completely random - there should be some status changes that are just not valid)

eg you may have these statuses

incompleteorder, validorder, confirmedorder, pickedorder, processedorder, despatchedorder

but say you have other conditions eg order on hold because the customer is on stop ...
or as you say, there may be multiple issues affecting the order - eg a site survey being incomplete, and customer's credit-worthiness not verified

then it may be appropriate to have all these options encompassed within a single status field - or it may still be better to store some of these values separately from the general order status, as you are doing at present

--------
fwiw, i think you are better doing whatever you finally decide with individual flags, rather then trying to bitslice a single byte or word

 

[The_Doc_Man]

My view is this: If you have an order, it can either flow or not flow. If it is not flowing, you must ask why not? This is where a very careful examination of your business rules is a sine qua non. What do your business rules say about non-flowing orders and why they aren't flowing? This is a rhetorical question because it is YOU who must provide that answer to yourself.

I think of a split between the order and its events/states, with the order ALWAYS being in a transition waiting for a hold of one reason or another until it is closed out completely. But this is a business rule issue first.

Let's say you have an order table with an order ID that is unique forever. I.e. parent order ID is a true prime key for orders, using a LONG Autonumber for lack of a better PK. This lets you track at least a couple of billion transactions.

You've got the order which you can reference via its ID, which is a prime key. The state of the order, however, is based on a separate child-table status record such as, but not limited to:

tblStatEvents
OrderID, FK to order table, LONG
StatusCode
StatusSubCode
StatusBegin
StatusEnd

and the Status codes are things like, Entered, Waiting for Inventory Availability, Waiting for Payment, Waiting for Quality Control, Waiting for Approval (of the authority noted as a status sub-code), Waiting for Shipping, Waiting for Delivery, Closed.

OK, what is the status of the order? It is the status code associated with the status record with the most recent StatusBegin date and an empty/zero StatusEnd date. If you are waiting for two different approvals, clearing one won't clear the other.

How do you manage that? Several ways. I migh use something based on a "Max" SQL aggregate for the StatusBegin date and an =0 criterion for the StatusEnd date as an underlying query. This query can be the basis of a status report.

You want order history? Look at the entire status table grouped by OrderID sorted by StatusBegin date. You might have to do a DLookup or use a JOIN to a status-code translation table to build the complete status record, but that's no biggie.

You can also do this by form with VBA, which is probably the best bet for individual order transitions. Say the highest state is "Waiting for Payment" and the check comes in.

So you build a "We Got Paid" form that only works on entries with the "Waiting for Payment" state and a zero StatusEnd field. For the order for which you have a payment, end that Wait status by supplying the StatusEnd date. Maybe your rule would say that if you were waiting for payment and got one, the "Move to Shipping" state is next. So within the form, you create a new Status entry for the order with code "Move To Shipping" and either today's date or the date of the StatusEnd for the "Waiting for Payment" entry.

Now, if you did that via a "We got paid" form, underlying code from the thing that posts the payment could also just directly create the new status entry automagically via recordset operations. If you have forms for each of your major state transition types, each drawing data from the query that looks only for pending states (ie. no state-end date), this should be reasonably simple to keep straight. The only catch would be where you wait for more than one approval, you couldn't make the next transition untill all "hold for approval" states were resolved.

 

[evanscamman]

I'm wondering if you guys have any thoughts on this:

tblOrderRequest stores original order as requested by user.
I can easily create a separate status table as suggested above.

But, when filling the order sometimes a substitue item must be used, and other times the order must be split across several suppliers.

So, tblOrderActual stores the Item that actually gets ordered - with a one to many relationship to tblOrderRequest.

In light of this, how would I implement a seperate Status table, as suggested above, when the order is split across 2 tables?

Thanks for any ideas,
Evan

 

[Banana]

Oooo... .things are getting fun now!

To me, there should be *one* order. It may contain many items, many suppliers, many status.

Thus the order table would be just a three way junction table between producers, suppliers and the customers and should only have order date. Maybe some more fields that relates directly to the order itself (but I can't think of what else...)

then the detail table would hold the values of what items were requested/actually received. Thus along with the fields for product key, a status flag indicating if it was requested, subsituted, or actually recieved.

Then have suppliers service the same order ID if the order is split between them. If you need to know what details were included, then the detail table will need supplier key as well.

I hope that gives you some ideas. If I've just succeeded in confusing you, feel free to post back with questions or feedbacks or concerns that what I proposed is not correct.

 

[evanscamman]

Banana,

I think i do need to have 2 different tables - Request and Actual.

In OrderRequest i store:
OrderRequestID
ItemID
Quantity
JobName
OrderedBy
AuthorizedBy
DeliverByDate

OrderActual stores:
OrderRequestID
ItemID
Quantity
SupplierID
ShipMethodID
DOA


OrderActual has the potential for multiple records that point back to one in OrderRequest
In my case, i think one table for all this would be a step away from normalization.
Also, there would be no way to compare request with actual.

So back to Status - any ideas?

Evan

 

[evanscamman]

How to deal with Status

Here's what i've got so far: (these orders for materials and supplies - placed by employees in a manufacturing facility - no customers involved)

TBL_REQUEST
Pending
Requested
Authorized
--OnHold
--Canceled
-------------------------------
TBL_ACTUAL
Processed
Confirmed
Shipping
--BackOrdered
--Delayed
Received
--RedFlagged
----Damaged
----Returned
----Refused
----ReOrdered
Invoiced
Reconciled

This is basically the "flow" method that The_Doc_Man was talking about.
I'm thinking 2 status tables - one each for tblRequest and tblActual.

My queries that show orders with status can use iif(isnull(tblActual.RequestID),tblRequest.Status,tblActualStatus)

What do you guys think of this?

 

[gemma-the-husky]

--------------------------------------------------------------------------------

I'm wondering if you guys have any thoughts on this:

tblOrderRequest stores original order as requested by user.
I can easily create a separate status table as suggested above.

But, when filling the order sometimes a substitue item must be used, and other times the order must be split across several suppliers.

So, tblOrderActual stores the Item that actually gets ordered - with a one to many relationship to tblOrderRequest.

In light of this, how would I implement a seperate Status table, as suggested above, when the order is split across 2 tables?

Thanks for any ideas,
Evan

you really have to consider your design. Lets say you have a requirement for 4000 widgets. Now you order these from company A, but they can only do 2000, so you part order those 2000, and order 1000 more from each of company B and company C.

Banana thinks there is only one order, but i dont agree - i would raise three orders, one for each supplier.

But then take it further, and make it a bit more complicated - lets say for each supplier, you have multiple items to order, and for each item these may delivered in multiple consignments. (and even worse if its a long term bulk order the prices may change at some point!) - Ive just reread your post, and you have substitiute items - do you decide on the replacement part, or does your supplier, as this will make a difference.

To do all of this, you need at least the following tables

suppliers - to hold suppliers
products - your products
purchase_orders - the general order details
purchase_order_items - separate items for each order
purchase_order_deliveries - consignments for each order line


This will get even more complicated if you then need to relate things like back orders/unfulfilled. returns and invoices and credits to these orders.

--------------
now maybe the order status thing is possibly a bit of a red herring - but maybe not - maybe it makes the whole thing even more complex still - but i think there are some desin issues to resolve besides status flags

---------------
Everything depends on what you actually need/want to do, but you do need to get the structure as close as possible before you start - much much harder to fix down the line.

 

[evanscamman]

gemma,

I think I am pretty much on target with your post.

I have all these tables:

suppliers - to hold suppliers
products - your products
purchase_orders - the general order details
purchase_order_items - separate items for each order
purchase_order_deliveries - consignments for each order line

except for the last one - order_deliveries.

Here, if an order is being delivered in pieces, i just split out the record in purchase_order_items. Because they are both linked to the original request it's easy to find them as a group and compare quantity to the original request.

Am I over simplifying this?

Also, my purchasing manager is the one that makes substitutions based on feedback from the supplier.

Thanks,
Evan

 

[gemma-the-husky]

what you are looking for is a system/structure where all the data related to any particular item is readily available, based on your table and key structure.

if you have that you will find it easy (easier) to design useable forms and reports.
then once you start designing forms etc, it will be clear whether some refinement is necessary