Active / Trash - Whose responsibility is it?

[jwcolby54]

But the implementation cost, if you flag trash with a boolean vs a date, is the same. The effort required in all cases: 1) add a field to a table, 2) handle a UI event and save a trash value flag, 3) filter future queries based the trash value flag.

In a landscape where storage space is effectively free (because a date takes more space), the cost of implementing this pattern is fixed. The data type of the flag is irrelevant. The advantage of a date is--for the same implementation cost--you get a time dimension free of charge.

So if just the burger costs $5, but the burger & fries also costs $5, it's tough to sell the solo burger as a value advantage.

Implementation cost is more than storage. It is also reporting, decisions, meetings, new hires to handle more reporting, decisions and meetings etc.

As I mentioned in a previous post, capturing every bit of data that exists has costs.

I am not arguing with you, I am simply pointing things out. If your (clients) business rules need dates then your (clients) business rules need dates. My (clients) business rules did not need dates. In the end, deciding the business rules is not my business. Informing the client what decisions may cost kinda is. But in the end, it is the client who decides these things.

OTOH I can't tell you how often a client informed me, in no uncertain terms... "We need to capture xyz data" and then a year later the table is a mish mash of garbage because it was just too much work... or some other reason.

In terms of design work, I just don't care whether we use a boolean or a date. Samo-samo.

 

[jwcolby54]

Discussing active & trash flags... I had something not dissimilar when working with the U.S. Navy which, in their infinite wisdom, said that we could not archive & delete a record from the main table until the last (implied) step in the process had been complete for 6 months. I.e. the project manager could see the status history of things for another half-year after all we handled.

