Opinions on Database Design - Redundancy vs. Ease/Speed? 3

[Vie]

I have a semi-complex question about a design issue. My Access database tracks people through a step-by-step process. For example each person goes through step A, to step B, to step C, to step D, and so on until they reach step X. Some steps can be skipped along the way and others cannot. As they go through each step their “status” is considered to be in one of number of pre-determined states. For instance, when a person has completed step A and step B, his or her status is “Under Review,” or when a person has completed step A and steps C and D, his or her status is “Ready for Briefing.”

For simplicity’s sake, let’s say that steps A-X are recorded all in one table which I’ll call Table1. Each person’s status is decided based on each whether or not there is a value in each of the steps (ie, not what the value is).

As it is now, Table1 also includes a field for Status although Status actually is a redundancy since the null or non-null state of FieldA, FieldB, FieldC, and so on, are what determines the status of each person.

I have a form which contains a subform in datasheet view that shows each person, all the steps, and their status. This is where the user will enter the data into the FieldA, FieldB, etc. As they enter data, FieldStatus automatically updates to the appropriate status through a series of AfterUpdate events for each of the step Fields. The user NEVER enters anything in the Status field. It is all done programmatically.

OK. Phew. Hope this is making sense –

This all works nicely but it strikes me as being against the standards of sound data modeling and design to have the Status redundancy in Table1. Like a calculated value, I’d prefer to see it only at run-time in the form – a sort of virtual status.

My question is:

Which is more advisable, the way I am doing it now (redundancy) or the way I think it should be (shown virtually but not stored in a table)?

If it’s the latter, I’ll have to post again because I’m not sure how to do this or if it’s even possible in Access.

 

[MakeItSo]

I would definitely prefer the latter - or a mix:
You could keep the status field in the data entry form unbound until data entry is completed.
Since the Status will probably not change gravely any more, you could store the temporarily final Status in your table.

-->UPDATE query in the Before_Update() event of the form?

Cheers,
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[JeremyNYC]

Actually, Andy, I think that increases the risk, as the user could update a bunch of the fields and then yank the power cord or give a three-finger salute, and then the status field would be out of synch with the other fields.

Unless the code to figure the status takes a very long time to run, which I really doubt, I would definitely make the status field calculated on the fly. This is exactly one of the situations for which the rules of normalization were written.

Vie, I would say post again, with a description of what you're doing and the code involved. One way to approach this is to use a system like that used for the arguments in the buttons parameter of a message box. I'm sure someone with a formal programming education can come up with a name for it, though I don't know it. But it's a really slick system. It allows you to have a huge variety of combinations of things passed to that parameter. The way it's done is that each constant is assigned a different value, and the values are pegged such that no two combinations can add up to the same thing. Here's a list of the values (though they'll be a _lot_ easier to read in the help file):
Constant Value Description
vbOKOnly 0 OK button only (default)
vbOKCancel 1 OK and Cancel buttons
vbAbortRetryIgnore 2 Abort, Retry, and Ignore buttons
vbYesNoCancel 3 Yes, No, and Cancel buttons
vbYesNo 4 Yes and No buttons
vbRetryCancel 5 Retry and Cancel buttons
vbCritical 16 Critical message
vbQuestion 32 Warning query
vbExclamation 48 Warning message
vbInformation 64 Information message
vbDefaultButton1 0 First button is default (default)
vbDefaultButton2 256 Second button is default
vbDefaultButton3 512 Third button is default
vbDefaultButton4 768 Fourth button is default
vbApplicationModal 0 Application modal message box (default)
vbSystemModal 4096 System modal message box
vbMsgBoxHelpButton 16384 Adds Help button to the message box
VbMsgBoxSetForeground 65536 Specifies the message box window as the foreground window
vbMsgBoxRight 524288 Text is right aligned
vbMsgBoxRtlReading 1048576 Specifies text should appear as right-to-left reading on Hebrew and Arabic systems

You could assign a series of values to different data states in your controls and write a function that checks each control in a separate if (or select) structure and then uses one big select structure to come up with the value for the status. Of course, depending on what your data model actually looks like, there may be simpler ways of going about it.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Vie]

