Active / Trash - Whose responsibility is it? (1 Viewer)

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

 

[jwcolby54]

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.

@Pat Hartman Trash is not the same thing as Active. Entonces, one field will not do. At least without resorting to mapping the four possible boolean values into something like an integer.
Bit zero is the state of trash
Bit 1 is the state of active

0 = is not trash and is not active
1 = is trash, is not active
2 = is active is not trash
3 = is active and is trash (an invalid state)

A client can be active, or it can be inactive. Being inactive does not mean it is trash
A client can be trash, in which case it should not be active. So if it was active, set it to inactive.
A client can be inactive but not be trash.

You get my point. Active and trash have two completely different meanings. Trash means (in my universe) that someone deleted it.

Which brings up an interesting point, to do this right we need an "Active state before deletion" flag. So if the record is recovered we don't have to jump through hoops to figure out the active state it was in before someone (accidentally presumably) deleted it.

The point of a trash flag is to be able to undelete the record. It is not to indicate the active state. The point of an Active flag is to indicate whether a client is active or not. It is not to indicate that the record was deleted.

 

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

 

[jwcolby54]

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.

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.

 

[jwcolby54]

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... this isn't hard. They are mutually exclusive TRUE, but not mutually exclusive false.

Would you place the first name and last name into the same field? That is denormalized. They are related but not the same thing. One is a first name, the other is a last name.

One is an active flag. The other is a deleted flag. They are states of a record. A record can be active or inactive. It can be trash or it can not be trash.

It CANNOT BE TRASH AND ACTIVE. That is true but it can be INACTIVE and NOT TRASH.It can be active and not trash. These are two DIFFERENT states of the record. They are related. They both belong to whatever entity you are discussing. But they are in no way the same thing, they are not discussing the same STATES.

To place them both in a single field is, in fact, denormalizing these two things, forcing them to be in a single field. They do not belong in a single field any more than the first name and last name belong in the same field. If you came on a table with the first name and last name in the same field you would snort in disgust at the designer. You can force the active and trash into the same field but I am going to snort in disgust at the designer.

As for the previous active state... that is a third piece of data. It discusses what the active state was BEFORE DELETION. It does not belong in one big single field either. These are THREE, DIFFERENT pieces of information. As such they belong in THREE DIFFERENT FIELDS. Oh wait, yea that's right, let's add the middle name into our one big name field. That's the ticket. REALLY?

Take every field in the customer record and force all of them into the same field. You can do that. What a mess that is.

Sorry, I just see no logic in cramming a bunch of different things into a single field "just to save fields". That is the very definition of denormalization!