The process I was tracking could have multiple steps and often they were "on hold" waiting for external resolution, so as you can guess, some records hung around a LOT longer than others. I had a status code that showed the most recent status of the action being recorded and the date on which that occurred. Of the statuses I kept, about 25% of them implied "no further action required." The status codes broke out into three main groups - active (scheduled), waiting, or done (which didn't always mean completed, but DID mean nothing else was expected.)

I could have done the archiving with pure logic, but the Navy wanted a more hands-on approach. So I had a Y/N flag that said "Eligible to archive" and it got set when I ran the query to find the dormant, closed records. I had a report to be run listing the eligible records, along with enough other info in the query to allow verification. FINALLY, once I had all the other tests and checks completed, I could run the query that, in two parts, transferred the eligible records to a secondary storage table and then deleted the record from the primary table.

Which aptly points out that it is the client who decides things. Tell me what you need and I will figure out how to make that happen.

 

[jwcolby54]

So the consensus is that dates may be required sometimes? Since dates are only marginally more difficult to implement, and since once implemented it just works however it works, let us use dates?

 

[cheekybuddha]

Why is the order being deleted?

It was a pseudo example

 

[The_Doc_Man]

In a landscape where storage space is effectively free (because a date takes more space), the cost of implementing this pattern is fixed.

However, when you compare Boolean or Y/N values vs. dates (which are typecasts of DOUBLE), the Boolean is cheaper. A DOUBLE is 64 bits - 8 bytes - whereas a Y/N datatype in native Access is stored in (word) integer - 16 bits or 2 bytes. If you want/need the time dimension, then surely you should use it. But the requirement rules all. If the requirements include a date, include the date. If not, save 6 bytes/48 bits. A big-memory machine does nothing to expand the size of an Access record, which is still limited to 4K bytes.

 

[jwcolby54]

It was a pseudo example

That is the most code I have ever seen for a pseudo example. Good Job!

 

[jwcolby54]

However, when you compare Boolean or Y/N values vs. dates (which are typecasts of DOUBLE), the Boolean is cheaper. A DOUBLE is 64 bits - 8 bytes - whereas a Y/N datatype in native Access is stored in (word) integer - 16 bits or 2 bytes. If you want/need the time dimension, then surely you should use it. But the requirement rules all. If the requirements include a date, include the date. If not, save 6 bytes/48 bits. A big-memory machine does nothing to expand the size of an Access record, which is still limited to 4K bytes.

In fact this is somewhat true. Indexes on the field, speed of queries, Joins etc. Although I was under the impression that booleans were bitmapped behind the scenes. Not sure about that.

 

[MarkK]

the Boolean is cheaper. A DOUBLE is 64 bits - 8 bytes - whereas a Y/N datatype in native Access is stored in (word) integer - 16 bits or 2 bytes. If you want/need the time dimension, then surely you should use it. But the requirement rules all. If the requirements include a date, include the date. If not, save 6 bytes/48 bits.

In a landscape where storage space is effectively free

If the cost of thing is zero, what do you "save" by reducing its consumption?

 

[The_Doc_Man]

Although I was under the impression that booleans were bitmapped behind the scenes.

Not for Access native Y/N or Boolean fields. Yes (or at least it is POSSIBLE) for SQL Server to bitmap Booleans. It's a special datatype called "bit."

To do the bitmapping in native Access, you take a BYTE, WORD, or LONG and a power-of-two constant (or hexadecimal constant if you prefer) and apply the bitwise operators.

JW, you almost certainly know these operators and principles, but for new members who might read this thread, I'll be a little bit pedantic...

According to the VBA Language Specification, v20140424, section 5.6.8.9, Access VBA has these bitwise operators;

1. AND - (a.k.a. logical multiplication) A AND B = TRUE if and only if both A and B are individually true
2. OR - (a.k.a inclusive OR a.k.a. logical addition) A OR B = TRUE if either A or B is individually true
3. XOR - (a.k.a. exclusive OR) A XOR B = TRUE when A does not equal B
4. EQV - (a.k.a. logical equivalence) A EQV B = TRUE when A equals B, whatever they both are
5. IMP - (a.k.a. "material implication) A IMP B = TRUE except when A is TRUE and B is FALSE
6. NOT - (a.k.a. logical negation) (A = NOT A) will always be FALSE, i.e. A XOR (NOT A) = TRUE

NOTE, however, that the result of bitwise bit-twiddling in any of the Boolean variables, when taken as a whole, will only ever be TRUE or FALSE based on the whole variable being zero (FALSE) or non-zero (TRUE). I.e. individual bit testing has to be done through bit masking.

 

[cheekybuddha]

@jwcolby54 - see what I meant in Post #10

 

[jwcolby54]

ROTFL.

First, I applaud your ability to get pizza. Wish I could still eat it, but the oily cheeses and most meat toppings screw with my liver.

Second, on a more serious note, if both "Active" and "Trash" are visible as CHECK boxes (not as a radio button), that implies that, unless you do some behind-the-scenes code on the check boxes' Click or Change events, it might be possible to have both checked or both unchecked. This leads to questions of "is that all there is - or do you have hidden code behind them?" and "what would it mean if both check boxes were in the same state, true OR false?"

Indeed I do. Active and Trash are related but not identical. And so I have a class which wraps those two controls, and imposes a set of rules on them.

1 If a record is Active, it cannot be Trash.
2 If a record is Trash, it cannot be Active. see #1 above
3) if a record is Inactive it is possibly, but not necessarily Trash.

As long as the record is not Trash, it can be Active or Inactive.

So when a delete happens, I need to set the active flag false. Automatically, in code so that #1 is enforced.

A class wraps the two controls, and sinks events for them. The event handler enforces the rules.

 

[MarkK]

Indeed I do. Active and Trash are related but not identical. And so I have a class which wraps those two controls, and imposes a set of rules on them.

1 If a record is Active, it cannot be Trash.
2 If a record is Trash, it cannot be Active. see #1 above
3) if a record is Inactive it is possibly, but not necessarily Trash.

As long as the record is not Trash, it can be Active or Inactive.

So when a delete happens, I need to set the active flag false. Automatically, in code so that #1 is enforced.

A class wraps the two controls, and sinks events for them. The event handler enforces the rules.

It seems that this pattern, and whether or not others understand it clearly, is really important to you.
• I just want to say thanks for bringing your passion, knowledge, and focus to this forum. IMO, it's a welcome breath of fresh air!
Cheers,
Mark

 

[jwcolby54]

It seems that this pattern, and whether or not others understand it clearly, is really important to you.
• I just want to say thanks for bringing your passion, knowledge, and focus to this forum. IMO, it's a welcome breath of fresh air!
Cheers,
Mark

