Cece and Chris:
I made some changes to the tables we created Wednesday but
didn’t write down a list of all those changes. The structural changes should
be clear from my revisions below. I’ve already filled in the values of the "lookup"
tables (Regions, Affiliations, Areas, and Subjects), so those don’t need to
be recreated.
Be sure to keep track of time spent on this project!
Kevin
Database design (revised 7/19/03)
The existing program is at http://www.uiuc.edu/ro/SLA/connections.htm
.
We will use the blog to keep a record of our communication while
building the database, and we will puts comments in the code to document what
we're doing.
All pages will be php files. Chris will plan our filenames and
variable names that we'll all use, especially when passing data between our
files.
Two types of records (in separate tables):
- Professionals (Note that they may volunteer to be mentors,
host site visits, be guest speakers, or any combination of the above.)
- Students
There are two types of users: program participants (professionals
and students) and the program coordinator. The program coordinator will be the
SLA officer in the charge of the SLA Connections program. This person will take
responsibility for the database, but other SLA officers will have access to
it as well. Regardless, the coordinator functions will be password protected
by one username and password that they will all share.
The program coordinator has these technical responsibilities:
- Keep track of the username and password for gaining access
to the coordinator functions of the database, and change it as necessary.
(We will need to provide directions on how to do this.) [These pages will
be password protected by following the directions at http://www.wam.umd.edu/howto/Web-howto.html#restrict
or http://httpd.apache.org/docs/misc/FAQ.html#user-authentication
. I'm not sure exactly how we'll need to do it.]
- Keep track of the username and password for the SLA account
on Prairienet, and change as necessary.
- Change the script username and password if necessary. These
will be stored in one include file accessed by all the scripts.
- Reregister the SLA account on Prairienet by July 1 every year,
and update the contact information on file with Prairienet every time new
officers are chosen.
Table structure:
When the coordinator assigns a mentee (student) to a mentor (professional),
the student should be keyed to the professional’s ID. That is, there's a one-to-many
relationship of mentors to mentees. A mentor can have more than one mentee,
and mentees only get one mentor.
While it would have been most efficient to store some answers
on forms as boolean values or as number codes which would be displayed properly
by the interface, we decided to just store answers for a number of questions
in the student and professional forms as text strings that are the answers (such
as for Updated, Alum, and Approved). For some fields, though, (those whose values
are used in both student and professional records) we decided that we should
store the list of possible answers in a separate table, and student and professional
records would be keyed to the appropriate value in the other table. (That is,
the student and professional tables both have tinyint fields whose value gets
filled as the record number of the text string chosen from the list of possible
answers.) Having a separate table is better design philosophically, allows global
changes on the name of a value, and allows easy matching of mentors and mentees
across the two tables. And best yet, as soon as a person’s record is approved,
the person’s "other" gets added to the list of possible answers that
others can use.
So Regions, Affiliations, Areas, and Subjects are stored in these
separate "lookup tables". Users filling out forms will be given the
"other" option on all but Regions, and approved submitted values
will be shown in the interface in addition to the standard ones so that new
submissions can start using these.
PUBLIC INTERFACE [Cece]
There should be a disclaimer to students that this program is
not the same one as the one the Hendersons run through their classes.
It should explain that if you want to update your information,
resubmit the form and choose the option for "submitting an updated profile."
Participant forms (one for students, one for professionals)
will be like the current paper ones except:
- Professionals and students can only choose their top three
areas and specialties (keep these terms, and keep using "interest"
vs. "experience" in the forms) and must rank them. Likewise, they
only choose three affiliations. For all such fields, one of their answers
can be "other". The list of possible answers is drawn from those
that are on the paper forms as well as the "other" values of approved
records. So new "other" values get stored with the answers suggested
on the paper forms.
- When you choose the "other" option (will be in a
couple of places on both forms), it forces you to fill in the text box next
to it with some text describing what your "other" thing is. But
it limits you to 20 characters. There will be a note saying that people should
please limit their annotation to one thing (interest, specialty, or whatever
the "other" category is for).
- There should be a question on each form asking if they are
signing up for the program for the first time, submitting an updated profile,
or "not sure." This will be stored in the Updated field as "yes",
"no", or "not sure".
- When you submit the form, it will check for errors in all values,
highlight any that are incorrect, and ask you to fix them. Even when you "get
everything right", you'll still have the option to confirm. MySQL will
take care of giving all new records the value "N" for the Approved
field.
- Store addresses as long text strings with line break characters
in the field value.
Students form only:
- For students, add a question asking if they prefer someone
in "Champaign-Urbana", "Chicago area", "anywhere
in Illinois", "anywhere", or "other" (with a required
text box). If they choose "other", this value gets filled in the
AreaOther field in the Students table, not added to the Areas table as another
choice for others to use.
- Don't ask students which degree they're a candidate for (everyone
who participates so far is a master's student), but do ask if they're on-campus
or LEEP. Store this as "on-campus" or "LEEP".
- Ask students what their bachelor's degree is in, and ask if
they have any other degrees (as we do already for professionals).
Professionals form only:
- For professionals, ask them to check off professional organization
membership from the following options: SLA, SLA Illinois Chapter, all SLA
divisions, AALL, FAFLRT, ATLA, ARLISNA, Medical Library Association, Music
Library Association, Theatre Library Association, ASIST, and "other"
(with required value).
- Don't ask professionals for their graduation date or what LIS
school they have their degree from, but do keep asking how many years of experience
they have and whether they're a GSLIS alum.
When forms are submitted, new records are added to the appropriate
table, except they have a boolean field flagged to indicate that it’s a new
record. (This will probably be the default value for the field in the MySQL
table design.) It will remain this way until the coordinator approves the record.
When new records are created, they need to have the date and time
of submission automatically recorded.
COORDINATOR FUNCTIONS [Chris does interface, Kevin does results.php]
Queries accessible to only the project coordinator that
will generate pages allowing them to:
- View the whole professional table or the whole student table.
(Viewing the whole professional table is the same as submitting the coordinator
form described below without choosing any limiting criteria.) Coordinator
can delete records from this screen.
- Approve new records in each table. It should show each new
submission with a few current records before and after it in alphabetical
order by last name to help avoid having more than one record per person. (We
can’t rely on what they say when submitting regarding whether it’s a new record
because people forget that they've already registered.) Coordinator can delete
records from this screen (especially old versions for which people submit
replacements). After approving a new record, the interface will suggest searching
for a mentor for this person. The coordinator will be able to follow a link
to the "coordinator form" described below for searching for professionals.
This will open in a new window, and there will be a message at some point
suggesting that the user keep the student’s record open as well and switch
between the windows.
- View a list of professionals who volunteered to be mentors,
ranked by the amount of time that has passed since a mentee was assigned to
them. At the top of the list is those who have never been assigned a mentee.
These people should be ranked by how long they’ve been in the program, with
those who volunteered the longest time ago ranked first.
- View lists of email addresses of all mentors, all mentees,
all mentors and mentees, all professionals, and all people in the database.
The lists will be in a format that makes it easy to copy the addresses from
your browser window into an email program for mailing. There might be check
boxes for categories, so the coordinator can check all categories for which
s/he wants email addresses. The list will be given with a comma and space
between each email address, put inside an HTML <textarea> with directions
explaining that if you click once in the box and select all, you can get all
the addresses.
- View a list of all professionals sorted by how long they’ve
been in the program. Be able to restrict this to just professionals who don't
have assigned mentees.
We don't need any other particular reporting functions, such as
profiling the participants by address.
Coordinator form:
There will be one search screen for the coordinator that lets
you search for professionals by limiting the results to professionals:
- With a certain "area" (just use this term; the user
chooses from a dropdown list that includes "other").
- With a certain "subject" (just use this term; the
user chooses from a dropdown list that includes "other").
- With a particular location. (Though this will be based on the
address the person reports, it needs to be tied to the student form that asks
students if they want a mentor in Champaign-Urbana, Chicago, anywhere in Illinois,
etc.)
- With a certain degree (undergraduate and graduate) and major.
- With a graduate date greater than "x" years ago.
- Who are alumni of GSLIS.
- Who have a certain word in their job title (requires a textbox).
- Who are a member of a certain professional organization.
- Whose last name begins with a given text string.
- Who volunteered to host site visits.
- Who volunteered to be speakers.
The last option on the screen will ask you how you want to sort
the results. They can sort by any field we can engineer it to allow.
The coordinator can limit their results by as many as the above
criteria as they select.
Results page:
This file will display results differently and present different
options depending on which parameters it's passed by an interface file. We will
have one standard parameter for which each interface file will pass a different
value so results.php can detect how it should be displaying results.
- If the page was called by the coordinator form, it will have
an option to assign a returned record to an unassigned student (from a drop-down
box).
- Fields will be shown as rows, and returned records are in columns.
(It will be easier to read this way.)
- From here, you can assign a mentee (student) to a mentor (professional).
The date the assignment is made needs to be recorded, and the coordinator
needs to be given the entire record for both people so s/he can email them
to let them know that they've been matched.
posted by Kevin Hawkins at 7:30 PM
