CLAS membership database (March 6, 2003)

 

The CLAS collaboration maintains lists of members, interested parties, students and others in order to manage the responsibilities and governance of the collaboration and to assist in deriving author lists for publications.  This document will describe the database used to maintain that information.

 

Responsibilities

The relevant responsibility of the membership committee are:

1.        Maintain a list of full, term, limited  members of the collaboration.

2.        Maintain the CLAS phonebook web page.

3.        Maintain the following e-mail lists:

a.        Membership: clas_membership

b.       Voting: vote_rp, vote_mh, vote_sn

c.        Working Group Lists: strnuc

d.       General interest: hallb

4.        Provide lists of authors for CLAS publications.

5.        Maintain up-to-date service statements for each member institution.

6.        Provide a list of members for shift assignment.

 

There are different people responsible for updating this information.

1.        Membership committee chair would be the database manager and should be able to change most fields. There may be a restriction on the access to shift related data.

2.        Individual members should be able to manage a subset of their information.

3.         Institutional representatives may need to manage some of the member information and some  of the institution information.

4.        Shift assignment person would need access to all data relating to shift assignments fields.

5.        SOS committee chair perhaps would maintain some status information (not currently implemented).

 

At present, the shift assignment responsibility has evolved separately from the membership database. Relevant information for shift assignments resides in an independent MYSQL database.  The merger of these two databases is currently underway.

 

DATA Overview

There are two types of information residing in the database:

 

It is also important to distinguish among three categories of data: critical, useful, and interesting.  A member's correct name, institution  and  CLAS membership status must be correct and would be considered critical. Telephone numbers, web sites, addresses are useful but not critical. Because the collaboration is small it would be easy to store the names of undergraduate students that are working on CLAS related projects. This type of information might be useful at some later date and falls into the category of interesting.

 

The design of the database depends on the three factors:

 

Access and Management

A user account clasmbr has been set up on the Jefferson lab cue. The CLAS membership chairperson will inherit the account.  This account will have the privileges to manage the membership database. MySQL is the database management software that has been chosen. The designers of the database that will replace the calibration map text files chose MySQL and the membership committee will follow their lead. The software is free and widely used.  The database will be placed on the JLAB machine claspc13 (clasdb). The CLAS membership database will be one of several databases on this machine.

 

There are different approaches to access and security. The database was designed based on MYSQL access features. With MySQL you can create users with different access who can view and modify only certain fields. The problem with this approach is that the restrictions cannot be applied to individual records. Separate tables are required to hold all fields that need to be accessible by a subset of the users only.

 

The decision was made to build the database on the model of institution management. Each institute would have editing access to certain data for its members only.  An account for each institution would be set up and the "edit" access for these users would be restricted to member information from their institutions and only to a subset of these fields. The users would be able to change the fields accessible. Choice of the database tool is not unique. Since  MYSQL handles the privileges,  any database tool can be used. With a OCBD connection, for example, Microsoft Access can read and edit the database or with JAVA and the MYSQL toolset a specialized program can be developed to read and edit.  A set of simple web based tools should be available so that users can perform the basic functions. The original plan for assigning MYSQL  privileges is shown below. Institutional managers would be able to change most of the information for members of their institutes. Other managers, for example the shift or service managers, would be able to access a set of necessary fields. A generic account would allow all members to view the information but not to change anything.

 

 

Database users (Original Plan):  A possible plan for database access

 

USER

Privileges

1

clasmbr

High, Major access

2

SOS committee chair

High, Major access

3

Shift assignment person

High, Major access

4

Institution 1

Restricted subfields, email

5

Institution 2

Restricted subfields

……

Restricted subfields

38

Institution 35

Restricted subfields

39

Generic

Read only

 

Access and Management New Approach

With the help of the the JLAB Electronic Media personnel a web based update tool is under development.  This approach may rely on JLAB computer accounts and passwords to control access to the database. In this approach the web tool would have access to all of the relevant fields. The web tool would control what changes were made based on the users ability to logon to JLAB computers.  A field containing the members JLAB account name would be required and the web tool would restrict update access based on the account name. The separate institution tables could be merged since the MYSQL database access mechanisms would not be used.

 

