Person Organization Module.

re: 12/17/01

The ps_person module will contain the information that is to be tracked concerning the people that are in the MSUFSTS database including fire fighters, instructors, fire chiefs, etc. The module entity-relationship diagram is depicted below.

PS ER

Table Definitions:

A summary of the table definitions depicted in the ER diagram above, is listed below. The complete definition of these tables can be found on the Max Mart, Inc. project web site and will be included in the final PS documentation.

There are five major themes modeled by these tables including personal information, Addresses, Phone numbers, Mailing lists, and organizations.

PERSON Information:

The person information is stored in the PS_PERSON table.

Table: PS_PERSON

Description: Person such as a student, fire fighter, EMS, Instructor, etc.

NAME	TYPE	WIDTH	PREC	NULL	DESCRIPTION
ID	NUMBER	10	0	NOT NULL	Generate id for the person.
ALTERNATE_ID	VARCHAR2	20		NOT NULL	This can be used for a DL number or if someone offers the SSN it can be placed here.
FIRST_NAME	VARCHAR2	15		NOT NULL
MIDDLE_NAME	VARCHAR2	15
LAST_NAME	VARCHAR2	20		NOT NULL
OLD_NAME	VARCHAR2	50			This is an OLD name for the person... such as a maiden name or if someone changes their name, could include last AND first name
SUFFIX	VARCHAR2	6			ie) Junior, 3rd, Senior
EMAIL	VARCHAR2	200
GENDER	VARCHAR2	6			MALE or FEMALE -- this field is not required
BIRTHDATE	DATE				This can be used to identify a person if someone offers their birthdate. It is NOT required.
ACTIVE	VARCHAR2	3		NOT NULL	Tells whether a person is active or inactive for reports. (YES or NO)
PASSWORD	VARCHAR2	20		NOT NULL	password is for Internet access to the Resource Center catalog and for On-Line registration for classes. Users will log on with their ID number or Alternate_ID value and password. They can change their password on-line.

Address Information:

The Address Information is stored in a combination of three tables. The first is SY_ADDRESS. This table stores ALL addresses that are used in the system. The second is the PS_ADDRESS table which links a person to a SY_ADDRESS record according to the TYPE of address. Address TYPES are stored in PS_ADDRESS_TYPE .

Table: SY_ADDRESS

Description: All addresses in the system will be maintained here. A person can have multiple addresses, an organization can have only one.

NAME	TYPE	WIDTH	PREC	NULL	DESCRIPTION
ID	NUMBER	10	0	NOT NULL	Generated id for the address that is in the table.
STREET_1	VARCHAR2	40		NOT NULL	The first line of the address... we are calling it street 1 but it is required so it could be a PO box or anything.
STREET_2	VARCHAR2	40			Second optional line of the address
CITY	VARCHAR2	30		NOT NULL	Required CITY of the address
STATE	VARCHAR2	2		NOT NULL	State code for the address.. Default is MT.
ZIP	VARCHAR2	10		NOT NULL	allows for a 12345-1234 zip.
ATTENTION	VARCHAR2	30			Allow for an ATTENTION: line on the address (optional)

Table: PS_ADDRESS

Description: The ADDRESSES that a person has of various TYPES. A person can have only ONE address of EACH TYPE. The address is stored in the SY_ADDRESS table. The ID of the address, ID of the person, and ID of the address Type is stored in this table.

NAME	TYPE	WIDTH	PREC	NULL	DESCRIPTION
PS_PERSON_ID	NUMBER	10	0	NOT NULL	Person for the address
SY_ADDRESS_ID	NUMBER	10	0	NOT NULL	Address of this type for this person
PS_ADDRESS_TYPE_ID	VARCHAR2	10		NOT NULL	Type of address this is

Table: PS_ADDRESS_TYPE

Description: Type of address such as HOME, OFFICE, WORK, etc. Categorizes the different addresses that a person has. A person can have only ONE of each type of address.

NAME	TYPE	WIDTH	PREC	NULL	DESCRIPTION
ID	VARCHAR2	10	0	NOT NULL	ex: HOME WORK

Phone Number Information:

The phone numbers for a person are stored in the PS_PHONE table. The phone number types such as Home, Work, Cell, etc are stored in the PS_PHONE_TYPE table.

Table: PS_PHONE

Description: Stores the person's various phone numbers that they have.

NAME	TYPE	WIDTH	PREC	NULL	DESCRIPTION
PS_PERSON_ID	NUMBER	10	0	NOT NULL	The id of the PERSON that this phone number belongs to.
PS_PHONE_TYPE_ID	VARCHAR2	10		NOT NULL	The type of phone number this is for this person
PREFIX	VARCHAR2	3		NOT NULL	Prefix or Area Code for this phone number
EXCHANGE	VARCHAR2	3		NOT NULL	exchange number such as the 265 in 406-265-4164
IDENTIFIER	VARCHAR2	4		NOT NULL	the 4164 in 406-265-4164 should be all digits
EXTENSION	VARCHAR2	4			an Extension number at a given phone number. Should be all DIGITS.

Table: PS_PHONE_TYPE

Description: Type of telephone number associated with the person. Ex: HOME WORK CELL A person can have EXACTLY ONE phone number for each type.

NAME	TYPE	WIDTH	PREC	NULL	DESCRIPTION
ID	VARCHAR2	10		NOT NULL	The description of the phone type such as HOME or CELL or MESSAGE

Mailing List Information:

The mailing list information stores the information needed to create mailing labels for mass mailing items to the people in the system. For example, if MFS produces a monthly news letter, then the mailing list tables will store who receives that news letter. A Mailing list can be one of two types: POSTAL or EMAIL. If the list is a POSTAL list then mailing labels will be printed for doing the mailing. If the list is EMAIL then an email message system will be used to send an email to each person on the list. The PS_MAILING_LIST table indicates what Address TYPE the list will be sent to for each person. When a list is created, the address type (address) for the person will be indicated. If the list is an EMAIL list, then no address type is allowed as the email address is in the person table.

Table: PS_MAILING_LIST

Description: The list of people that are getting mail for mail types. Intersect between PERSON and MAIL TYPE

NAME	TYPE	WIDTH	PREC	NULL	DESCRIPTION
PS_PERSON_ID	NUMBER	10	0	NOT NULL	Person receiving the mailing
PS_MAIL_TYPE_ID	VARCHAR2	5		NOT NULL	The type of mailing being sent
PS_ADDRESS_TYPE_ID	VARCHAR2	10			The person's address type that is going to be used for this mailing.

Table: PS_MAIL_TYPE

Description: Mailing TYPES that a person can indicate an interest in receiving such as FIRE CHIEF NEWS LETTER or EMS Monthly...

NAME	TYPE	WIDTH	NULL	DESCRIPTION
ID	VARCHAR2	5	NOT NULL	MFS created id .
LIST_TYPE	VARCHAR2	6	NOT NULL	Indicates if this list is a POSTAL list which means that it will be mailed through US Postal service or if this list is an EMAIL list which means that it will be mailed through an email message system
DESCRIPTION	VARCHAR2	50	NOT NULL	Description of the Mailing Type

Organization and Membership Information:

The organizations that subscribe to the MSUFSTS resource library are stored in the PS_ORGANIZATION table. The people in the system can belong to the organizations. The table PS_MEMBERSHIP tracks what organizations a person belongs to. It also tracks that person's position in the organization. Organizations are categorized by types found in the PS_ORGANIZATION_TYPE table.

Table: PS_ORGANIZATION

Description: An organization is an entity in the system that can sponsor training, check out resources, etc. Examples: Havre Fire Department, MSU Bozeman, Chinook EMS

NAME	TYPE	WIDTH	PREC	NULL	DESCRIPTION
ID	NUMBER	10	0	NOT NULL	Generated id for the organization.
NAME	VARCHAR2	100		NOT NULL	Name of organization. This can't be duplicated in the system. This will be enforced by the UNAME field which will be unique UPPERCASE.
UNAME	VARCHAR2	100		NOT NULL	THIS IS THE UPPERCASE of NAME. Enforces UNIQUE on name by enforcing it here.
SY_ADDRESS_ID	NUMBER	10	0	NOT NULL	This is the organization's address taken from the address table.
PS_ORGANIZATION_TYPE_ID	VARCHAR2	3		NOT NULL	Type of organization such as GOV, STATE.
COUNTY	VARCHAR2	30		NOT NULL	County that this organization is located in. It is initially picked from the COUNTY table.
PHONE	VARCHAR2	13			Phone number for organization.
FDID	VARCHAR2	5			The Fire Department's Montana ID. (FDID)
SHIPPING_DAYS	NUMBER	2	0	NOT NULL	Number of days to ship item from RC to organization. Default will be the value in SY_SYSTEM.SHIPPING_DAYS
PAID	DATE				The last date that the organization paid their dues for belonging to the MFS training center as a member organization.

Table: PS_ORGANIZATION_TYPE

Description: Holds the types of organizations and their yearly cost. ie. volunteer, private, local

NAME	TYPE	WIDTH	PREC	NULL	DESCRIPTION
ID	VARCHAR2	3		NOT NULL	User provided id for type of organization.
COST	NUMBER	5	2	NOT NULL	Yearly amount the RC charges the organization.
TYPE	VARCHAR2	50		NOT NULL	Type of organization. User provided long name for type of organization.

Table: PS_MEMBERSHIP

Description: Links the Person and Organization they belong to. A person could have more than one position in an organization and can belong to more than one organization.

NAME	TYPE	WIDTH	PREC	NULL	DESCRIPTION
ID	NUMBER	10	0	NOT NULL	Generated ID
PS_ORGANIZATION_ID	NUMBER	10	0	NOT NULL	The organization that this person belongs to
PS_PERSON_ID	NUMBER	10	0	NOT NULL	The person who holds this membership
PS_POSITION_TYPE_ID	VARCHAR2	4		NOT NULL	This is the position that a person is holding in this organization.
JOINED	DATE			NOT NULL	The date this person joined the organization with this position type.
LEFT	DATE				The date the member left the organization. If left date is null then they are still in the organization. They can not check out items for an organization if left date is NOT NULL.

Table: PS_POSITION_TYPE

Description: Holds the type of position a member can be in an organization i.e. fire chief, fire fighter, fire fighter1, and fire fighter2, etc.

NAME	TYPE	WIDTH	PREC	NULL	DESCRIPTION
ID	VARCHAR2	4		NOT NULL	The ID for position_type, an abbreviation of the position.
POSITION	VARCHAR2	50		NOT NULL	Holds the position name i.e. fire chief, fire fighter, fire fighter1, and fire fighter2, etc.

Table: SY_COUNTY

Description: Holds the counties, so less errors occur in the database with misspelled county name. A lookup table.

NAME	TYPE	WIDTH	PREC	NULL	DESCRIPTION
ID	NUMBER	2	0	NOT NULL	This is the ID for county, it should be the actual number that each county is assigned with the state.
NAME	VARCHAR2	30		NOT NULL	The name of the county.
UNAME	VARCHAR2	30		NOT NULL	Uppercase of county name (copied from NAME)