Thanks Andy and Jeremy.

Jeremy, I'm intrigued by your suggestion. Never conceived of that before but it sounds like just the right thing.

I have no idea where to begin to understand how to do it though, so, before I get into the particulars of my situation, I wonder if you can point in the right direction for beginning to learn about such a, for lack of a better word, "strategy?"

I understand what you're hinting at with the built-in enums but I haven't much experience using constants. I feel like if I could just get a clue where to begin, I might pick up enough know-how that I could post an intelligent question when I can't manage to make it work!

Vie

 

[JeremyNYC]

Vie,

Well, I don't think you need to go so far as build the enums or constants. It would be nice, but it sounds like a lot of work.

Instead, I would make a grid of your controls and how the business rules translate from those controls to the status field. For example, if Ctl1 is null, the status can't be more than 1. If ctl2 has a date later than today, the status has to be 4. Etc.

Then, build a series of logical structures that walsk throught he controls, adding to a single variable, depending on what's in each control.

Then, build a select structure that generates the status. Or, you could make a table to store the possible numeric values and the corresponding status levels.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[johnwm]

The big advantage of using a binary series as the values for independent tests is that they can always be obtained with a logical AND. So if your values for test1 is 1, test 2 is 2, test 3 is 4 and test 4 is 8, then logical ORing those values (rather than adding them) gives you a set of BIT flags set in a LONG.
[tt]
test1 test2 test3 test4
1 2 4 8[/tt]

Then test1 OR test3 gives 5, and if you OR that with test3 again, you still get 5. To extract individual data, use AND.

If myLong AND 4 Then 'test3 is true

Using constants then makes it all more readable:

Const test1 = 1
Const test2 = 2
Const test3 = 4
Const test4 = 8

'Then to set values:
myLong = myLong OR test1  'stores test1 as True

'To check:
If myLong AND test2 Then  'True if test2 is True

'To Unset:
myLong = myLong AND NOT test3  'stores test3 as False

Note sample code is VB syntax, but the same principle works in most languages. Use a LONG as your storage and you will be fine up to 15 separate test values.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Vie]

Thanks for the input everyone! I grasp some of it but not all of it so I'm going back to the books. As I'm working on it, I'm sure I'll have more questions. Thanks again, Vie

 

[ESquared]

I LOVE using binary values to store information, I'm currently doing it for security permissions in one of my applications.

Maybe some examples will help you out, Vie.

Here's my take on the subject:

Note: I'm ignoring the values you place into each step, and simply assigning false or true (0 or 1) based on whether the value is null or not. If I had more details I could give you better ideas.

You have 25 steps, A through X. Assign each step a power of two starting with 1:
A 1 (2^0)
B 2 (2^1)
C 4
D 8
...
W 8388608 (2^23)
X 16777216 (2^24)

Now you can store a user's step-completed-state in a single Long (4-byte integer) variable.

All steps completed: 1+2+4+8+16... + 8388608+16777216 = 33554431 (notice this is 2^25 -1)
Steps A and B completed: 1+2 = 3
Steps B and C completed: 2+4 = 6

Next, you can use boolean operands to determine your status. Say lngStat is the variable containing the summed value above.

Note that when you're doing boolean logic, your result is a number, which you must then inspect to see if its value is satisfactory. Sometimes you only care if it is nonzero, other times it must be a specific number.

My way to think about it is that AND 'cuts out' or 'turns off' bits, and OR 'includes' or 'turns on' bits.

UnderReview = (lngStat AND 3) = 3  [COLOR=green]' 1+2, steps A and B must be complete.[/color]
UnderReview2 = lngStat AND 3 [COLOR=green] 'Steps A [B]or[/B] B must be complete.[/color]

