Sys_Default_Id

Int

Primary Key

System Generated Sequential Key

Sys_Ind_Mem_Price

Float

Not NULL

24.00

Approved Annual Fee for Individual Memberships

Sys_Inst_Mem_Price

Float

Not NULL

48.00

Approved Annual Fee for Institutional Memberships

Sys_Reg_User_Price

Float

Not NULL

0.00

Approved Annual Fee for Registered Non-member users

Sys_PPP_Price

Float

Not NULL

125.00

Approved Annual Fee for Graphical PPP Access

Sys_DB_Price

Float

Not NULL

Approved Annual Fee for Database Hosting

Sys_VirtualDomain_Price

Float

Not NULL

Approved Annual Fee for Virtual Domain Hosting

Sys_TimeQuota1

Float

Not NULL

?

Maximum allowed time per single session for users

Sys_TimeQuota2

Float

Not NULL

?

Maximum allowed sessions per day for users

Sys_SSL_Price

Float

Not NULL

Approved Additional Annual Fee for SSL encrypted Virtual Domain Hosting

Sys_ListServer_Price

Float

Not NULL

100.00

Approved Annual Fee for Listserver Hosting

Sys_Next_UserId

String (5)

Not NULL

Next available UserName for Registered User accounts following the Csuite naming convention of aannn1

Sys_ExtraDiskQuota_Price

Float

Not NULL

?

Approved Annual Pricing per Mb of extra Disk Quota above the approved included amounts

Sys_Incl_Quota1

Int

Not NULL

Approved Amount of basic disk space in filesystem1

Sys_Incl_Quota2

Int

Not NULL

Approved Amount of basic disk space in filesystem2

Sys_Incl_Quota3

Int

Not NULL

Approved Amount of basic disk space in filesystem3

Sys_Quota1_Mnt

 String (8)

Not NULL

Mount point on the system for quota1

Sys_Quota2_Mnt

 String (8)

Not NULL

Mount point on the system for quota2

Sys_Quota3_Mnt

 String (8)

Not NULL

Mount point on the system for quota3

Sys_Last_Annual_Cycle

Time­stamp

The timestamp of the last generated invoice cycle. This is used to frequent duplicate invoice cycles within the same fiscal year.

User_Number

Int

Primary Key

System Generated Sequential Number

User_LastName

String (30)

Not NULL

User’s Last Name

User_FirstName

String (20)

Not NULL

User’s First Name

User_Initial

String (60)

User’s Initial

User_Salutation

String (60)

One of “Mr.”, “Mrs.”, “Ms.”, “Dr.”, “Rev.”

User’s Title of Address

User_Organization

 String (50)

Name of User’s Organization if user is a group or collective entity

User_Title

String (30)

Position or title of person within the organization

User_StreetAddress1

String (30)

Not NULL

User’s first line of mailing address

User_StreetAddress2

String (30)

User_City

 String (30)

Not NULL

User_Province

String (30)

Not NULL

User_Country

 String (30)

Not NULL

User_PostalCode

 String (20)

Not NULL

User_HomePhone

String (20)

User_WorkPhone

 String (20)

User_Age

Int

Really only required for users under the age of majority (so we get their parent’s signature on any application)

User_IsActive

Int

Not NULL

0

0 for not active, 1 for active, 2 for declined, 3 for expired, 4 for purged

User_Since

 Time­stamp

Not NULL

Date the user applied for an account

User_IsMember

Int

Not NULL

0

0 for non-member, 1 for member

User_Status_Msg

 String (60)

Any Volunteer entered text string to describe issues concerning a user

User_UserName

String (16)

Not NULL

The system generated username for non- members, or the personalized username for members. This is only used during the account application process and NOT changed for users who upgrade to membership later.

User_InitialPassword

String (16)

Not NULL

Must be checked against the cracklib

The User requested initial password in MD5 hash encryption

User_EnteredBy

 String (16)

Not NULL

The name of the GPFN Volunteer adding the user to the system

User_Number

Int

Foreign Key

References User

If an entry exists in this table with User_Number, then we know that this User is also a member

Affinity_Number

String (10)

Foreign Key

References Affinity