ROTFL, well thanks.

I actually used this pattern to prevent deletes by users. In my databases the "delete" key never deleted anything, all it did was set the trash flag and cleared the active flag. That is just me. It is not my job to tell other developers what to do, only what I did and why.

There was a ton of "they are both the same thing, you only need one of them". It is obvious (to me) that is not true. And since there were so many "you only need one", it is obvious (to me) that an explanation is necessary.

But I do have passion, knowledge and focus!

 

[jwcolby54]

ROTFL, well thanks.

I actually used this pattern to prevent deletes by users. In my databases the "delete" key never deleted anything, all it did was set the trash flag and cleared the active flag. That is just me. It is not my job to tell other developers what to do, only what I did and why.

There was a ton of "they are both the same thing, you only need one of them". It is obvious (to me) that is not true. And since there were so many "you only need one", it is obvious (to me) that an explanation is necessary.

But I do have passion, knowledge and focus!

In the case of @The_Doc_Man , he was actually asking HOW I did it. A class with event sinks enforcing rules is the how.

 

[jwcolby54]

@jwcolby54 - see what I meant in Post #10

Nothing in my original post indicated in any way that only a single flag can represent two different pieces of information. It can be done obviously but we are database folks. We can also denormalize our data and keep info about two things in a single field or table but we don't. In fact we go to great lengths to NOT do that, to normalize our data. I am normalizing my data by having two fields for two different things.

 

[cheekybuddha]

your flags represent "state" and a record can be in only a single state at a time. The record is active/inactive/trash. It can't be more than one of those at one time.

 

[The_Doc_Man]

Just for completeness, the validity test is simple: The record is in a valid state if (Active IMP (NOT Trash)) is TRUE.

 

[The_Doc_Man]

LOL, I don't understand this one. A valid state is anything other than trash and active simultaneously. There are THREE valid states. What does "a valid state" mean?

I was showing a simple test for determining whether your record with TWO semi-independent flags are in a valid configuration. I wrote it as a response to those who complained about the complexity of the test. It's a freakin' one-liner test with two parameters. I agree it shouldn't be so hard as some folks are making it out to be.

Imp operator

VBA language reference

learn.microsoft.com

You have to look at the truth-table for the IMP Boolean operator. In this table, you can test those two flags in a single statement to see if they are in a valid configuration. Per your discussion, the only invalid configuration is if both ACTIVE and TRASH are true at the same time. The expression I mentioned is a simple test for exactly that case. The ONLY invalid state for IMP is when the left operand is TRUE and the right operand is FALSE.

ACTIVE	TRASH	NOT TRASH	ACTIVE IMP (NOT TRASH)
FALSE	FALSE	TRUE	TRUE
FALSE	TRUE	FALSE	TRUE
TRUE	FALSE	TRUE	TRUE
TRUE	TRUE	FALSE	FALSE

 

[jwcolby54]

I was showing a simple test for determining whether your record with TWO semi-independent flags are in a valid configuration. I wrote it as a response to those who complained about the complexity of the test. It's a freakin' one-liner test with two parameters. I agree it shouldn't be so hard as some folks are making it out to be.

Imp operator

VBA language reference

learn.microsoft.com

You have to look at the truth-table for the IMP Boolean operator. In this table, you can test those two flags in a single statement to see if they are in a valid configuration. Per your discussion, the only invalid configuration is if both ACTIVE and TRASH are true at the same time. The expression I mentioned is a simple test for exactly that case. The ONLY invalid state for IMP is when the left operand is TRUE and the right operand is FALSE.

ACTIVE	TRASH	NOT TRASH	ACTIVE IMP (NOT TRASH)
FALSE	FALSE	TRUE	TRUE
FALSE	TRUE	FALSE	TRUE
TRUE	FALSE	TRUE	TRUE
TRUE	TRUE	FALSE	FALSE

I think I see the problem here. Businesses run by rules. It is not a state table it is a business decision. Active is a state. Trash is a state. Each of these are business decisions.

A paint color record is Active or Trash. Active means that we sell more than 10000 gallons a month. The president of the damned company decided this. Not some flunky in IT. We sell millions of gallons of white a month, we sell 5 gallons of that icky green color. We are not going to deal with a paint color where we only ever sold a five gallons. The business rule is that Active means historically we sold more than x gallons a month. A muckety muck makes that decision.

