HT/VT Reporting Website Overview

Discussions around miscellaneous technologies and projects for the general membership.
marlattrj-p40
New Member
Posts: 24
Joined: Tue Jun 05, 2007 4:31 pm

#91

Post by marlattrj-p40 »

brado426 wrote: Whenever a teacher reports, the system plugs the Visit-type ID into the "Results" database table. The VisitType ID in the VisitType table is joined with the VisitType ID in the Results table. If we allow the Visit Types to be modified/Deleted/Added in the "Visit-Types" table, what is going to happen to all the Visit Type relationships that were previously reported? If significant changes were made to the Visit-Types table, the reported visits would link to the wrong Visit-Type or possibly even link to nothing.
I have run into this problem before in the past and my solution is normally to add a called "enabled" and allow the values "1" or "0" depending on weather or not the Visit-Type is wanted or not. Don't allow the Visit-Types to be deleted just disabled and enabled.

-Rich
User avatar
mkmurray
Senior Member
Posts: 3255
Joined: Tue Jan 23, 2007 9:56 pm
Location: Utah
Contact:

#92

Post by mkmurray »

brado426 wrote:Here's a technical concern that I'm not sure how to solve....

Whenever a teacher reports, the system plugs the Visit-type ID into the "Results" database table. The VisitType ID in the VisitType table is joined with the VisitType ID in the Results table. If we allow the Visit Types to be modified/Deleted/Added in the "Visit-Types" table, what is going to happen to all the Visit Type relationships that were previously reported? If significant changes were made to the Visit-Types table, the reported visits would link to the wrong Visit-Type or possibly even link to nothing.

The system currently stores up to one year of historical data so that the "History" report can be generated.

It is almost as if we would need to allow the user to configure the Visit Types the first time they used the system but never allow it again. Either that or we would need to do an UPDATE of all the IDs that were previously submitted whenever a change was made to the VisitTypes table.

The more I think about this, the more complex it gets. :o

I'm not sure that there are any easy answers here. Please enlighten me.

Brad O.
I would say never remove a VisitType. There's really no reason to. Just add a column to the table that signifies whether that VisitType is an active option for a Teacher to select. That way, deactivated ones can still show up in historical reports. I doubt the VisitTypes will ever get over 15 or 20 anyway.
User avatar
mkmurray
Senior Member
Posts: 3255
Joined: Tue Jan 23, 2007 9:56 pm
Location: Utah
Contact:

#93

Post by mkmurray »

marlattrj wrote:I have run into this problem before in the past and my solution is normally to add a called "enabled" and allow the values "1" or "0" depending on weather or not the Visit-Type is wanted or not. Don't allow the Visit-Types to be deleted just disabled and enabled.

-Rich
LOL, yeah what he said...
User avatar
brado426
Member
Posts: 313
Joined: Sun Feb 11, 2007 9:50 pm
Location: Foothill Ranch, CA
Contact:

#94

Post by brado426 »

That makes sense. So the administrative menu for this will only allow you to add new Visit Types or Enable/Disable an existing Visit Type. Of course, Visit Type name changes would have to be prohibited.

Brad O.
russellhltn
Community Administrator
Posts: 31975
Joined: Sat Jan 20, 2007 2:53 pm
Location: U.S.

#95

Post by russellhltn »

brado426 wrote:Of course, Visit Type name changes would have to be prohibited.
I guess it depends on how much you want to trust your admin. It might be desirable to change the phraseology(visit, visit complete, successful visit, we came - we saw - we taught), but renaming "Visited" to "Out of Town" would be rather disastrous.

Perhaps a pop-up warning message during the rename function?

I'd also be amiss if I didn't point out the possibility of NOT referencing the visit type table and recording the current accepted value of the table into the history DB (or whatever it is). That prevents any changes to the visit type table from affecting past months. I have mixed feelings about that, but I wanted to make sure we saw all the options.
User avatar
WelchTC
Senior Member
Posts: 2085
Joined: Wed Sep 06, 2006 8:51 am
Location: Kaysville, UT, USA
Contact:

#96

Post by WelchTC »

brado426 wrote:Here's a technical concern that I'm not sure how to solve....

Whenever a teacher reports, the system plugs the Visit-type ID into the "Results" database table. The VisitType ID in the VisitType table is joined with the VisitType ID in the Results table. If we allow the Visit Types to be modified/Deleted/Added in the "Visit-Types" table, what is going to happen to all the Visit Type relationships that were previously reported? If significant changes were made to the Visit-Types table, the reported visits would link to the wrong Visit-Type or possibly even link to nothing.

The system currently stores up to one year of historical data so that the "History" report can be generated.

It is almost as if we would need to allow the user to configure the Visit Types the first time they used the system but never allow it again. Either that or we would need to do an UPDATE of all the IDs that were previously submitted whenever a change was made to the VisitTypes table.

The more I think about this, the more complex it gets. :o

I'm not sure that there are any easy answers here. Please enlighten me.