Tables

The database tables are shown below. Only the critical information is listed.  The shift management information was included in the same way as it appears in the current shift database.  Any person involved with the collaboration could appear.  The information necessary to determine the publication status and affiliations would reside in a table with time fields (historic data) so that the status at the time of the data recording could be used to determine eligibility.  Some of the tables are included so that the scope of the database could be expanded.  Data could be added to the CLAS status tab, for example, to include new categories.

 

 

 

Table overview

 

TABLE

 Description

Critical Information

1

person

List of all people with any relevant connection . Store information that is not generally updateable.

Name, publication status, working group, shift information

2

person-institute-N-info (N: 1 for each inst.)

Members managed information

Address, email

3

historical information

Information recorded with a time stamp

CLAS status, affiliated institutions, time

4

institutions

Institute information and service information

name, address, representative, status

5

Clas status

List of possible CLAS status options

Type (Tern, Full...)

6

working groups

list of the working groups

Names (Nstar …)

7

publications

list of publications

name, responsible person, (not impelemted)

8

experiment periods

list of experiment periods (e1a…)

name, time period

9

pub/member correlation

correlates the authors to the publications

 (not implemented)

10

pub/exp period

correlates the time periods with the publications

 (not implemented)

11

acc schedule

current acc schedule loaded for shift generation

 (not implemented)

12

expert schedule

completed shift schedule for experts

 (not implemented)

13

novice schedule

completed shift schedule for novices

 (not implemented)

14

status change

List of the important status changes (members added..)

date,comment (not impelemted)

 

 

 

 

 

A list of all the tables and the recommended fields follow.

 

1

person

 Field

 Index

pointer

 edit

 Value/comment

 

 

ID

Key

 

 

 

 

title

 

clasmbr

 

 

 

first name

 

clasmbr

 

 

 

middle name

 

clasmbr

 

 

 

last name

 

clasmbr

 

 

 

expert

 

shift

accounting fields that keep track of totals

 

 

owl_ex

 

shift

  ""                              ""

 

 

day_ex

 

shift

  ""                              ""

 

 

eve_exp

 

shift

  ""                              ""

 

 

owl_nvv

 

shift

  ""                              ""

 

 

day_nv

 

shift

  ""                              ""

 

 

eve_nv

 

shift

  ""                              ""

 

 

pager

 

shift

 

 

 

shift status

 

shift

expert,worker,not trained

 

 

shift status2

 

shift

excused,available

 

 

shift contact

Ptr

shift

points to the person responsible (advisor)

 

 

pub status

 

clasmbr

available for none, all, photon only …

 

 

pubname

 

clasmbr

for publications (should move ?)

 

 

working group

 

clasmbr

photon  (should move ?)

2

person-institute-N-info (N: 1 for each inst.)

 

 

 

 

 

 

person

Ptr

 

link back to person table

 

 

address

 

 

 

 

 

web page

 

inst-rep

 

 

 

e-mail 1

 

inst-rep

 

 

 

e-mail 2

 

inst-rep

 

 

 

primary phone

 

inst-rep

 

 

 

alt phone

 

inst-rep

 

 

 

position

 

inst-rep

fac,ungrad,grad,postdoc,Ustaff,Jlabstaff,other

3

historical information

 

 

 

 

 

 

start time

 

clasmbr

 

 

 

end time

 

clasmbr

 

 

 

person

Ptr

clasmbr

 

 

 

institution

Ptr

clasmbr

 

 

 

Clas status

Ptr

clasmbr

 

 

 

comment

 

clasmbr

 

4

institutions

 

 

 

 

 

 

id

Key

clasmbr

 

 

 

person

Ptr

clasmbr

institution representative

 

 

acronym

 

clasmbr

 

 

 

name

 

clasmbr

 

 

 

mailing add

 

clasmbr