ReadyForBriefing = (lngStat AND 13)  [COLOR=green]' 1+4+8, steps A, C, and D must be complete.[/color]

AnotherState = ((lngStat AND 48)>0) AND ((lngStat AND 13)=13)  [COLOR=green]'Steps A, C, and D must be complete, as well as one of either E or F.[/color]

I'm sure some of these can be simplified... my brain isn't working right now.

With some work, I think I could come up with a single logic statement to return your "Status" value. I'd of course have to know what constitutes each status in order to do it... but you could even have a lookup table that converts the value to the text description of the status.

I hope that wasn't too horribly confusing!!!

 

[Vie]

ESquared,

Thanks so much for your very helpful post! I've been trying to get up to speed with bitwise operators and have read some helpful articles about it but I'm still having trouble applying it to my situation.

I've been playing around with enumerated constants which are kind of nifty...

Public Enum VoucherStatus

vsNoApplication = 1
vsApplicationInReview = 2
vsEligible = 4
vsIneligible = 8
vsReadyforBriefing = 16
vsVoucherInReview = 32
vsVouchered = 64

End Enum

On my form where the user edits data, I need something like:

For i to (rs.RecordCount) 'evaluate Voucher Status for each row in the recordset

     If IsNull (Me.tbxApplicationDate) Then
        tbxVoucherStatus = vsNoApplication
     ElseIf Not IsNull (Me.tbxApplicationDate) _
        And IsNull(tbxEligible) Then _
        tbxVoucherStatus = vsApplicationInReview
     ....etc.

Or, better yet, a select case? That's the idea anyway, but I know this sort of thing won't work and isn't quite what you're talking about.

I'm really fishing around here for clues.

First off, I don't really understand how to evaluate the state of each of the controls and, second, how do I store the sum of the controls in the variable, lngVoucherStatus?

I must seem like a total dodo! I'm not looking for anyone to give me the complete answer, I like doing the work, but I just a little leg up in the right direction. It seems like the more I learn, the more I realize just how little I know!

Again, thanks so much!
Vie

 

[ESquared]

Well, the bitwise stuff is very useful when states can all be off or on. In my security application, any user type could conceivably be in our out of a "security profile," so it makes sense to store each one in a bit, as long as I don't have more than 31 usertypes.

For example, it's just as likely my bitmask would be 1111000010101010 as it would to be 0101010111110000.

In your situation, there is a sequential progression from earlier states to later ones. Your bitmasks will largely be ones followed by zeroes: 1111111100000000 1000000000000000 1111111111111110. To me this means other (easier) methods besides bit-storage could do the job.

Give me a rundown of what consists of each state, because I am sure that I have more, possibly better ideas for you. I don't care what things are called, just specifically I need to know:

Which step numbers/letters can be skipped and when/why? Are there steps where one or the other can be skipped but not both? What goes into the value for each textbox when a step is completed?

Right now my mind is going in the direction of some tables:

[tt]PeopleSteps
PersonID StepID Value
1 A "whatever"
2 A "frog"
2 B "caribou"
2 C "doss"
1 B "tungsten"

Steps
StepID Status Condition Value
A 1 0 1 'C=0: can be reached from the start
B 1 1 1 'C=1: Must have completed A to reach here
C 2 1 2 'C=1: A or B are sufficient'
D 3 2 3 'C=2: must have completed C because C has a value of 2

Note: for this layout, status and value are the same, but they conceivably might not be throughout all the steps.

Statuses
StatusID Name
1 Under Review
2 Ready for Briefing[/tt]

Here's the idea:
- Each step returns a current status.
- Each step has a value associated with having reached it.
- Each step specifies the level required to have been reached before it can be accessed.

Current step for a person:

[tt]CurrentStep = DMax("StepID","PeopleSteps","PersonID=2")[/tt]

The value of that step:

[tt]CurrentStepValue = DLookup("Value","Steps","StepID=CurrentStep")[/tt]

