Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | contact_id | dbLong | YES | YES | Number assigned a unique value on each row of this table. | ||||
contact_date | dbDate | YES | NO | Date() | Date on which the contact occurred. | ||||
FK | contact_method_defn_id | dbLong | YES | NO | Value indicating the row of the contact_method table representing the contact method (for example, "phone call", "meeting", etc.) | ||||
FK | party_id | dbLong | YES | NO | Value identifying the party involved in the contact. | ||||
rec_mod_date | dbDate | NO | NO | Date() | The date on which the record was last modified | ||||
remk | mlr_remk | dbText (150) | NO | NO | Text providing ancillary information/comments about the contact. | ||||
FK | staff_mbr_id | dbLong | YES | NO | Value indicating the row of the staff_mbr table representing the staff member involved in the contact. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
contat subject (contat subject) | Non-identifying | contact | contact_subject | 1:N |
contact method (contact method) | Non-identifying | contact_method_defn | contact | 1:N |
staff mbr contact (staff mbr contact) | Non-identifying | staff_mbr | contact | 1:N |
party contact (party contact) | Non-identifying | party | contact | 1:N |
Each row of this table represents a single staff contact with a relation |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | contact_method_defn_id | dbLong | YES | YES | Number assigned a unique identifying value on each row of the table. | ||||
abbr | mlr_abbr | dbText (25) | YES | NO | Abbreviation used to identify the contact method in short displays. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full text definition of contact method |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
contact method (contact method) | Non-identifying | contact_method_defn | contact | 1:N |
Each row in this table represents a contact method code that can be used to indicate the method of a contact ("meeting", "phone call", "letter", "email", "voice mail message", etc.) |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | contact_subject_id | dbLong | YES | YES | Number assigned a unique value for each row of the table. | ||||
FK | contact_id | dbLong | YES | NO | Number indicating the row of the contact table representing the contact to which this row associates a contact subject. | ||||
FK | contact_subject_defn_id | dbLong | YES | NO | Value indicating which row of the contact_subject_defn table represents the subject associated with the contact table row indicated on this row. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
contact subject defn (contact subject defn) | Non-identifying | contact_subject_defn | contact_subject | 1:N |
contat subject (contat subject) | Non-identifying | contact | contact_subject | 1:N |
Each rwo in this table represents a subject that was covered during a staff contact with a relation |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | contact_subject_defn_id | dbLong | YES | YES | Number assigned to unique value for each row of table. | ||||
abbr | mlr_abbr | dbText (25) | YES | NO | Abbreviation used to identify contact subject within short displays. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full text definition of contact subject. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
contact subject defn (contact subject defn) | Non-identifying | contact_subject_defn | contact_subject | 1:N |
each row in this table represents a code that can be used to indicate a subject covered during a staff contact with a relation |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | county_defn_id | dbLong | YES | YES | Number assigned a unique identifying value for each row of table. | ||||
abbr | mlr_abbr | dbText (25) | YES | YES | Abbreviation used to indicate county in short concise displays. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full name of county. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
county loctn (county loctn) | Non-identifying | county_defn | loctn | 1:N |
county easement proj (county easement proj) | Non-identifying | county_defn | easement_proj_county | 1:N |
Each row in this table represents a code that may be used to indicate a county |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | donation_id | dbLong | YES | YES | Number assigned to uniquely identify this row within this table | ||||
amt_provided | dbCurrency | NO | NO | If the donation is a direct donation (cash, fund transfer, etc.), this column contains the dollar value of the donation. | |||||
donation_date | dbDate | YES | NO | Date() | The date on which the donation was received, or (for pledged future donations), the date on which the donation is promised. | ||||
FK | donation_type_defn_id | dbLong | YES | NO | Value representing the row of the donation_type_defn table representing the donation type assigned to this row. | ||||
donation_value | dbCurrency | NO | NO | For non-monetary donations, this column contains the estimated dollar value of the donation. | |||||
FK | fund_defn_id | dbLong | YES | NO | Value referring to the row of the fund_defn table that represents the fund into which this donation will be placed. | ||||
FK | income_category_defn_id | dbLong | NO | NO | Value indicating row of income_category_defn table representing the type of income | ||||
FK | pledge_id | dbLong | NO | NO | For promised donations associated with a pledge, a value referring to the row of the pledge table describing the pledge. | ||||
FK | final_donor_party_id | dbLong | YES | NO | Value the party to actually transfer funds to the organization. For proxy donations, this references the proxy donor. | ||||
FK | orig_donor_party_id | dbLong | NO | NO | In the case of a proxy donation, value identifying the originating donor. The proxy donor will be referenced by the value of the final_donor_party_id column. | ||||
pledge_amt | dbCurrency | NO | NO | If the donation record is a promised future donation offered as part of a pledge, this column indicates the dollar value of the part of the donation to be paid on the donation date. | |||||
pledge_yn | dbBoolean | YES | NO | 0 | Yes/no indicating whether the row represents a promised donation resulting from a pledge. | ||||
proxy_donation_yn | dbBoolean | NO | NO | 0 | Yes/no variable indicating whether the row represents a donation provided for the original donor through a final or proxy donor. | ||||
rec_mod_date | dbDate | NO | NO | Date() | The date on which the record was last modified | ||||
remk | mlr_remk | dbText (150) | NO | NO | Text providing ancillary information/comments about the donation. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
pledge donation (pledge donation) | Non-identifying | pledge | donation | 1:N |
donation type (donation type) | Non-identifying | donation_type_defn | donation | 1:N |
donation fund (donation fund) | Non-identifying | fund_defn | donation | 1:N |
final donor (final donor) | Non-identifying | party | donation | 1:N |
orig donor (orig donor) | Non-identifying | party | donation | 1:N |
donation income category (donation income category) | Non-identifying | income_category_defn | donation | 1:N |
Each row in this table represents a donation, a planned donation, a provision of an in-kind donation or service, etc. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | donation_type_defn_id | dbLong | YES | YES | Number assigned to uniquely identify this row within this table. | ||||
abbr | mlr_abbr | dbText (25) | YES | NO | Abbreviation used to identify this donation type in short displays | ||||
defn | mlr_def | dbText (60) | NO | NO | Full text definition of the donation type |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
donation type (donation type) | Non-identifying | donation_type_defn | donation | 1:N |
Each row in this table represents a code that can be used to categorize a donation ("fund transfer", "in-kind", "service", "materials", etc.) |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | easement_proj_id | dbLong | YES | YES | Number assigned a unique value for each row of the table. | ||||
abbr | mlr_abbr | dbText (25) | NO | NO | Abbreviation used to identify the project in pick lists, short displays in reports, etc. | ||||
area_ac | dbLong | NO | NO | Area involved in easement, in acres | |||||
completed_date | dbDate | NO | NO | ||||||
defn | mlr_def | dbText (60) | NO | NO | Full text name of the project. | ||||
easement_nbr | mlr_abbr | dbText (25) | NO | NO | Identifying number assigned in hard copy filing system | ||||
FK | ecosystem_defn_id | dbLong | NO | NO | Value identifying the row of the ecosystem defn table representing the ecosystem in which the project is located | ||||
prop_name | dbText (50) | NO | NO | Name of the property involved in the project. | |||||
rec_mod_date | dbDate | NO | NO | Date() | The date on which the record was last modified | ||||
FK | region_defn_id | dbLong | YES | NO | Value identifying the row of the region table representing the region in which this project is located. | ||||
remk | mlr_remk | dbText (150) | NO | NO | Text providing ancillary information/comments about the project. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
proj milestone (proj milestone) | Non-identifying | easement_proj | easement_proj_milestone | 1:N |
proj party (proj party) | Non-identifying | easement_proj | easement_proj_party | 1:N |
region proj (region proj) | Non-identifying | region_defn | easement_proj | 1:N |
proj staff (proj staff) | Non-identifying | easement_proj | easement_proj_staff | 1:N |
ecosystem proj (ecosystem proj) | Non-identifying | ecosystem_defn | easement_proj | 1:N |
easement proj county (easement proj county) | Non-identifying | easement_proj | easement_proj_county | 1:N |
Each row in this table represents an easement project undertaken by the organization. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | easement_proj_county_id | dbLong | NO | YES | Number assigned a unique value for each row of the table. | ||||
FK | easement_proj_id | dbLong | YES | NO | Value identifying the row of the easement project table representing the project associated with the county | ||||
FK | county_defn_id | dbLong | YES | NO | Value identifying the row of the county defn table representing the county associated with the easement project | ||||
area_ac | dbSingle | NO | NO | Area involved in easement and falling within the county, in acres |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
easement proj county (easement proj county) | Non-identifying | easement_proj | easement_proj_county | 1:N |
county easement proj (county easement proj) | Non-identifying | county_defn | easement_proj_county | 1:N |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | easement_proj_milestone_id | dbLong | YES | YES | Number assigned a unique value in each row of the table. | ||||
FK | easement_proj_id | dbLong | YES | NO | Value identifying the row of the easement_proj table representing the project. | ||||
milestone_date | dbDate | YES | NO | Date() | Date on which the milestone represented by this row was reached. | ||||
FK | milestone_type_id | dbLong | YES | NO | Value identifying the row of the easement milestone table representing the milestone documented here | ||||
rec_mod_date | dbDate | NO | NO | Date() | The date on which the record was last modified |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
proj milestone (proj milestone) | Non-identifying | easement_proj | easement_proj_milestone | 1:N |
milestone type (milestone type) | Non-identifying | milestone_type_defn | easement_proj_milestone | 1:N |
Each row represents a milestone achieved in an easement project. Milestones might include "project team formed", "funding obtained", "activity started", "project terminated", etc. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | easement_proj_party_id | dbLong | YES | YES | Number assigned a unique value for each row of the table | ||||
FK | easement_proj_id | dbLong | YES | NO | Value identifying the row of the easement_proj table representing the project. | ||||
FK | easement_proj_role_defn_id | dbLong | YES | NO | |||||
FK | party_id | dbLong | YES | NO | Value identifying the row representing the involved party |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
party proj (party proj) | Non-identifying | party | easement_proj_party | 1:N |
proj party (proj party) | Non-identifying | easement_proj | easement_proj_party | 1:N |
proj party role (proj party role) | Non-identifying | easement_proj_role_defn | easement_proj_party | 1:N |
Each row in this table represents a link between an easement project and a "relation". Typically, this would represent that the relation is a landowner of the involved property |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | ||
PK | easement_proj_role_defn_id | dbLong | YES | NO | |||||
abbr | mlr_abbr | dbText (25) | YES | YES | |||||
defn | mlr_def | dbText (60) | NO | NO |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
proj staff role (proj staff role) | Non-identifying | easement_proj_role_defn | easement_proj_staff | 1:N |
proj party role (proj party role) | Non-identifying | easement_proj_role_defn | easement_proj_party | 1:N |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | easement_proj_staff_id | dbLong | YES | YES | Number assigned a unique value for each row of the table. | ||||
FK | easement_proj_id | dbLong | YES | NO | Value identifying the row of the easement_proj table that represents the project referenced by this row. | ||||
FK | easement_proj_role_defn_id | dbLong | YES | NO | |||||
FK | staff_mbr_id | dbLong | YES | NO | Value indicating the row of the staff_mbr table identifying the staff member who's role in an easement project is defined by this row. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
staff proj (staff proj) | Non-identifying | staff_mbr | easement_proj_staff | 1:N |
proj staff (proj staff) | Non-identifying | easement_proj | easement_proj_staff | 1:N |
proj staff role (proj staff role) | Non-identifying | easement_proj_role_defn | easement_proj_staff | 1:N |
Each row represents role played by a staff member within an easement project. If a single staff member played multiple roles, a separate row representing each of those roles for that project would be present. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | ecosystem_defn_id | dbLong | YES | YES | Number assigned a unique identifying value for each row of table. | ||||
abbr | mlr_abbr | dbText (25) | YES | NO | Abbreviation used to describe region in short displays. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full text definition/name of region. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
ecosystem proj (ecosystem proj) | Non-identifying | ecosystem_defn | easement_proj | 1:N |
Each row of this table represents a code that may be used to indicate a geographic region. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | email_id | dbLong | YES | YES | Uniquely identifies each row in the table | ||||
FK | party_id | dbLong | YES | NO | Value indicating which row of the relation table contains the row this row is associated with. | ||||
address | dbText (60) | NO | NO | Full email address. | |||||
primary_yn | dbBoolean | NO | NO | Yes/no indicating whether the email address on this row is the primary email address to be used for contacting this relation. If several email addresses are entered for one relation, only one of those rows should have a "yes" entered for this column. | |||||
remk | mlr_remk | dbText (150) | NO | NO | Ancillary information/comments about the email address represented on this row. | ||||
rec_mod_date | dbDate | NO | NO | Date() | The date on which the record was last modified |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
party email (party email) | Non-identifying | party | 1:N |
Each row in this table represents an email address used by a relation. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | fund_defn_id | dbLong | YES | YES | Number assigned to uniquely identify this row within this table | ||||
abbr | mlr_abbr | dbText (25) | YES | NO | Abbreviation used to identify the fund in short displays. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full text definition of the fund | ||||
remk | mlr_remk | dbText (150) | NO | NO | Text providing ancillary information/comments about fund. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
donation fund (donation fund) | Non-identifying | fund_defn | donation | 1:N |
Each row in this table represents a code that may be used to indicate the fund to which a donations is placed |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | group_defn_id | dbLong | YES | YES | Uniquely identifies each row in the table | ||||
FK | group_type_defn_id | dbLong | YES | NO | Value referencing the row of the group_type_defn table that describes the group type applied to this group | ||||
abbr | mlr_abbr | dbText (25) | YES | NO | The abbreviation used to refer to this group in pull-down lists, concise report displays, etc. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full text definition of the group. | ||||
remk | mlr_remk | dbText (150) | NO | NO | Text providing ancillary information/comments about this group. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
group type (group type) | Non-identifying | group_type_defn | group_defn | 1:N |
group mbr (group mbr) | Non-identifying | group_defn | group_mbr_period | 1:N |
pub group defn (pub group defn) | Non-identifying | group_defn | pub | 1:N |
Each row in this table represents a group for which membership info is maintained. Examples: publication subscribers, those in a particular profession, those performing a certain volunteer activity, those providing large donations, etc. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | group_mbr_period_id | dbLong | YES | YES | Uniquely identifies each row in the table | ||||
FK | group_defn_id | dbLong | YES | NO | Value referencing the row of the group table defining the group to which the membership period applies. | ||||
FK | party_id | dbLong | YES | NO | Value identifying the party to who the membership is assigned. | ||||
date_start | dbDate | YES | NO | Date() | The start date of the group membership period | ||||
date_end | dbDate | YES | NO | DateAdd("yyyy",1,Date()) | The end date of the group membership period | ||||
remk | mlr_remk | dbText (150) | NO | NO | Text providing ancillary/comments about the group membership period represented in this record. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
group mbr (group mbr) | Non-identifying | group_defn | group_mbr_period | 1:N |
party group mbr (party group mbr) | Non-identifying | party | group_mbr_period | 1:N |
Index name | Columns | Unique | Descending | Clustered |
grp_mbr_start | date_start | NO | NO | NO |
grp_mbr_end | date_end | NO | NO | NO |
Each row in this table represents a continuous period of time during which a "party" is a member of a single group. No two rows are allowed to define overlapping membership periods for the same relation and group. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | group_type_defn_id | dbLong | YES | YES | Uniquely identifies each row in the table | ||||
abbr | mlr_abbr | dbText (25) | YES | NO | Abbreviation used to identify the group type within short displays. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full text definition of group type. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
group type (group type) | Non-identifying | group_type_defn | group_defn | 1:N |
Each row in this table represents a code that may be used to categorize a group. As examples a group might be categorized as "subscription", "donor type", "organization type", "volunteer", etc. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | ||
PK | income_category_defn_id | dbLong | YES | YES | |||||
abbr | mlr_abbr | dbText (25) | YES | NO | |||||
defn | mlr_def | dbText (60) | NO | NO |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
donation income category (donation income category) | Non-identifying | income_category_defn | donation | 1:N |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | loctn_id | dbLong | YES | YES | Uniquely identifies each row in the table | ||||
FK | county_defn_id | dbLong | NO | NO | Foreign key referencing a row of the county table that is defined to represent the county in which this location is located. | ||||
FK | loctn_type_defn_id | dbLong | YES | NO | Foreign key referencing the row of the loctn_type_defn table that describes the type of loction represented in the current row | ||||
FK | party_id | dbLong | YES | NO | Value identifying the party this location is used by | ||||
FK | region_defn_id | dbLong | NO | NO | Foreign key referencing the row in the region_defn table defined to represent the region in which this location is found. | ||||
country | dbText (50) | NO | NO | The name of the country the location falls in | |||||
address_1 | dbText (50) | NO | NO | First line of street address | |||||
address_2 | dbText (50) | NO | NO | Second line of street address | |||||
city | dbText (50) | NO | NO | The city where this location is found | |||||
organization_name | dbText (50) | NO | NO | If the location is a business location, the name of the business. This is included here as an elaboration of "address" information, rather than as a record formally representing the business. | |||||
postal_code | dbText (15) | NO | NO | Postal code for the location | |||||
primary_yn | dbBoolean | NO | NO | Yes/no indicating whether the location is to be used as the primary location for contacting the "party". | |||||
rec_mod_date | dbDate | NO | NO | Date() | The date on which the record was last modified | ||||
state | dbText (30) | NO | NO | State/Province where the location is found. For U.S., a two-letter USPS abbreviation. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
region loctn (region loctn) | Non-identifying | region_defn | loctn | 1:N |
loctn type (loctn type) | Non-identifying | loctn_type_defn | loctn | 1:N |
loctn phone (loctn phone) | Non-identifying | loctn | phone | 1:N |
party loctn (party loctn) | Non-identifying | party | loctn | 1:N |
county loctn (county loctn) | Non-identifying | county_defn | loctn | 1:N |
Index name | Columns | Unique | Descending | Clustered |
loctn_city | city | NO | NO | NO |
Each row in this table represents a location used by a relation. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | loctn_type_defn_id | dbLong | YES | YES | Uniquely identifies each row in the table | ||||
abbr | mlr_abbr | dbText (25) | YES | YES | Code used to identify loctn type in concise report displays, etc. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full text description of the meaning of the location type represented on a row | ||||
remk | dbText (150) | NO | NO | Text providing meaningful ancillary information or details about the location type represented by a row |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
loctn type (loctn type) | Non-identifying | loctn_type_defn | loctn | 1:N |
Each row in this table represents a code that may be used to categorize a location. Possible values might include "home", "work", "summer", "winter", etc. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | milestone_type_id | dbLong | YES | YES | Counting number assigned to uniquely identify each row of the table. | ||||
abbr | mlr_abbr | dbText (25) | YES | YES | Character abbreviation used to label milestone in concise displays such as reports, etc. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full text description of the milestone |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
milestone type (milestone type) | Non-identifying | milestone_type_defn | easement_proj_milestone | 1:N |
Each instance defines a code used to describe a code that can be assigned to a project to indicate a project milestone achieved |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | party_id | dbLong | YES | YES | Number assigned a unique identifying value for each row of the table. | ||||
add_date | dbDate | YES | NO | Date() | The date on which the row was created. | ||||
name | dbText (50) | NO | NO | The name of the party. For example: "Bradley Family", "George and William Smith", "Mr. and Mrs. Jonnie Johnson", "Buffalo Bros Guitars", "John Henry", etc. | |||||
FK | party_type_defn_id | dbLong | YES | NO | Indicates the "type" of the "party" (i.e. "business", "familty"...) | ||||
rec_mod_date | dbDate | NO | NO | The date on which the record was last modified | |||||
remk | mlr_remk | dbText (150) | NO | NO | Free-text comments about party | ||||
salutation | dbText (50) | NO | NO | Salutation used for the party in letters, etc. | |||||
FK | assigned_staff_mbr_id | dbLong | YES | NO | 25 | Value identifying the staff_mbr assigned as primary staff contact. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
party proj (party proj) | Non-identifying | party | easement_proj_party | 1:N |
staff party (staff party) | Non-identifying | staff_mbr | party | 1:N |
party category (party category) | Non-identifying | party | party_category | 1:N |
party phone (party phone) | Non-identifying | party | phone | 1:N |
party email (party email) | Non-identifying | party | 1:N | |
party group mbr (party group mbr) | Non-identifying | party | group_mbr_period | 1:N |
party loctn (party loctn) | Non-identifying | party | loctn | 1:N |
final donor (final donor) | Non-identifying | party | donation | 1:N |
party contact (party contact) | Non-identifying | party | contact | 1:N |
party mbr (party mbr) | Non-identifying | party | party_mbr | 1:N |
orig donor (orig donor) | Non-identifying | party | donation | 1:N |
party type (party type) | Non-identifying | party_type_defn | party | 1:N |
party pledge (party pledge) | Non-identifying | party | pledge | 1:N |
Index name | Columns | Unique | Descending | Clustered |
party_name | name | NO | NO | NO |
Each row in this table represents a "relationship" that is significant to the organization. A relationship may be targeted towards a person, a family, a business, or an organization. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | party_category_id | dbLong | YES | YES | Uniquely identifies each row in the table | ||||
FK | party_category_defn_id | dbLong | YES | NO | Value identifying the category to be applied. | ||||
FK | party_id | dbLong | YES | NO | Value identifying the party to which the category is applied. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
party category defn (party category defn) | Non-identifying | party_category_defn | party_category | 1:N |
party category (party category) | Non-identifying | party | party_category | 1:N |
Each row in this table represents the application of a categorization code to a "relation". An arbitrary number of categorizations may be applied to a single relation. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | party_category_defn_id | dbLong | YES | YES | Uniquely identifies each row in the table | ||||
abbr | mlr_abbr | dbText (25) | YES | NO | Abbreviation used to identify relation class within short displays. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full text definition of category | ||||
remk | mlr_remk | dbText (150) | NO | NO | Text providing ancillary information/comments about the class. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
party category defn (party category defn) | Non-identifying | party_category_defn | party_category | 1:N |
Each row in this table represents a code that may be used to categorize a relation. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | party_mbr_id | dbLong | YES | YES | Unique number assigned to each row of this table. | ||||
FK | person_id | dbLong | YES | NO | Value identifying the person. | ||||
FK | party_id | dbLong | YES | NO | Value identifying the party. | ||||
FK | party_mbr_role_defn_id | dbLong | NO | NO | Indicates the role played in the "party" by this person (i.e. "mgr", "employee", etc.) | ||||
primary_yn | dbBoolean | NO | NO | Yes/no indicating whether the person plays the role of primary contact for this party (only one "primary" per party) | |||||
rec_mod_date | dbDate | NO | NO | Date() | The date on which the record was last modified |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
party mbr (party mbr) | Non-identifying | party | party_mbr | 1:N |
party mbr person (party mbr person) | Non-identifying | person | party_mbr | 1:N |
party mbr role (party mbr role) | Non-identifying | party_mbr_role_defn | party_mbr | 1:N |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | ||
PK | party_mbr_role_defn_id | dbLong | YES | YES | |||||
abbr | mlr_abbr | dbText (25) | YES | YES | |||||
defn | mlr_def | dbText (60) | NO | NO |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
party mbr role (party mbr role) | Non-identifying | party_mbr_role_defn | party_mbr | 1:N |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | ||
PK | party_type_defn_id | dbLong | YES | NO | |||||
abbr | mlr_abbr | dbText (25) | YES | YES | |||||
defn | mlr_def | dbText (60) | NO | NO |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
party type (party type) | Non-identifying | party_type_defn | party | 1:N |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | person_id | dbLong | YES | YES | Unique number assigned to every row in this table | ||||
birthdate | dbDate | NO | NO | ||||||
name_first | dbText (50) | NO | NO | First name | |||||
name_last | dbText (50) | NO | NO | Last name | |||||
name_middle | dbText (50) | NO | NO | Middle name or initial | |||||
name_prefix | dbText (50) | NO | NO | Name prefix ("Ms.","Dr.", etc.) | |||||
name_suffix | dbText (50) | NO | NO | Name suffix ("III", "PhD", etc.) | |||||
rec_mod_date | dbDate | NO | NO | Date() | The date on which the record was last modified | ||||
remk | mlr_remk | dbText (150) | NO | NO | Text providing ancillary information/comments about this person. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
related person (related person) | Non-identifying | person | related_person | 1:N |
person event (person event) | Non-identifying | person | person_event | 1:N |
party mbr person (party mbr person) | Non-identifying | person | party_mbr | 1:N |
starting person (starting person) | Non-identifying | person | related_person | 1:N |
Index name | Columns | Unique | Descending | Clustered |
person_name_last | name_last | NO | NO | NO |
person_name_first | name_first | NO | NO | NO |
Each row in this table represents a single person |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | person_event_id | dbLong | YES | YES | Uniquely identifies each row in the table | ||||
FK | person_event_type_defn_id | dbLong | YES | NO | Value referencing the row of the person_event_type_defn table that properly categorizes the event | ||||
FK | person_id | dbLong | YES | NO | Value referencing the row of the person table describing the person to who the event occurred. | ||||
event_date | dbDate | YES | NO | Date() | The date on which the event occurred. | ||||
rec_mod_date | dbDate | NO | NO | Date() | The date on which the record was last modified | ||||
remk | mlr_remk | dbText (150) | NO | NO | Text providing ancillary information/comments about the event. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
person event (person event) | Non-identifying | person | person_event | 1:N |
person event type (person event type) | Non-identifying | person_event_type_defn | person_event | 1:N |
Each row in this table represents a significant event that has happened to a person. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | person_event_type_defn_id | dbLong | YES | YES | Uniquely identifies each row in the table | ||||
abbr | mlr_abbr | dbText (25) | YES | NO | Abbreviation used to identify personal event type in short displays. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full text definition of the personal event type. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
person event type (person event type) | Non-identifying | person_event_type_defn | person_event | 1:N |
Each row in this table represents a code that can be used to categorize a personal event into one of a mutually exclusive set of categories. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | phone_id | dbLong | YES | YES | Uniquely identifies each row in the table | ||||
FK | loctn_id | dbLong | NO | NO | Value referencing the row of the location table to which a phone is related. Mobile phones may be related to a person rather than to a location. | ||||
FK | party_id | dbLong | NO | NO | Value identifying the party using this phone number. If the phone is a mobile phone, it should probably be referenced directly to a party, If it is not a mobile phone, it should probably not be referenced to a "party", but instead to a location. | ||||
FK | phone_type_defn_id | dbLong | YES | NO | Value referncing the row of the phone_type_defn table defining the type of phone number stored in this row. | ||||
area_code | dbInteger | YES | NO | Area code portion of the phone number | |||||
country_code | dbInteger | NO | NO | Country code portion of the phone number | |||||
ext | dbInteger | NO | NO | Extension portion of the phone number | |||||
nbr | dbText (15) | NO | NO | Main portion of the phone number. Area code, country code and extension should be stored in other fields designated for that purpose. | |||||
primary_yn | dbBoolean | YES | NO | Yes/no indicating whether this phone is the primary one that should be used for this relation. If several phones are associated with a relation, only one should have a "yes" entered for this column. | |||||
rec_mod_date | dbDate | NO | NO | Date() | The date on which the record was last modified | ||||
remk | mlr_remk | dbText (150) | NO | NO |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
phone type (phone type) | Non-identifying | phone_type_defn | phone | 1:N |
loctn phone (loctn phone) | Non-identifying | loctn | phone | 1:N |
party phone (party phone) | Non-identifying | party | phone | 1:N |
Each row in this table represents a phone that is used by a relation or a location |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | phone_type_defn_id | dbLong | YES | YES | Number assigned a unique identifying value for each row of table. | ||||
abbr | mlr_abbr | dbText (25) | YES | YES | Abbreviation used to identify phone type in short displays. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full text definition of phone type. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
phone type (phone type) | Non-identifying | phone_type_defn | phone | 1:N |
Each row in this table represents a code that may be used to categorize a phone. Possible values might include "mobile", "fax", etc. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | pledge_id | dbLong | YES | YES | Number assigned to uniquely identify this row among all rows in this table. | ||||
FK | party_id | dbLong | YES | NO | Value referncing row of party table describing the party making the pledge | ||||
pledge_date | dbDate | YES | NO | Date on which the pledge was made | |||||
rec_mod_date | dbDate | NO | NO | Date() | The date on which the record was last modified | ||||
remk | mlr_remk | dbText (150) | NO | NO | Text providing description, ancillary information and/or comments about the pledge. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
pledge donation (pledge donation) | Non-identifying | pledge | donation | 1:N |
party pledge (party pledge) | Non-identifying | party | pledge | 1:N |
Each row in this table represents a single pledge promise made by a relation. This promise might be a promise to provide several future donations at planned times. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | pub_id | dbLong | YES | NO | Counting number assigned to uniquely identify each row in table | ||||
abbr | mlr_abbr | dbText (25) | YES | YES | A short abbreviation used to represent a row of the table in concise reports, displays, etc. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full-text description of the publication represented by the row. | ||||
remk | mlr_remk | dbText (150) | NO | NO | Miscellaneous comments about the publication represented by this row of the table. | ||||
FK | group_defn_id | dbLong | NO | NO |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
pub issue (pub issue) | Non-identifying | pub | pub_issue | 1:N |
pub group defn (pub group defn) | Non-identifying | group_defn | pub | 1:N |
Each row represents a publication produced by the organization |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | pub_issue_id | dbLong | YES | NO | Counting number assigned a unique identifying value for each row of the table | ||||
FK | pub_id | dbLong | YES | NO | Number identifying a single associated row of the "pub" table that characterizes the publication represented by this row of the table. | ||||
pub_date | dbDate | NO | NO | Date() | The publication date of the volume/iissue | ||||
remk | mlr_remk | dbText (150) | NO | NO | Miscelaneous comments about the volume represented by this row of the table. | ||||
title | mlr_def | dbText (60) | NO | NO | Title of the publication issue/volume represented by this row of the table |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
pub issue (pub issue) | Non-identifying | pub | pub_issue | 1:N |
Each row of this table represents a specific volume or issue of a publication produced by the organization. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | region_defn_id | dbLong | YES | YES | Number assigned a unique identifying value for each row of table. | ||||
abbr | mlr_abbr | dbText (25) | YES | NO | Abbreviation used to describe eocystem in short displays. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full text definition/name of ecosystem. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
region loctn (region loctn) | Non-identifying | region_defn | loctn | 1:N |
region proj (region proj) | Non-identifying | region_defn | easement_proj | 1:N |
Each row of this table represents a code that may be used to indicate a ecosystem |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | related_person_id | dbLong | YES | YES | Uniquely identifies each row in the table | ||||
FK | person_id | dbLong | YES | NO | Value referencing the row of the person table to whom the related person applies | ||||
FK | related_person_person_id | dbLong | YES | NO | Value referencing the row of the person table describing the related person. | ||||
FK | related_person_role_defn_id | dbLong | YES | NO | Value referencing the row of the related_person_role_defn table that defines the relationship of the end person to the start person. | ||||
remk | mlr_remk | dbText (150) | NO | NO | Text providing ancillary information/comments about the relationship represented on this row. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
related person role (related person role) | Non-identifying | related_person_role_defn | related_person | 1:N |
related person (related person) | Non-identifying | person | related_person | 1:N |
starting person (starting person) | Non-identifying | person | related_person | 1:N |
Each row in this table represents the relationship of an "start person" to a "end person". If the start person is the mother of the end person, who is her son, then the relationship type will be "son". |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | related_person_role_defn_id | dbLong | YES | YES | Uniquely identifies each row in the table | ||||
abbr | mlr_abbr | dbText (25) | YES | NO | Abbreviation used to identify related person role in short displays. | ||||
defn | mlr_def | dbText (60) | NO | NO | Full text definition of role |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
related person role (related person role) | Non-identifying | related_person_role_defn | related_person | 1:N |
Each row in this table represents a code that can be used to indicate the role a related person plays relative to another person |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | rpt_row_id | dbLong | YES | YES | Integer value uniquely identifying each row in table | ||||
rpt_key | dbText (6) | YES | NO | Synthesized value that should uniquely identify each report execution for which rows are stored in this table | |||||
row_id_val | dbLong | YES | NO | The primary key value of a row that should be summarized in the report |
Index name | Columns | Unique | Descending | Clustered |
rpt_row_id_val_ndx | row_id_val | NO | NO | NO |
Each row of this table holds identifying information for a row selected for inclusion in a report. The contents are managed dynamically to exist only for the duration of report generation. |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |
Key | Column name | Domain | Data type | Not null | Unique | Check | Default | Description | |
PK | staff_mbr_id | dbLong | YES | YES | Number assigned a unique value for each row in table. | ||||
abbr | mlr_abbr | dbText (25) | YES | NO | Abbreviation used to identify the staff member in concise displays like pick lists, reports, etc. | ||||
current_staff_yn | dbBoolean | NO | NO | Yes/no indicating whether the individual is a current staff member. | |||||
name_first | dbText (50) | NO | NO | First name of staff member | |||||
name_last | dbText (50) | NO | NO | Last name of staff member. |
Constraint (relationship) name | Relationship type | Parent table | Child table | Card. |
staff party (staff party) | Non-identifying | staff_mbr | party | 1:N |
staff mbr contact (staff mbr contact) | Non-identifying | staff_mbr | contact | 1:N |
staff proj (staff proj) | Non-identifying | staff_mbr | easement_proj_staff | 1:N |
Each row represents a current or former staff member of the organization |
Name | Value |
Linked table | No |
Connect | |
Source Table Name | |
Validation rule | |
Validation text |