Town Government Personnel Database

[Joshua Smith]

All,

Last night I had a long chat with Barre's town clerk and the administrative assistant to our BOS, about developing a database for tracking personnel stuff for Barre. In particular, they are looking for a system that can help keep track of all the appointing the board needs to do, and all the stuff the state requires us to track (getting sworn in, taking the ethics test every two years, acknowledging receipt of the open meeting law, etc.). As with the open meetings, they will still need to keep lots of paper as the official records, but this database will help keep track of everything in an "unofficial" capacity. (That is, it will be correct, but it won't be definitive.)

I'm thinking that this could be a part of the mytowngovernment.org site: sort of a subsidiary application, which has some connections to the open meeting notice system. The two are clearly related, but there are lots of people who need to be tracked from a personnel standpoint that do not post meetings. So I'm thinking that there will be two separate systems, but they will be aware of each other. For example, if a town uses both systems, then a board's page could automatically list all the members of that board.

As we talked through the different kinds of positions they need to track, it became clear to me that this is a pretty complicated problem. There are elected officials, multi-year appointments to boards, annual appointments to boards, annual appointments of employees (fire & police), appointed contract employees (typically department heads), regular employees who are not appointed but still need to take the ethics test, and so on.

And, of course, we need to keep track of multiple people on boards with staggered term-ending dates, and mid-term appointments for people who leave boards.

It also seems like this would be a great place to keep track of the org chart of town government, and heck, why not throw the job descriptions in here, too!

They need the system to keep track of phone numbers, email addresses, and mailing addresses; but, of course, the system needs to hide that info from the general public.

But the system should be available to the general public, since giving them the ability to find this information on the web will reduce the need for the town clerk's office to look it up for them.

Our BOS would use the system to send out various notices (do you want to be re-appointed? you have been reappointed! etc.), and keep track of who has responded and how. It will use email to communicate with people where possible, and make it easy to communicate by phone or mail when necessary.

Our Town Clerk would use the system to keep track of who has done what and when, and be able to generate reports so she can remind people about stuff like taking the ethics test.

So….. would any of you be interested in using a system like that? Do you already *have* a system like that, and can help get us pointed in the right direction with some of the tricky design questions? If you think this might be useful to your town, let me know, so I can make sure the design is right for everybody, not just for our little town :)

Thanks!

-Joshua

[Nancy Burnham]

Hi Josh,

YES, definately interested in it. It sounds great and will make our jobs a
little bit easier and more efficient.

Thanks for all you do!!

Nancy

All,

So�.. would any of you be interested in using a system like that? Do you

[Davis, Debbie]

I would be very interested.
I need to get together with you to get on my government. It is a little slow now so I would have the time to do it.
Debbie Davis
Leicester

-----Original Message-----
From: mytowngovernme...@googlegroups.com [mailto:mytowngovernme...@googlegroups.com] On Behalf Of Joshua Smith
Sent: Tuesday, July 19, 2011 9:36 AM
To: mytowngovernme...@googlegroups.com
Subject: Town Government Personnel Database

All,

Last night I had a long chat with Barre's town clerk and the administrative assistant to our BOS, about developing a database for tracking personnel stuff for Barre. In particular, they are looking for a system that can help keep track of all the appointing the board needs to do, and all the stuff the state requires us to track (getting sworn in, taking the ethics test every two years, acknowledging receipt of the open meeting law, etc.). As with the open meetings, they will still need to keep lots of paper as the official records, but this database will help keep track of everything in an "unofficial" capacity. (That is, it will be correct, but it won't be definitive.)

I'm thinking that this could be a part of the mytowngovernment.org site: sort of a subsidiary application, which has some connections to the open meeting notice system. The two are clearly related, but there are lots of people who need to be tracked from a personnel standpoint that do not post meetings. So I'm thinking that there will be two separate systems, but they will be aware of each other. For example, if a town uses both systems, then a board's page could automatically list all the members of that board.

As we talked through the different kinds of positions they need to track, it became clear to me that this is a pretty complicated problem. There are elected officials, multi-year appointments to boards, annual appointments to boards, annual appointments of employees (fire & police), appointed contract employees (typically department heads), regular employees who are not appointed but still need to take the ethics test, and so on.

And, of course, we need to keep track of multiple people on boards with staggered term-ending dates, and mid-term appointments for people who leave boards.

It also seems like this would be a great place to keep track of the org chart of town government, and heck, why not throw the job descriptions in here, too!

They need the system to keep track of phone numbers, email addresses, and mailing addresses; but, of course, the system needs to hide that info from the general public.

But the system should be available to the general public, since giving them the ability to find this information on the web will reduce the need for the town clerk's office to look it up for them.

Our BOS would use the system to send out various notices (do you want to be re-appointed? you have been reappointed! etc.), and keep track of who has responded and how. It will use email to communicate with people where possible, and make it easy to communicate by phone or mail when necessary.

Our Town Clerk would use the system to keep track of who has done what and when, and be able to generate reports so she can remind people about stuff like taking the ethics test.

So..... would any of you be interested in using a system like that? Do you already *have* a system like that, and can help get us pointed in the right direction with some of the tricky design questions? If you think this might be useful to your town, let me know, so I can make sure the design is right for everybody, not just for our little town :)