If a step is available to be displayed/used or not:

[tt]Function IsAvailable(StepID, CurrentStepValue) as Boolean
IsAvailable = CurrentStepValue >= DLookup("Condition","Steps","StepID=" & StepID)
End Function[/tt]

And with some careful consideration for your values in your table, you can specify what you need. You can handle "any one of these x steps must be completed to go to the next." If you need "any two+..." then you can add another column to your table and jiggle the numbers again. Let me know if this is the case and I'll help.

I guess it sounds complicated... let me know if you want more ideas.

Someone else could pipe up, too.

 

[Vie]

Thanks for taking the time, ESquared. I'm a bit slow getting back to TT the past couple of days with tax time, etc.

Anyway, yeah, it does sound kind of complicated...but, in answer to your questions:

Which step numbers/letters can be skipped and when/why? Are there steps where one or the other can be skipped but not both? What goes into the value for each textbox when a step is completed?

I'll do my best to describe the scenario. Basically, it's an application process. Everyone starts out as status "No Application" (ApplicationDate is Null) and then their status is rubber-stamped, or not, at various stages along the way to becoming "Vouchered" (VoucherIssueDate Not Null).

All the values that go into the textboxes when a given step is complete are dates except one.

So a person applies (AppDate Not Null) and their status is considered "Application Under Review" until the next step is completed. The next step is the yes/no value such that, when the review of their application is complete, each person is deemed either "Eligible" (yes) or "Ineligible" (no).

OK, so:

A ApplicationDate - null or not null
B Eligible - yes or no