If an entry exists in this table, then the member has a special pricing relationship with GPFN (to be implemented in Phase 4)

Member_Type

Int

Not NULL

1 or 2

1 for Individual Member, 2 for Institutional Member

Member_Since

Time­stamp

Not NULL

Date on which user applied to become a member

Member_EnteredBy

 String (16)

Not NULL

The name of the GPFN Volunteer adding the member to the system

Affinity_Number

String (10)

Primary Key

Affinity_Name

String (50)

Not NULL

Name of group or organization entering into an affinity relationship with GPFN

Affinity_Contact_ LastName

String (30)

Not NULL

Name of contact person with the group or organization

Affinity_Contact_ FirstName

String (20)

Not NULL

Affinity_StreetAddress1

String (30)

Not NULL

Affinity_StreetAddress2

String (30)

Affinity_City

 String (30)

Not NULL

Affinity_Province

String (30)

Not NULL

Affinity_Country

 String (30)

Not NULL

Affinity_PostalCode

String (30)

Not NULL

Affinity_Phone

String (20)

Not NULL

Affinity_Ind_Mem_Price

Float

Not NULL

0.00

Agreed price of individual memberships under this affinity agreement

Affinity_Ind_Mem_Max_ Count

Int

Not NULL

0

Agreed maximum number of individual memberships to be sold under this affinity agreement

Affinity_PPP_Price

Float

Not NULL

0.00

Agreed price of PPP connections under this affinity agreement

Affinity_PPP_Max_Count

Int

Not NULL

0

Agreed maximum number of PPP connections to be sold under this affinity agreement

Affinity_Quota2_Amt

Int

Not NULL

0

Agreed amount of disk space in Quota space 2

Affinity_Quota2_Amt

Int

Not NULL

0

Agreed amount of disk space in Quota space 2

Affinity_Quota_Max_ Count

Int

Not NULL

0

Agreed maximum number of members who benefit from the increased quota limits under this affinity

Affinity_Annual_ Agreement_Fee

Float

Not NULL

0.00

Annual Fee to be invoiced to the Affinity_Name for the terms of the affinity agreement

Affinity_EnteredBy

String (16)

Not NULL

The name of the GPFN Volunteer adding or editing the affinity to the system

User_Number

Int

Foreign Key

References User

Service_Text_Access

Int

Not NULL

1

1 if user has access to text-only dial-up

Service_PPP_Access

Int

Not NULL

0

1 if user has access to PPP graphical dial-up

Service_WebSite

Int

Not NULL

0

1 if user has access to web space within GPFN’s public web space (not including the user’s public_html directory)

Service_Website_Path

String

The relative path from the Webserver’s document root to the directory for this members’ web space within the gpfn.ca domain.

Service_DBAccess

Int

Not NULL

0

Number of databases the user has access to on the database server

Service_Quota1

Int

Not NULL

0

Number of extra Mb of disk space the user has subscribed to on the filesystem referenced by Quota1

Service_Quota2

Int

Not NULL

0

Number of extra Mb of disk space the user has subscribed to on the filesystem referenced by Quota2

Service_Quota3

Int

Not NULL

0

Number of extra Mb of disk space the user has subscribed to on the filesystem referenced by Quota3

Service_VirtualDomain

Int

Not NULL

0

Number of virtual domains the user has on the system

Service_ VirtualDomainName

String

Registered Name of the Virtual Domain

Service_TimeQuota1

Int

NotNULL

0

Amount of extra time the user has paid to be added to the value included in Sys_TimeQuota1

Service_TimeQuota2

Int

NotNULL

0

Amount of extra time the user has paid to be added to the value included in Sys_TimeQuota2

Service_EmailAliases_ Max_Count

Int

Not NULL

0

The maximum number of e-mail aliases belonging to this user

Service_SSL

Int

Not NULL

0

The number of SSL enabled Virtual Domain web hosts for this user on the system

Service_Listserver

Int

Not NULL

0

The number of Listservers for this user on the system

Services_EnteredBy

 String (16)

Not NULL

The name of the GPFN Volunteer adding or editing this service list on the system

Alias_Id

Int

Primary Key

System Generated Incremental Number

User_Number