Thanks!

-Joshua

[Tara]

Good morning,
I just forwarded this to the IT people who were responsible for coordinating with you with the original web site. I think this sound great, and I cannot thank you enough for all you have done!! I imagine we will be in touch!!
Tara Whiting
West Tisbury

-----Original Message-----
From: mytowngovernme...@googlegroups.com [mailto:mytowngovernme...@googlegroups.com] On Behalf Of Joshua Smith
Sent: Tuesday, July 19, 2011 9:36 AM
To: mytowngovernme...@googlegroups.com
Subject: Town Government Personnel Database

[Berlin Town Clerk]

Greetings,

James Wheeler, the Assistant Town Clerk, has made a similar database so we
could track employees. He works tonight so I can ask him to send it along.
Thanks for thinking of the Clerks.

Eloise

----- Original Message -----
From: "Joshua Smith" <Joshua...@charter.net>
To: <mytowngovernme...@googlegroups.com>

Sent: Tuesday, July 19, 2011 9:35 AM
Subject: Town Government Personnel Database

All,

So�.. would any of you be interested in using a system like that? Do you

[Nancy Blackmer]

I have been on vacation - just got back to the office this morning - this
sounds great. I currently use a program hosted by General Code that sounds
similar. I hope that there might be some way to bring that information into
your program. If not, I will just enter it all again.

Nancy M. Blackmer, MMC/CMMC
Town Clerk
6 Prospect St.
Orange, MA 01364
town...@townoforange.org
Phone - 978-544-1100, ext. 101
Fax - 978-544-1134
www.townoforange.org

----- Original Message -----
From: "Joshua Smith" <Joshua...@charter.net>
To: <mytowngovernme...@googlegroups.com>
Sent: Tuesday, July 19, 2011 9:35 AM
Subject: Town Government Personnel Database

All,

So�.. would any of you be interested in using a system like that? Do you

[Joshua Smith]

Is there some option in that program to "export" the data to a spreadsheet? Or is there a web interface that perhaps I could scrape it from?

Getting my hands on a real dataset would definitely be helpful!

-Joshua

> So….. would any of you be interested in using a system like that? Do you already *have* a system like that, and can help get us pointed in the right direction with some of the tricky design questions? If you think this might be useful to your town, let me know, so I can make sure the design is right for everybody, not just for our little town :)
>
> Thanks!
>
> -Joshua
>

[Nancy Blackmer]

Can you use this? Not exactly sure if this is all the information - I found
the ability to Export this so thought I would try it. Is this a good
format, or is there another format that would be better? I had several
choices so if there is something that would be better, let me know.

-Joshua

> So�.. would any of you be interested in using a system like that? Do you

[Nancy Blackmer]

I will look into that.

Nancy M. Blackmer, MMC/CMMC
Town Clerk
6 Prospect St.
Orange, MA 01364
town...@townoforange.org
Phone - 978-544-1100, ext. 101
Fax - 978-544-1134
www.townoforange.org
----- Original Message -----
From: "Joshua Smith" <Joshua...@charter.net>
To: <mytowngovernme...@googlegroups.com>

Sent: Thursday, July 21, 2011 7:38 AM
Subject: Re: Town Government Personnel Database

-Joshua

> So�.. would any of you be interested in using a system like that? Do you

[Nancy Blackmer]

I think this is actually the information I would want carried over - I tried
this in another format. Let me know which is better, or if you prefer
something different. Thanks.

Nancy M. Blackmer, MMC/CMMC
Town Clerk
6 Prospect St.
Orange, MA 01364
town...@townoforange.org
Phone - 978-544-1100, ext. 101
Fax - 978-544-1134
www.townoforange.org
----- Original Message -----
From: "Joshua Smith" <Joshua...@charter.net>
To: <mytowngovernme...@googlegroups.com>

Sent: Thursday, July 21, 2011 7:38 AM
Subject: Re: Town Government Personnel Database

-Joshua

> So�.. would any of you be interested in using a system like that? Do you

[Joshua Smith]

This looks like a good start. Clearly, there is a lot more information I'm thinking we need to keep track of, though.

Are you paying a fee for the General Code software? (Don't worry, I wouldn't charge for this functionality; I'm just curious what the market rate is for this stuff.)

>> So….. would any of you be interested in using a system like that? Do you already *have* a system like that, and can help get us pointed in the right direction with some of the tricky design questions? If you think this might be useful to your town, let me know, so I can make sure the design is right for everybody, not just for our little town :)
>>
>> Thanks!
>>
>> -Joshua
>>
> <grouptermhistory.xls>

[town...@townoforange.org]

Yes, I do pay a fee - I am not in the office so I am not sure the amount -
I will check on Monday and let you know.

Nancy

[Nancy Blackmer]

I forgot to look yesterday - the amount is $100.00 per year.

>> So�.. would any of you be interested in using a system like that? Do you

[Town Government Support]

