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.

 

[Pat Hartman]

One example from my past, I used a pair of boolean fields called Active and Trash. I placed them in the vast majority of my tables. When a new record is created Active was set true and Trash is set false.

I hate to be critical but you should never use two fields when one will do. If you like the look of two checkboxes, use an Option Group. If you have more than two states, then a combo is appropriate. You would STILL never use two flags. You would use one. Frequently when you use a status flag, you also include a date indicating when that status was entered. When you only have two states, I also prefer the date/null approach.

 

[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.

 

[Pat Hartman]

I am normalizing my data by having two fields for two different things.

When the two things are mutually exclusive and that requires you to set both of them at the same time, you're fooling yourself into thinking the data is normalized.

Initially your description was of two states. It was only after we pointed out the flaw in keeping two fields when one would do that you introduced the third state. All the more reason to use a single variable to hold the three mutually exclusive states.

 

[Pat Hartman]

Would you place the first name and last name into the same field? That is denormalized.

Of course not. But 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. We don't need to have this discussion here. You have your opinion. I disagree. There is nothing else to talk about. I am not going to change your mind. You could change mine if there is other information I am not aware of but given the current information. We will need to agree to disagree

I am not suggesting mushing multiple values into a single field. Not sure where you are getting that from. The status field means one and only one thing at a time. Think of it like "department" or "marital status". A record can belong to only a single department at one time. There are four common values for marital status. In your case, for status - If a record is active, it is not inactive and it is not trash. If a record is inactive, it is not active and it is not trash. If a record is trash, it is not active and it is not inactive.

In binary, you have three values. 10, 01, 00. 11 is not valid. To determine inactive, you must test two fields. With only a single field, you have a simple query that always tests a single field for 1 of 3 values depending on what type of records you are interested in. With your current design, you need three separate queries. A separate query for each state because each state requires testing a different field or in the case of inactive, you must test two fields.

 

[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.