Brad O.
If I understand the problem correctly, the way to resolve this is to never show the user the "Visit Type ID" but instead show them the "description". Because you are linking the talbes using the "ID", the description can change all you want. Of, however, a user wishes to delete a "visit type" after it has been used, there are two ways to handle the situation.
  1. Prohibit it. Most DB's allow you to put referential checks on relationships that would throw a DB error if you try to delete a record that has links to it.
  2. Set the related to tables Visit Type ID to "null" indicating that the type was removed. Again, most DB's allow a referential check to do this for you automatically.
Did I understand your problem correctly?

Tom
scion-p40
Member
Posts: 259
Joined: Sun Apr 22, 2007 12:56 am

#97

Post by scion-p40 »

RussellHltn wrote:Another thought: "Out of town for the month". Last I looked membership records weren't to be moved unless the person would be gone for more then 3 months. That leaves a lot of "home for the holidays" or "home for summer". Especially for singles and student wards.

(Yeah, I know. I keep pushing for simplification and yet I keep thinking up more things.) :rolleyes:

But I guess my prior question still holds: A drop-down category or put it in "notes"?
In watching this discussion, it occurs to me that two layers of reporting might help. For example:
HT visited
HT not visited
letter
email
phone
refused
out of town
not in the ward (i.e: moved & the records need to go elsewhere)
other (defined locally)
other
VT visited
in person
phone
letter
VT not visited
email
refused
out of town
not in the ward
other
other

Local admin (RS pres, EQ pres, etc) could flag types of visits for the appropriate visited/not visited category. Statistical information could then be readily generated for the officially counted data (as determined by local leaders per Tomw) and for the subsets of visited and not visited for local planning purposes.

This would suit both types of needs.

It would also be helpful to extract historical data for visits to particular households/individuals (HT/VT). This would help leaders better analyze events in areas where leadership is more fluid, such as YSA & military wards.

NOTE:
Topics should be indented under each of the following headings:
HT visited (as a default, HT visits are in person, so I have no additional detail here)
HT not visited
VT visited
VT not visited
User avatar
brado426
Member
Posts: 313
Joined: Sun Feb 11, 2007 9:50 pm
Location: Foothill Ranch, CA
Contact:

#98

Post by brado426 »

RussellHltn wrote:

I guess it depends on how much you want to trust your admin. It might be desirable to change the phraseology(visit, visit complete, successful visit, we came - we saw - we taught), but renaming "Visited" to "Out of Town" would be rather disastrous.

I think people would want to change the phraseology, but allowing changes like that sets the user up to cause problems. Along the lines of your example, just think if someone changed "Visited" to "Not Visited." All the historical data would be completely wrong.

I don't think we should be trusting the 'Admin. too much in this case. There would likely be hundreds of 'Admins' with varying levels of computer experience.

Tom W. wrote:


If I understand the problem correctly, the way to resolve this is to never show the user the "Visit Type ID" but instead show them the "description". Because you are linking the talbes using the "ID", the description can change all you want. Of, however, a user wishes to delete a "visit type" after it has been used, there are two ways to handle the situation.
  1. Prohibit it. Most DB's allow you to put referential checks on relationships that would throw a DB error if you try to delete a record that has links to it.
  2. Set the related to tables Visit Type ID to "null" indicating that the type was removed. Again, most DB's allow a referential check to do this for you automatically.

Yes, that was what I was pretty much what I was thinking. There is rarely a need to display the ID to the user.

The problem with setting an ID in the "Visit Types" table to null is that all the previously reported Visit-Types in the results table would suddenly not link to anything. We would need to somehow specify what all those links were supposed to link to. Perhaps a pop-up that allowed the user to "re-assign" all previously reported Visit Types to another value if they try to delete a Visit-Type.

I'm leaning towards only allowing adds, enables and disables, and not allowing deletes. Then, we would have the option to create a maintenance task that deleted any Visit-Types that did not exist in the "Results" table after a year to keep the "Visit-Types" table clean.

Brad O.
User avatar
mkmurray
Senior Member
Posts: 3255
Joined: Tue Jan 23, 2007 9:56 pm
Location: Utah
Contact:

#99

Post by mkmurray »

brado426 wrote:I'm leaning towards only allowing adds, enables and disables, and not allowing deletes. Then, we would have the option to create a maintenance task that deleted any Visit-Types that did not exist in the "Results" table after a year to keep the "Visit-Types" table clean.
And as for the rename functionality, I would just leave it at disabling the old and creating a new.
User avatar
brado426
Member
Posts: 313
Joined: Sun Feb 11, 2007 9:50 pm
Location: Foothill Ranch, CA
Contact:

#100

Post by brado426 »

mkmurray wrote:And as for the rename functionality, I would just leave it at disabling the old and creating a new.

Absolutely! I think this solution would provide the flexibility we are looking for while restraining the admins from creating problems.
Post Reply

Return to “Other Member Technologies”