dept,address…

 

 

phone

 

clasmbr

 

 

 

fax

 

clasmbr

 

 

 

web page

 

clasmbr

 

 

 

CLAS status

 

clasmbr

member,non-member (not implemented)

 

 

password

 

shift

 

 

 

SOS status

 

service

okay

 

 

SOS link

 

service

web address or file name..

5

status change

 

 

 

 

 

 

ID

 Key

 

 

 

 

date

 

clasmbr

 

 

 

effected change

 

clasmbr

 

 

 

comment

 

clasmbr

 

6

Clas status

 

 

 

Add new CLAS categories if necessary

 

 

id

 Key

clasmbr

 

 

 

type

 

clasmbr

 Full,Term,limited,applicant,observer,interested

7

working groups

 

 

 

 

 

 

ID

 Key

clasmbr

 

 

 

WG name

 

clasmbr

Real Photon,Nucleon Structure,Multihadron,no

 

 

chairperson

 

clasmbr

 

 

 

cc rep

 

clasmbr

 

 

 

comment

 

clasmbr

 

8

publications

 

 

 

 

 

 

ID

 Key

clasmbr

 

 

 

title

 

clasmbr

 

 

 

resposible Pkey

 

clasmbr

 

 

 

edit status

 

clasmbr

fixed,editable  (for removing authors)

9

experiment periods

 

 

 

 

 

 

id

 Key

 

 

 

 

name

 

 

e1a,g6…

 

 

comment

 

 

 

 

 

start of period

 

 

 

 

 

end of period

 

 

 

10

pub/member correlation

 

 

 

 

 

 

publication

Ptr

clasmbr

 

 

 

person

Ptr

clasmbr

 

11

pub/exp period

publication

Ptr

clasmbr

 

 

 

exp period

Ptr

clasmbr

 

12

acc schedule

 

 

 

 

 

 

date

 

shift

 

 

 

exp period

 Ptr

shift

 

 

 

energy

 

shift

 

 

 

day

 

shift

 

 

 

 

 

 

 

13

expert schedule

 

 

 

 

 

 

day date

 

shift

 

 

 

owl Pkey

 

shift

 

 

 

day Pkey

 

shift

 

 

 

eve Pkey

 

shift

 

14

novice schedule

 

 

 

 

 

 

day date

 

shift

 

 

 

owl Pkey

 

shift

 

 

 

day Pkey

 

shift

 

 

 

eve Pkey

 

shift

 

15

CLAS roles/interests

 

 

 

other than the primary groups

 

 

Id

Key

 

 Just put any other stuff in the this list and

 

 

Name

 

 

You can correlate

 

 

 

 

 

 

 

 

 

 

 

 

16

Roles/member correlation

 

 

 

 

 

 

Person

Ptr

 

 

 

 

role

Ptr

 

 

 

The original data was derived from a list kept by previous membership chairs. It contained a starting list with dated  corrections and addendums. The fields and tables are summarized below.

 


Database March 2003

AMERICAN

id

 

people

id

 

workGroups

id

 

not used yet

 

ANDES

person

 

 

title

 

 

name

 

newInfo

id

ANL

institute

 

 

firstname

 

 

chair

 

 

title

ASU

address1

 

 

middlename

 

 

rep

 

 

firstname

BATES

address2

 

 

lastname

 

 

comment

 

 

middlename

BONN

address3

 

 

shiftcontact

 

 

 

 

 

lastname

CALTECH

address4

 

 

shiftstatus

 

 

 

 

 

pubname

CISCO

address5

 

 

shiftstatus2

 

PUBstatus

id

 

 

workingroup

CMU

web

 

 

pubname

 

 

comment

 

 

web1

CNU

phone

 

 

PUBstat

 

 

 

 

 

phone1

CUA

fax

 

 

Wgroup

 

 

 

 

 

phone2

DUKE

pubname

 

 

 

 

experiments

id

 

 

fax

EMMY

email1

 

expPeriod

id

 

 

name

 

 

email1

