member_canonical
The canonical member table. Other tables have foreign keys to this table.
Primary Key:Checks:member_canonical_check-((name_first <> ''::text) AND (name_last <> ''::text))member_canonical_name_first_check-(name_first = upper(name_first))member_canonical_name_last_check-(name_last = upper(name_last))member_canonical_name_middle_check-(name_middle = upper(name_middle))member_canonical_name_prefix_check-(name_prefix = upper(name_prefix))member_canonical_name_suffix_check-(name_suffix = upper(name_suffix))member_canonical_ssn_check-(ssn = claims.clean_ssn(ssn))
canonical_member_seen_id=>member_seen.member_seen_id
member_canonical_canonical_member_seen_id_idx-canonical_member_seen_idmember_canonical_idx(Unique) -ssn,name_prefix,name_last,name_first,name_middle,name_suffix,date_of_birth,gender_or_sexmember_canonical_ssn_idx-ssn
| Column | Data Type | Nullable | Indexed | Description |
|---|---|---|---|---|
member_canonical_id | bigint | No | Yes | - |
ssn | text | No | Yes | Social Security Number (From NM109 code 34 or REF SY) |
ssn_verified | boolean | No | No | Whether the SSN has been verified to be correct. No verified SSNs may be a duplicate of themselves but an unverified member could still share the same SSN. |
canonical_member_seen_id | bigint | No | Yes | Snowflake ID of the canonical member. |
name_prefix | text | No | Yes | Prefix to the name (From NM106) |
name_last | text | No | Yes | Last name or organization name (From NM103) |
name_first | text | No | Yes | First / given name (From NM104) |
name_middle | text | No | Yes | Middle name (From NM105) |
name_suffix | text | No | Yes | Suffix to the name (From NM107) |
date_of_birth | date | No | Yes | Date of birth (From DMG02) |
gender_or_sex | gender_or_sex_type | No | Yes | May be either the preferred gender or the sex assigned at birth of the individual (From DMG03) |