The Data Model is intended to be a comprehensive model to support both the immediate needs of the UMS and possible future features and enhancements. Not all fields will be used in the currently scoped implementation.
Data Type | Constraints | Default Value | Description | |
---|---|---|---|---|
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 |
Timestamp |
|
|
The timestamp of the last generated invoice cycle. This is used to frequent duplicate invoice cycles within the same fiscal year. |
Field Name | Data Type | Constraints | Default Value | Description |
---|---|---|---|---|
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 |
Timestamp |
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 |
Field Name | Data Type | Constraint | Default Value | Description |
---|---|---|---|---|
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 |
Timestamp |
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 |
Field Name | Data Type | Constraints | Default Value | Description |
---|---|---|---|---|
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_Quota1_Amt | Int | Not NULL | 0 |
Agreed amount of disk space in Quota space 1 |
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 |
Field Name | Data Type | Constraints | Default Value | Description |
---|---|---|---|---|
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 |
Field Name | Data Type | Constraints | Default Value | Description |
---|---|---|---|---|
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 |
Field Name | Data Type | Constraints | Default Value | Description |
---|---|---|---|---|
Invoice_Number |
Int |
Primary Key |
|
System Generated Incremental Number |
User_Number |
Int |
Foreign Key |
References User |
|
Invoice_Date |
Timestamp |
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 |
Field Name | Data Type | Constraints | Default Value | Description |
---|---|---|---|---|
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 |
Field Name | Data Type | Constraints | Default Value | Description |
---|---|---|---|---|
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 |
Timestamp |
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 |
Field Name | Data Type | Constraint | Default Value | Description |
---|---|---|---|---|
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 |
Links
[1] https://niedermayer.ca/user/login?destination=node/87%23comment-form
[2] https://niedermayer.ca/user/login?destination=node/88%23comment-form
[3] https://niedermayer.ca/user/login?destination=node/89%23comment-form
[4] https://niedermayer.ca/user/login?destination=node/90%23comment-form
[5] https://niedermayer.ca/user/login?destination=node/91%23comment-form
[6] https://niedermayer.ca/user/login?destination=node/92%23comment-form
[7] https://niedermayer.ca/user/login?destination=node/93%23comment-form
[8] https://niedermayer.ca/user/login?destination=node/94%23comment-form
[9] https://niedermayer.ca/user/login?destination=node/95%23comment-form
[10] https://niedermayer.ca/user/login?destination=node/96%23comment-form
[11] https://niedermayer.ca/user/login?destination=node/97%23comment-form