Int

Foreign Key

References User

Alias_GPFNAddress

String (30)

Not NULL

The GPFN address to which mail is received

Alias_ExtAddress

String (50)

Not NULL

The non-GPFN address to which mail is redirected

Invoice_Number

Int

Primary Key

System Generated Incremental Number

User_Number

Int

Foreign Key

References User

Invoice_Date

Time­stamp

Not NULL

Date on which the invoice was issued

Invoice_Medium

 String (20)

Not NULL

One of “e-mail”, “paper”, “fax” or some other such descriptor of the method in which the invoice was delivered

Invoice_Amount

Float

Not NULL

The total value of the invoice

Invoice_EnteredBy

 String (16)

Not NULL

The name of the GPFN Volunteer adding this invoice to the system

Item_Number

Int

Primary Key

System Generated Incremental Number

Invoice_Number

Int

Foreign Key

References Invoice

User_Number

Int

Foreign Key

References User

Item_Description

String (60)

Not NULL

Description of the line item for the invoice

Item_Amount

Float

Not NULL

0.00

Amount of the line item for the invoice

Receipt_Number

Int

Primary Key

System Generated Incremental Number

Invoice_Number

Int

Foreign Key

References Invoice

User_Number

Int

Foreign Key

References User

Receipt_Date

Time­stamp

Not NULL

Date that the amount was received and processed by the volunteer

Receipt_Amount

Float

Not NULL

0.00

Amount received from the user

Receipt_Payment_Type

String (30)

Not NULL

One of “Cash”, “Cheque”, “Credit Card” or some other description

Receipt_Entered_By

 String (16)

Not NULL

The name of the GPFN Volunteer adding this receipt to the system

Upgrade_Id

Int

Primary Key

System Generated Incremental Number

Invoice_Number

Int

Foreign Key

References Invoice

User_Number

Int

Foreign_Key

References User

Upgrade_User-Ind

Int

Not NULL

0

1 if the upgrade turns a registered user into an individual member

Upgrade_User-Inst

Int

Not NULL

0

1 if the upgrade turns a registered user into an institutional member

Upgrade_Ind-Inst

Int

Not NULL

0

1 if the upgrade turns an individual into an institutional member

Upgrade_Username

String (16)

The selected username for a user upgrading to a membership from a registered user account.

Upgrade_toPPP

Int

Not NULL

0

1 if the upgrade enables PPP 

Upgrade_toDB

Int

Not NULL

0

The number of Databases to be added to the User’s account

Upgrade_DBName

String

The requested name for a database on the database server.

Upgrade_Quota1

Int

Not NULL

0

Number of Mb to be added to the user’s disk quota of the filesystem referenced by Quota1

Upgrade_Quota2

Int

Not NULL

0

Number of Mb to be added to the user’s disk quota of the filesystem referenced by Quota2

Upgrade_Quota3

Int

Not NULL

0

Number of Mb to be added to the user’s disk quota of the filesystem referenced by Quota3

Upgrade_toVirtualDomain

Int

Not NULL

0

Number of Virtual Domains to add to the user

Upgrade_ VirtualDomainName

String

The requested domain for an application for a virtual domain

Upgrade_TimeQuota1

Int

Not NULL

0

Number of additional minutes to add to Sys_TimeQuota1

Upgrade_TimeQuota2

Int

Not NULL

0

Number of additional sessions to be added to Sys_TimeQuota2 for this user

Upgrade_toSSL

Int

Not NULL

0

Number of SSL enabled web hosts to be added to this client

Upgrade_toAliases

Int

Not NULL

0

Number of additional email aliases to be added to this user’s services

Upgrade_AliasExtAddress

String

e-mail address of the external service to which e-mail to the alias will be redirected

Upgrade_ AliasGPFNAddress

String

e-mail address of the GPFN address to which mail will be receive and then redirected

Upgrade_toListserver

Int

Not NULL

0

Number of additional Listservers to be added to this user’s services

Upgrade_ListserverName

String

Name of mailing list for an upgrade to Listserver.

Upgrade_ApprovedBy

 String (16)

Not NULL

The name of the GPFN Volunteer approving this upgrade on the system