FIU

email2

 

 

name

 

 

title

 

 

email2

FSU

phone2

 

 

start

 

 

 

 

 

affilinst1

GB

 

 

 

end

 

limited

id

 

 

affilinst2

GBEDINBURGH

 

 

 

comment

 

 

exp id

 

 

affilinst3

GBGLASGOW

 

 

 

 

 

 

person id

 

 

admininst1

GEORGETOWN

 

 

history

id

 

 

 

 

 

 

GWU

 

 

 

start

 

not used yet

 

 

 

 

HAMPTON

 

 

 

end

 

authors

id

 

 

 

INDSTRA

 

 

 

person

 

 

person

 

 

 

INFNFR

 

 

 

institute1

 

 

pub

 

 

 

INFNGE

 

 

 

institute2

 

 

 

 

 

 

IPCZECH

 

 

 

institute3

 

not used yet

 

 

 

 

ITEP

 

 

 

CLASstatus

 

publPeriods

id

 

 

 

JLAB

 

 

 

comment

 

 

period

 

 

 

JMU

 

 

 

 

 

 

pub

 

 

 

JMU22

 

 

institutes

id

 

not used yet

 

 

 

 

KYUNGPOOK

 

 

 

acronym

 

publications

id

 

 

 

LUND

 

 

 

rep

 

 

rep

 

 

 

MIT

 

 

 

name

 

 

title

 

 

 

MOSCOW

 

 

 

address1

 

 

status

 

 

 

NCATU

 

 

 

address2

 

 

comment

 

 

 

NONE

 

 

 

address3

 

not used yet

 

 

 

 

NSU

 

 

 

address4

 

changes

id

 

 

 

ODU

 

 

 

address5

 

 

when

 

 

 

OHIOU

 

 

 

web

 

 

what

 

 

 

ORSAY

 

 

 

phone

 

 

comment

 

 

 

ORST

 

 

 

fax

 

 

 

 

 

 

PITT

 

 

 

CLASmember

 

Temporary/Tests

 

 

 

RICE

 

 

 

SOSokay

 

Itable

temp table

 

 

 

ROMA

 

 

 

SOSlink

 

tableA

name

 

 

 

ROMA

 

 

 

 

 

CLAS_status

id

 

 

 

RPI

 

 

CLASstatus

id

 

institutes_OLD

 

 

 

 

SACLAY

 

 

 

type

 

 

 

 

 

 

SANPAULO

 

 

 

 

 

 

 

 

 

 

SCAROLINA

 

 

 

 

 

 

 

 

 

 

TEST

 

 

 

 

 

 

 

 

 

 

TMPA

 

 

Feb-02

 

For the author lists.
The address used for REVTEX
 address2…address 5
alphabetized by address1
institute name is not used

 

 

TRENT

 

 

mysql  Ver 11.15

 

 

Tulane

 

 

Distrib 3.23.48,

 

 

UCLA

 

 

for pc-linux-gnu (i686)

 

 

UCONN

 

 

Connection id:          6615

 

 

UK

 

 

Current database: membership

 

 

UMASS

 

 

Current user:clas@localhost

 

 

UNCW

 

 

Current pager:          stdout

 

 

 

 

 

 

UNH

 

 

Using outfile:          ''

 

 

 

 

 

 

UNIONC

 

 

Server version:   3.23.48-log

 

 

 

 

 

 

URICH

 

 

Protocol version:       10

 

 

 

 

 

 

UTEP

 

 

Con:Localhost via UNIX socket

 

 

 

 

 

 

VIRGINIA

 

 

Client characterset:    latin1

 

 

 

 

 

 

VSU

 

 

Server characterset:    latin1

 

 

 

 

 

 

VT

 

 

UNIX socket

 

 

 

 

 

 

WISCONSIN

 

 

/var/lib/mysql/mysql.sock

 

 

 

 

 

 

WM

 

 

 

 

 

 

 

 

 

 

YEREVAN

 

 

 

 

 

 

 

 

 

 

deceased