Here is my first cut at a database schema for this application. I know that most of the people on this list have probably never *seen* a database schema before, but I hope that it will be somewhat self-explanatory.  What I'm trying to do is capture all the different kinds of data we'll need to track, and the relationships between those data.  I welcome any and all feedback, questions, suggestions…

# a town in the system, e.g., Barre

Town:

- name

- zip

- administrators (list of email addresses)

# a group of people, such as a department, board, or committee

Organization:

- Town

- name

- description

# a role filled by a person, such as selectman, zoning board member, or fire chief

# multiple people may be filling the same role at the same time

Role:

- Town

- Organization

- title

- description

- attached job description file

- elected (true/false)

- appointed (true/false)

- contract (true/false)

- renumeration type (stipend, salary, hourly, volunteer)

- term (number of years)

- term limit (number of terms / unlimited)

- must be sworn in (true/false)

# a person being tracked by the system

Person:

- Town

- name

- telephone

- email

- postal address

- ethics test (list of dates)

- open meeting acknowledgement (list of dates)

# a term of office filled by a particular person

Term:

- Town

- Role

- Person

- title # e.g., Chairman, Clerk, Agent, Member

- start (date)

- end (date)

- sworn in (true/false)

# notification sent to a person

Notification:

- Town

- Person

- Role (if applicable)

- type (appointment query, appointment notice, ethics test reminder, open meeting reminder, swear-in reminder)

- method (email/phone/postal)

- acknowledged (true/false)

[Joshua Smith]

All,

I've recently had a chance to add this new functionality to the mytowngovernment.org system. I've got the schema implemented, along with forms for adding organizations, roles, people, and terms. Administrators get to see everything, and the general public just gets the non-confidential top-level information (who is on what board, and for what term).

I haven't built the notice-sending logic yet. I figured getting the database populated with real data for a town would be a good start.

If you are interested in being a beta tester for this new capability, let me know. We can try to work out a batch upload from excel (like we did to bootstrap your towns), or you can just enter the information directly into the system using the forms on the site.

-Joshua

All,

Last night I had a long chat with Barre's town clerk and the administrative assistant to our BOS, about developing a database for tracking personnel stuff for Barre.  In particular, they are looking for a system that can help keep track of all the appointing the board needs to do, and all the stuff the state requires us to track (getting sworn in, taking the ethics test every two years, acknowledging receipt of the open meeting law, etc.). As with the open meetings, they will still need to keep lots of paper as the official records, but this database will help keep track of everything in an "unofficial" capacity. (That is, it will be correct, but it won't be definitive.)

I'm thinking that this could be a part of the mytowngovernment.org site: sort of a subsidiary application, which has some connections to the open meeting notice system.  The two are clearly related, but there are lots of people who need to be tracked from a personnel standpoint that do not post meetings. So I'm thinking that there will be two separate systems, but they will be aware of each other. For example, if a town uses both systems, then a board's page could automatically list all the members of that board.

As we talked through the different kinds of positions they need to track, it became clear to me that this is a pretty complicated problem. There are elected officials, multi-year appointments to boards, annual appointments to boards, annual appointments of employees (fire & police), appointed contract employees (typically department heads), regular employees who are not appointed but still need to take the ethics test, and so on.

And, of course, we need to keep track of multiple people on boards with staggered term-ending dates, and mid-term appointments for people who leave boards.

It also seems like this would be a great place to keep track of the org chart of town government, and heck, why not throw the job descriptions in here, too!

They need the system to keep track of phone numbers, email addresses, and mailing addresses; but, of course, the system needs to hide that info from the general public.

But the system should be available to the general public, since giving them the ability to find this information on the web will reduce the need for the town clerk's office to look it up for them.

Our BOS would use the system to send out various notices (do you want to be re-appointed? you have been reappointed! etc.), and keep track of who has responded and how. It will use email to communicate with people where possible, and make it easy to communicate by phone or mail when necessary.

Our Town Clerk would use the system to keep track of who has done what and when, and be able to generate reports so she can remind people about stuff like taking the ethics test.

So….. would any of you be interested in using a system like that? Do you already *have* a system like that, and can help get us pointed in the right direction with some of the tricky design questions? If you think this might be useful to your town, let me know, so I can make sure the design is right for everybody, not just for our little town :)

Thanks!

-Joshua

[town...@townoforange.org]

I am off this week - I tried to send an e-mail earlier but was having
trouble with my internet connection so I don't know if you got it - I
would be interested in this feature and am willing to test it.

Nancy Blackmer, Orange

[Nancy Burnham]

I think this will be a great feature and I am willing to be a tester.

 

Nancy Burnham

Athol Town Clerk

----- Original Message -----

From: Joshua Smith

To: mytowngovernme...@googlegroups.com

Sent: Wednesday, December 28, 2011 11:03 AM

Subject: Re: Town Government Personnel Database

[Shawn Dooley]

I would be interested in being a beta tester.  

Shawn C. Dooley

Town Clerk

1 Liberty Lane

Norfolk, MA 02056

508.528.1400

sdo...@virtualnorfolk.org

[Shirley MA Town Clerk]

 

I'd be interested in being a tester too.  We currently keep some of this info in an Access database in the BOS and some in an Access database in my office and would love to have all the info in one central location.

 

Amy McDougall