The business rule for deleting records: We have never ever sold that color. We never even produced it. EVER. Any paint color that was never even manufactured will be deleted. Somebody silly sales guy loved shades of green. He decided that we would have 10 THOUSAND colors of green. He lobbied persistently and got 10000 shades of green. Only 1 ever sold. We have 9,993 green color records where we have never sold a single gallon. We need to clean up our paint color tables. The president of the company makes that decision. Not some flunky in IT.

There are RULES for what defines Active. It is not a binary "related to Trash" thing.
There are RULES which define Trash. It is not a binary thing "related to Active"

This is not some foreign concept guys. Rules run businesses.

A client is active if he buys more that $10000 a year. It costs us $20,000 a year to even deal with a customer. We have people who process orders, who pick orders, who drive orders around. We lease office space for these workers. We are not dealing with customers who don't buy enough stuff from us. The president of the company issues an edict. "Make all customers who purchase below X dollars inactive. Send them a letter. Do not process any orders below $1000 for that customer. If they want to be active, then they will order a larger order."

The president of the company makes that BUSINESS decision, not some flunky in IT. Active is a state. In ANY TABLE it is a state. It is unrelated to trash, in ANY SENSE OTHER THAN a record cannot be active and trash at the same time.

An order was placed a month ago. It was an active order. We were working on it. The client came on hard times. They called up and asked us to place the order on hold. We make it inactive. We didn't trash it. A BUSINESS DECISION is made to make that order inactive. That client goes out of business. Hmmmm NOW we need to delete that order. It was never processed, it will never be processed. The PRESIDENT dictates that Inactive Orders, where we never heard from the client again will be deleted. A business decision. A muckety muck makes that decision, not some flunky in IT.

WE are flunkeys in IT. We do not decide that some state table defines active and trash. It is NOT a state table. It is TWO ENTIRELY UNRELATED STATES of whatever table it is in. Muckety mucks make decisions as to what these states actually mean and when each of these ENTIRELY UNRELATED states will change.

Go to Amazon. "This product is not currently active. We don't know if it will ever be sold again". It says that right on the product page on many different things. A BUSINESS DECISION somewhere out in the world, NOT A STATE TABLE!!!

If you want to make this silly argument that it is nothing more than a state table with the Muckety Mucks in your client's company then so be it. It is not my job to argue that. When the President tells you that HE makes these decisions and you need to go somewhere else...

Now that I am calm again (ahhhooooommmmm this is what I do for a living... Ahhhooooommmmm this is what I do for a living), Trash was a flag that I created out of thin air to trap the delete key and prevent order entry flunkies from deleting records. To prevent sales flunkies from deleting records. The records may in fact end up deleted. Or not. They might have been "deleted" by a flunky somewhere hitting delete. I created this flag because the IT department was complaining that they were having to restore backups too often because some flunky deleted stuff they shouldn't. In many if not most cases REAL DELETES should come from delete queries being executed because some muckety said to do so.

That doesn't change the fact that behind the scenes, BUSINESS DECISIONS made by muckety mucks drive these very real states.

I was oh so hoping that we could discuss the mechanics of classes implementing the trash flag, how events can enforce a rule. I guess not. Good luck with your state table. And so with that I am out of here.

 

[The_Doc_Man]

@jwcolby54 - You are frustrated with this big flap over two flags vs. a single state variable. You see the flags as governing certain SEPARATE (and binary) business actions/decisions. You implemented them separately based what you thought was necessary and appropriate. You caught flak when some folks latched onto something that was a pragmatic solution to the problems you faced. Unfortunately, certain of our members don't appreciate pragmatic solutions.

You have one record that can be in multiple simultaneous states if the putative actions related to those states are independent actions. When we discussed in an earlier post HOW you implemented your screening to decide if something was set incorrectly, my response was a simple expression that tests this question: Do I have to take action because of a specific combination of flag settings that are incompatible with each other? All I wanted to do was to demonstrate that it wasn't the complex test that some folks thought it was. If the purpose of my response wasn't so clear, I apologize for the misunderstanding.