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.