For those deemed eligible, the next step (actually I'm leaving out a few steps here for clarity) is to attend a briefing. While BriefingAttendedDate is Null and Applied is not Null and Eligible is Yes, the person is "Ready for Briefing."

C BriefingAttendedDate - null or not null

Once they've attended but before they've been vouchered, they are "Voucher Under Review." When this review is complete, they are issued a voucher (VoucherIssuedDate not null).

D VoucherIssuedDate - null or not null

Now for the wrinkle. Some people can go directly from being Eligible to being Vouchered without attending a briefing. It's not common but it does happen. The reason why these people can skip the briefing is, unfortunately, a bit murky. It's determined by another agency whose practices are left intentionally opaque. The why's are kind of beyond the purview of the company I'm working for.

These dates and eligible/yes-no are stored in a table with the person's ID number something like what you did in the "PeopleSteps" table, except that the individual steps don't have separate ID's (I just used A, B, C, D, etc. to shorthand it in my original question) and its constructed more like:

[tt]PersonID[tab]AppDate[tab]Eligibity[tab]BfgAttdDate[tab]VchIssuedDate[/tt]
[tt] 1 [tab]null [tab]null [tab]null [tab]null[/tt]
[tt] 2 [tab]8/12/03[tab]Yes [tab]null [tab]null[/tt]
[tt] 3 [tab]9/12/03[tab]No [tab]null [tab]null[/tt]
[tt] 4 [tab]1/25/04[tab]Yes [tab]2/2/04 [tab]2/16/04[/tt]
[tt] 5 [tab]7/25/03[tab]Yes [tab]null [tab]8/1/03[/tt]

The Voucher Statuses of each person above are:

Person 1 is "No Application"
Person 2 is "Eligible"
Person 3 is "Ineligible"
Person 4 is "Vouchered"
Person 5 is "Vouchered"

Does that help any?

Vie

 

[ESquared]

Create a new query/view, call it "PersonEligibility." It will look something like this:

[tt]SELECT PersonID, PersonStatus(PersonID);[/tt]

Make a Module with a function. Note: I'm doing this with DAO because I can. You can do it with ADODB if you like. You could do it with individual DLookups. It don't mattah.

[tt][tt]Public[/tt] [tt]Function[/tt] PersonStatus(ThePerson [tt]As[/tt] [tt]Long[/tt]) [tt]As[/tt] [tt]Long[/tt]
[tt]Dim[/tt] lngStatus [tt]As[/tt] [tt]Long[/tt]
[tt]Dim[/tt] rstR [tt]As[/tt] DAO.Recordset
[tt]Dim[/tt] dbsD [tt]As[/tt] DAO.[tt]Database[/tt]

[tt]Set[/tt] dbsD = CurrentDb
[tt]Set[/tt] rstR = dbsD.OpenRecordset("SELECT * FROM PeopleStatus WHERE PersonID =" & ThePerson & ";")
[tt]With[/tt] rstR
[tt]'do all your logic here[/tt]
[tt]'start from the end[/tt]
[tt]If[/tt] [tt]Not[/tt] IsNull(!StepX) [tt]Then[/tt]
lngStatus = 25 [tt]'Complete![/tt]
[tt]ElseIf[/tt] ...
[tt]End[/tt] [tt]If[/tt]
[tt]Select[/tt] [tt]Case[/tt]
[tt]Case[/tt]
[tt]End[/tt] [tt]Select[/tt]
Etcetera
[tt]'and finally[/tt]
PersonStatus = lngStatus
[tt]End[/tt] [tt]With[/tt]
[tt]Set[/tt] rstR = [tt]Nothing[/tt]
[tt]Set[/tt] dbsD = [tt]Nothing[/tt]
[tt]End[/tt] [tt]Function[/tt][/tt]

Then you can use the function when you want to know the status of one single person, in code or a query, and you can use the query to easily join a table against it without having to remember the syntax/function name.

Other ideas:
Write a function to return the last non-null column name from the recordset.

For lngCntr=rstR.Fields.Count-1 to 0 step -1
if not isnull(rstr.fields(lngcntr)) then
LastColName=rstr.fields(lngcntr).name
exit for
end if
next

And do something with the returned name. Of course, you'd have to be careful about what order your columns are in and if there are any non-affecting columns to the right of them.

If you restructure your data so each step is its own record (like in my example) and give each step a number to sort by, you could do a single query to return the last non-null step number for such-and-such person.

I'm afraid I went a bit crazy with all the suggestions above, giving you way more than you needed to just calculate the answer you wanted. I do apologize!

 

[Vie]

Absolutely no need for apologies - are you kidding me! You've been terrific. Your suggestions get the job done.

But I have one last lingering question, probably kind of thick-skulled but, if the subform where each person and their various application dates, briefing dates, voucher status is shown is in datasheet view, is there a way to see all the people in a clump like that with each of their statuses correct (as opposed to looking at them each individually - conceivably, there will eventually be thousands of people in this database and the user may be looking at a group of 1000 or more at a time and wanting to see each of their statuses as they scroll through the subform)? I've been experimenting with it today and find that the status for everyone reflects the status of the last person in the recordset because, in reality, the subform is just one unbound textbox control for the Voucher Status, not, say, 396 textboxes, one for each person. I guess what I'm trying to say is, like in an Excel spreadsheet, if there were a colum for each of the fields personID through Step X, you could also have a column for voucher Status and have it sum up the values in the step fields and then display the corresponding status for each row. Is there a way to make the subform behave something like that?

(I'm guessing probably not).

Hope you're having a good weekend!
Vie

 

[ESquared]

Yes. Set the subform's Record Source to be a query instead of a table name. I don't necessarily mean you have to go make and save a query, just click on the 3 little dots next to the Record Source property of your Form to invoke the Query Builder. Add the fields you like or just use .* and then add an additional column:

[tt]Status: PersonStatus([PersonID])[/tt]

which in SQL will look like

[tt]..., PersonStatus([PersonID]) AS Status[/tt]

Then you can just set your control's Control Source to Status and you're off! Make sure to make this control uneditable by either Locked or Disabled or both (you may or may not know that both is different than just one of each, try them out).

Let me know how else I can help. It's an interesting project and I would be interested to know what you finally decide.

-E²