User data model
The following model describes the schema objects related to the user management system in OpenIAM.
Note: You should not develop processes that write directly to the schema. The schema will evolve with each release and you should use the OpenIAM API to shield your processes from these schema changes.
Table: USERS
The USERS table is the primary table to store basic user attributes. This table has relationships with other user related tables. The relationships are described below.
- One to Many relationship with:
- Login - Accounts that are associated with this user.
- User_attributes - Custom attributes that a user object may have
- Auth_state - A user can have more than 1 active session based on authentication policy
- User_Note
- User_identity_Ans - This table refers to teh
| Column Name | Data Type | Description | Note |
|---|---|---|---|
USER_ID | VARCHAR(32) | Unique identifier for the user. | Primary Key |
FIRST_NAME | VARCHAR(100) | First name of the user. | |
LAST_NAME | VARCHAR(100) | Last name of the user. | |
MIDDLE_INIT | VARCHAR(50) | Middle initial of the user. | |
TYPE_ID | VARCHAR(32) | Foreign key referencing METADATA_TYPE.TYPE_ID for user type. | |
CLASSIFICATION | VARCHAR(100) | Classification of the user (e.g., Employee, Contractor). | |
TITLE | VARCHAR(100) | Job title of the user. | |
MAIL_CODE | VARCHAR(100) | Internal mail code for the user. | |
COST_CENTER | VARCHAR(100) | Cost center for financial tracking. | |
STATUS | VARCHAR(40) | Current status of the user (e.g., Active, Inactive). | |
SECONDARY_STATUS | VARCHAR(40) | Additional status information. | |
BIRTHDATE | DATETIME | Date of birth of the user. | |
SEX | CHAR(1) | Gender of the user (M or F). | |
CREATE_DATE | DATETIME | Timestamp of user creation. | |
CREATED_BY | VARCHAR(32) | User ID of the creator of this record. | |
LAST_UPDATE | DATETIME | Last modification timestamp. | |
LAST_UPDATED_BY | VARCHAR(32) | User ID of the last person who updated the record. | |
PREFIX | VARCHAR(4) | Name prefix (e.g., Mr., Dr.). | |
SUFFIX | VARCHAR(20) | Name suffix (e.g., Jr., Sr.). | |
USER_TYPE_IND | VARCHAR(20) | Indicator for user type. | |
EMPLOYEE_ID | VARCHAR(100) | Employee number or HR system reference. | |
EMPLOYEE_TYPE | VARCHAR(32) | Foreign key referencing METADATA_TYPE.TYPE_ID for employment type. | |
LOCATION_CD | VARCHAR(50) | Location code assigned to the user. | |
LOCATION_NAME | VARCHAR(100) | Name of the assigned location. | |
COMPANY_OWNER_ID | VARCHAR(32) | ID of the company owning this user account. | |
JOB_CODE | VARCHAR(32) | Foreign key referencing METADATA_TYPE.TYPE_ID for job classification. | |
ALTERNATE_ID | VARCHAR(32) | Alternate user identifier. | Used for Out-of-Office |
START_DATE | DATE | Date when the user started employment. | |
LAST_DATE | DATE | Date when the user ended employment. | |
MAIDEN_NAME | VARCHAR(40) | User's maiden name. | |
NICKNAME | VARCHAR(100) | User's preferred nickname. | |
PASSWORD_THEME | VARCHAR(20) | Password policy theme assigned to the user. | Deprecated |
USER_OWNER_ID | VARCHAR(32) | ID of the user owner. | Deprecated |
DATE_PASSWORD_CHANGED | DATETIME | Timestamp of the last password change. | |
DATE_CHALLENGE_RESP_CHANGED | DATETIME | Timestamp of the last challenge-response question update. | |
SYSTEM_FLAG | VARCHAR(1) | System flag indicator. | |
DATE_IT_POLICY_APPROVED | TIMESTAMP | Timestamp when the IT policy was last accepted. | |
CLAIM_DATE | DATE | Date when the user account was claimed. | Deprecated |
LASTNAME_PREFIX | VARCHAR(10) | Prefix used for sorting last names. | |
SUB_TYPE_ID | VARCHAR(32) | Foreign key referencing METADATA_TYPE.TYPE_ID for sub-type classification. | |
PARTNER_NAME | VARCHAR(60) | Name of a business or external partner associated with the user. | |
PREFIX_PARTNER_NAME | VARCHAR(10) | Prefix for the partner's name. | |
ALTERNATE_START_DATE | DATETIME | Alternate start date for employment. | |
ALTERNATE_END_DATE | DATETIME | Alternate end date for employment. | |
SECRET_WORD | VARCHAR(255) | User's secret word for authentication. | |
SECRET_FAIL_COUNT | INT(3) | Counter for failed secret word attempts. | |
POSITION_STATUS | VARCHAR(40) | Status of the user's position. | Used in position change workflow |
PREVIOUS_POSITION | VARCHAR(100) | User's previous position title. | Used in position change workflow |
IS_VISIBLE | CHAR(1) | Indicates if the user is visible (Y or N). | |
CERT_DELEGATE_END_DATE | DATETIME | End date for delegated certification tasks. | Used in access certification |
CERT_DELEGATE_START_DATE | DATETIME | Start date for delegated certification tasks. | Used in access certification |
CERT_DELEGATE_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the delegate. | |
GRAPH_ID | VARCHAR(100) | Identifier used for graphical representation of relationships. | |
USER_HANDLE | BLOB | Binary handle of the user (e.g., biometric data). | |
CONVERSION_DATE | DATETIME | Timestamp of the user's last conversion (e.g., status change). |
Relationships
- One-to-Many: A User can have multiple Login Records → Referenced by LOGIN.USER_ID
- One-to-Many: A User can have multiple User Attributes → Referenced by USER_ATTRIBUTES.USER_ID
- One-to-Many: A User can belong to multiple Groups → Referenced by USER_GRP.USER_ID
- One-to-Many: A User can have multiple Roles → Referenced by USER_ROLE.USER_ID
- Many-to-Many: Users can have relationships with other users → Managed through USER_TO_USER_MEMBERSHIP
- Self-referencing relationship: User_owner_id references the users tables. This attribute has been deprecated and will be dropped in the next major rel
Table: LOGIN
Purpose: Stores authentication credentials, including linked accounts.
| Column Name | Data Type | Description | Notes |
|---|---|---|---|
LOGIN | VARCHAR(220) | The unique login identifier for the user in a specific managed system. | Primary Key |
MANAGED_SYS_ID | VARCHAR(32) | Foreign key referencing MANAGED_SYS.MANAGED_SYS_ID, indicating the managed system. | Primary Key |
IDENTITY_TYPE | VARCHAR(20) | Type of identity associated with the login (e.g., Primary, Service Account). | |
USER_ID | VARCHAR(32) | Foreign key referencing USERS.USER_ID, identifying the associated user. | Foreign Key to Users.USER_ID |
PASSWORD | VARCHAR(255) | Encrypted password for the user. | |
PWD_CHANGED | DATETIME | Timestamp of the last password change. | |
PWD_EXP | DATETIME | Timestamp indicating when the password will expire. | |
GRACE_PERIOD | DATETIME | End date for the grace period before login restrictions apply. | |
CREATE_DATE | DATETIME | Timestamp when the login was created. | |
CREATED_BY | VARCHAR(32) | User ID of the person who created the login record. | |
AUTH_FAIL_COUNT | INT(11) | Number of failed authentication attempts. | |
LAST_AUTH_ATTEMPT | DATETIME | Timestamp of the last authentication attempt. | |
LAST_LOGIN | DATETIME | Timestamp of the last successful login. | |
LAST_LOGIN_IP | VARCHAR(60) | IP address of the last successful login. | |
PREV_LOGIN | DATETIME | Timestamp of the previous successful login. | |
PREV_LOGIN_IP | VARCHAR(60) | IP address of the previous login. | |
PWD_CHANGE_COUNT | INT(11) | Number of times the password has been changed. | |
PSWD_RESET_TOKEN | VARCHAR(80) | Password reset token for account recovery. | |
PSWD_RESET_TOKEN_EXP | DATETIME | Expiration timestamp of the password reset token. | |
LOGIN_ID | VARCHAR(32) | Unique identifier for the login record. | |
LAST_UPDATE | TIMESTAMP | Timestamp of the last modification. | |
LOWERCASE_LOGIN | VARCHAR(220) | Lowercase version of the login ID for uniqueness enforcement. | |
PROV_STATUS | VARCHAR(20) | Provisioning status of the login (e.g., Active, Pending). | |
CHALLENGE_RESPONSE_FAIL_COUNT | INT(11) | Number of failed challenge-response attempts. | |
SMS_RESET_TOKEN | VARCHAR(10) | Temporary SMS-based reset token. | |
SMS_RESET_TOKEN_EXP | DATETIME | Expiration timestamp of the SMS reset token. | |
SMS_CODE_EXPIRATION | TIMESTAMP | Expiration timestamp for the SMS code. | |
TOPT_ACTIVE | CHAR(1) | Indicates if Time-based One-Time Password (TOTP) authentication is enabled (Y or N). | |
IS_ACTIVE | CHAR(1) | Indicates if the login is active (Y or N). | |
FIRST_TIME_LOGIN | CHAR(1) | Indicates if the login is the first-time login (Y or N). | |
RESET_PWD | VARCHAR(32) | Identifier for reset password tracking. | |
LAST_OTP_ACCESS | DATETIME | Timestamp of the last OTP authentication. | |
OTP_FAIL_COUNT | INT(11) | Number of failed OTP attempts. | |
OTP_ACTIVE | CHAR(1) | Indicates if OTP authentication is enabled (Y or N). | |
STATUS | VARCHAR(20) | Status of the login (e.g., Active, Suspended). | |
LOCK_MARKER | VARCHAR(20) | Lock status of the login (NOT_LOCKED, LOCKED). | |
AUTH_TYPE | VARCHAR(255) | Authentication type used for the login (e.g., LDAP, SAML). |
Relationships
- Many-to-One: A Login Record is linked to one User → References USERS.USER_ID
- Many-to-One: A Login Record is linked to an external Managed System → References MANAGED_SYS.MANAGED_SYS_ID
- On to Many: A Login Records can have many LOGIN_ATTRIBUTEs - References LOGIN_ATTRIBUTES.LOGIN_ID
Table: USER_ATTRIBUTES
Purpose: Stores custom attributes for users.
| Column Name | Data Type | Description |
|---|---|---|
ID | VARCHAR(32) | Unique identifier for the user attribute record. |
USER_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, representing the user to whom the attribute belongs. |
METADATA_ID | VARCHAR(32) | Foreign key linking to METADATA_ELEMENT.METADATA_ID, defining the type of attribute. |
NAME | VARCHAR(100) | Name of the attribute (e.g., Department, Skill, Certification). |
Relationships
- Many-to-One: A User Attribute is linked to one User → References Users.USER_ID
Table: LOGIN_ATTRIBUTE
Purpose: Stores additional attributes associated with logins.
| Column Name | Data Type | Description |
|---|---|---|
LOGIN_ATTR_ID | VARCHAR(32) | Unique identifier for the login attribute record. |
NAME | VARCHAR(100) | Name of the attribute (e.g., Role, Permission Level). |
VALUE | VARCHAR(4000) | Value of the attribute. |
METADATA_ID | VARCHAR(20) | Foreign key referencing METADATA_ELEMENT.METADATA_ID, defining metadata properties. |
LOGIN_ID | VARCHAR(32) | Foreign key referencing LOGIN.LOGIN_ID, linking the attribute to a login. |
Relationships: • One-to-one: A Login Attribute is linked to one LOGIN_ATTRIBUTE_VALUES → References LOGIN_ATTRIBUTE_VALUES.LOGIN_ATTRIBUTE_ID
Table: LOGIN_ATTRIBUTE_VALUES
Purpose: Stores a large (text) value assigned to a LOGIN_ATTRIBUTEs.
| Column Name | Data Type | Description |
|---|---|---|
LOGIN_ATTRIBUTE_ID | VARCHAR(32) | Foreign key referencing LOGIN_ATTRIBUTES.ID, linking the value to an attribute. |
ATTR_VALUE | TEXT | Value associated with the attribute. |
Relationships: • One-to-One: A Login Attribute Value is linked to a Login Attribute → References LOGIN_ATTRIBUTES.LOGIN_ATTR_ID
Table: USER_ATTRIBUTE_VALUES
Purpose: tores a large (text) value assigned to a USER_ATTRIBUTES.
| Column Name | Data Type | Description |
|---|---|---|
USER_ATTRIBUTE_ID | VARCHAR(32) | Foreign key linking to USER_ATTRIBUTES.ID, representing the specific user attribute. |
ATTR_VALUE | TEXT | Value assigned to the attribute (e.g., "Engineering", "Python Certified"). |
Relationships:
- One-to-One: A User Attribute Value is linked to a USER_ATTRIBUTES → References USER_ATTRIBUTES.ID
Table: USER_AFFILIATION
Purpose: Tracks a user’s affiliation with a company or organization.
| Column Name | Data Type | Description |
|---|---|---|
COMPANY_ID | VARCHAR(32) | Foreign key referencing COMPANY.COMPANY_ID, identifying the affiliated company. |
USER_ID | VARCHAR(32) | Foreign key referencing USERS.USER_ID, identifying the associated user. |
CREATE_DATE | DATETIME | Timestamp when the affiliation was created. |
METADATA_TYPE_ID | VARCHAR(32) | Foreign key referencing METADATA_TYPE.TYPE_ID, defining the type of affiliation. |
MEMBERSHIP_ID | VARCHAR(32) | Unique identifier for the user’s membership in the company. |
START_DATE | DATETIME | Start date of the user's affiliation. |
END_DATE | DATETIME | End date of the user's affiliation. |
DESCRIPTION | VARCHAR(255) | Additional details about the affiliation. |
EDGE_ID | VARCHAR(40) | Identifier used for graphical representation of relationships. |
Relationships:
- Many-to-One: A User Affiliation is linked to a User → References USERS.USER_ID
- Many-to-One: A User Affiliation is linked to a Company → References COMPANY.COMPANY_ID
Table: USER_AFFILIATION_RIGHTS
Purpose: Manages access rights associated with user affiliations.
| Column Name | Data Type | Description |
|---|---|---|
MEMBERSHIP_ID | VARCHAR(32) | Foreign key referencing USER_AFFILIATION.MEMBERSHIP_ID, linking the rights to a specific affiliation. |
ACCESS_RIGHT_ID | VARCHAR(32) | Foreign key referencing ACCESS_RIGHTS.ACCESS_RIGHT_ID, defining specific access rights granted. |
EDGE_ID | VARCHAR(40) | Identifier used for graphical representation of relationships. |
Relationships:
- Many-to-One: A User Affiliation Right is linked to a User Affiliation → References USER_AFFILIATION
Table: USER_AUTH_PARAM
Purpose: Stores authentication parameters for a user.
| Column Name | Data Type | Description |
|---|---|---|
PARAM_ID | VARCHAR(32) | Unique identifier for the authentication parameter. |
USER_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the associated user. |
TYPE | VARCHAR(50) | Type of authentication parameter (e.g., OTP, security key). |
PARAM_VALUE | VARCHAR(255) | Value of the authentication parameter. |
CONFIRMED | CHAR(1) | Indicates whether the parameter is confirmed (Y or N). |
CONFIRMED_TYPE | VARCHAR(25) | Type of confirmation (e.g., email, phone verification). |
Relationships:
- Many-to-One: A User Authentication Parameter is linked to a User → References USERS.USER_ID
Table: USER_ROLE
Purpose: Maps users to roles. Cross reference table.
| Column Name | Data Type | Description |
|---|---|---|
USER_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the user. |
ROLE_ID | VARCHAR(32) | Foreign key linking to ROLE.ROLE_ID, identifying the assigned role. |
MEMBERSHIP_ID | VARCHAR(32) | Unique identifier for the user's role membership. |
START_DATE | DATETIME | Start date of the user's role assignment. |
END_DATE | DATETIME | End date of the user's role assignment. |
DESCRIPTION | VARCHAR(255) | Additional details about the role assignment. |
EDGE_ID | VARCHAR(40) | Identifier used for graphical representation of relationships. |
STATUS | VARCHAR(15) | Status of the role assignment (default: NOT_REMINDED). |
Relationships
- Many-to-One: A User can have multiple Roles → References USERS.USER_ID
- Many-to-One: A Role can be assigned to multiple Users → References ROLE.ROLE_ID
Table: USER_GRP
Purpose: Associates users with groups. Cross reference table.
| Column Name | Data Type | Description |
|---|---|---|
USER_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the user. |
GRP_ID | VARCHAR(32) | Foreign key linking to GRP.GRP_ID, identifying the group. |
MEMBERSHIP_ID | VARCHAR(32) | Unique identifier for the user-group membership. |
START_DATE | DATETIME | Start date of the user's group membership. |
END_DATE | DATETIME | End date of the user's group membership. |
DESCRIPTION | VARCHAR(255) | Additional details about the membership. |
EDGE_ID | VARCHAR(40) | Identifier used for graphical representation of relationships. |
STATUS | VARCHAR(15) | Status of the membership (default: NOT_REMINDED). |
Relationships
- Many-to-One: A User can have multiple Groups → References USERS.USER_ID
- Many-to-One: A Group can be assigned to multiple Users → References GROUP.GRP_ID
Table: USER_GRP_MEMBERSHIP_RIGHTS
Purpose: Defines access rights within a group.
| Column Name | Data Type | Description |
|---|---|---|
MEMBERSHIP_ID | VARCHAR(32) | Foreign key linking to USER_GRP.MEMBERSHIP_ID, identifying the user’s group membership. |
ACCESS_RIGHT_ID | VARCHAR(32) | Foreign key linking to ACCESS_RIGHTS.ACCESS_RIGHT_ID, defining specific access rights. |
EDGE_ID | VARCHAR(40) | Identifier used for graphical representation of relationships. |
Relationships:
- Many-to-One: A User Group Membership Right is linked to a User Group → References USER_GRP
Table: USER_IDENTITY_ANS
Purpose: Stores answers to user security questions.
| Column Name | Data Type | Description |
|---|---|---|
IDENTITY_ANS_ID | VARCHAR(32) | Unique identifier for the user’s identity answer. |
IDENTITY_QUESTION_ID | VARCHAR(32) | Foreign key linking to IDENTITY_QUESTION.IDENTITY_QUESTION_ID, identifying the security question. |
USER_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the user. |
QUESTION_ANSWER | VARCHAR(2000) | User’s answer to the security question. |
IS_ENCRYPTED | VARCHAR(1) | Indicates whether the answer is encrypted (Y or N). |
QUESTION_TEXT | VARCHAR(2000) | The actual security question text (if stored with the answer). |
Relationships:
- Many-to-One: A User Identity Answer is linked to a User → References USERS.USER_ID
Table: USER_IT_POLICY_HISTORY
Purpose: Tracks IT policy acceptance history.
| Column Name | Data Type | Description |
|---|---|---|
ID | VARCHAR(32) | Unique identifier for the IT policy history record. |
USER_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the user. |
VERSION_ID | VARCHAR(32) | Foreign key linking to IT_POLICY_VERSION.VERSION_ID, identifying the version of the policy. |
SUBMIT_DATE | TIMESTAMP | Timestamp when the user accepted or submitted the policy. |
ACCEPTED | CHAR(1) | Indicates whether the policy was accepted (Y or N). |
NAME | VARCHAR(128) | Name of the policy version. |
CONTENT_PROVIDER_ID | VARCHAR(32) | Foreign key linking to CONTENT_PROVIDER.CONTENT_PROVIDER_ID, identifying the provider of the policy content. |
Relationships:
- Many-to-One: A User IT Policy History is linked to a User → References USERS.USER_ID
Table: USER_KEY
| Column Name | Data Type | Description |
|---|---|---|
USER_KEY_ID | VARCHAR(32) | Unique identifier for the user's key record. |
USER_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the user. |
NAME | VARCHAR(40) | Name of the key (e.g., SSH Key, API Key). |
KEY_VALUE | VARCHAR(2048) | Actual value of the stored key. |
Table: USER_NOTE
| Column Name | Data Type | Description |
|---|---|---|
USER_NOTE_ID | VARCHAR(32) | Unique identifier for the user note record. |
USER_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the user. |
NOTE_TYPE | VARCHAR(20) | Type of note (e.g., General, Compliance, Warning). |
DESCRIPTION | VARCHAR(2000) | Detailed description of the note. |
CREATE_DATE | DATETIME | Timestamp when the note was created. |
CREATED_BY | VARCHAR(20) | Identifier of the user who created the note. |
Table: USER_RES_MEMBERSHIP_RIGHTS
| Column Name | Data Type | Description |
|---|---|---|
MEMBERSHIP_ID | VARCHAR(32) | Foreign key linking to RESOURCE_USER.MEMBERSHIP_ID, identifying the user’s membership to a resource. |
ACCESS_RIGHT_ID | VARCHAR(32) | Foreign key linking to ACCESS_RIGHTS.ACCESS_RIGHT_ID, defining specific access rights. |
EDGE_ID | VARCHAR(40) | Identifier used for graphical representation of relationships. |
Table: USER_ROLE_MEMBERSHIP_RIGHTS
| Column Name | Data Type | Description |
|---|---|---|
MEMBERSHIP_ID | VARCHAR(32) | Foreign key linking to USER_ROLE.MEMBERSHIP_ID, identifying the user’s role membership. |
ACCESS_RIGHT_ID | VARCHAR(32) | Foreign key linking to ACCESS_RIGHTS.ACCESS_RIGHT_ID, defining specific access rights. |
EDGE_ID | VARCHAR(40) | Identifier used for graphical representation of relationships. |
Table: USER_TO_USER_MEMBERSHIP
Purpose: Defines user-to-user relationships. This table is used to supported the Related user functionality. Example, a User can have a primary account and an admin account.
| Column Name | Data Type | Description |
|---|---|---|
MEMBERSHIP_ID | VARCHAR(32) | Unique identifier for the user-to-user membership. |
PRIMARY_USER_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the primary user. |
RELATED_USER_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the related user. |
TYPE_ID | VARCHAR(32) | Foreign key linking to METADATA_TYPE.TYPE_ID, defining the relationship type. |
DESCRIPTION | VARCHAR(255) | Additional details about the user-to-user membership. |
Relationships
- Many-to-Many: Users can have relationships with multiple users → References Users.USER_ID
Table: ADDRESS
| Column Name | Data Type | Description |
|---|---|---|
ADDRESS_ID | VARCHAR(32) | Unique identifier for the address. |
NAME | VARCHAR(100) | Name associated with the address (e.g., Home, Work). |
COUNTRY | VARCHAR(100) | Country of the address. |
BLDG_NUM | VARCHAR(100) | Building number. |
STREET_DIRECTION | VARCHAR(20) | Street direction (e.g., N, S, E, W). |
SUITE | VARCHAR(20) | Suite or apartment number. |
ADDRESS1 - ADDRESS7 | VARCHAR(400) | Additional address lines for extended addresses. |
CITY | VARCHAR(100) | City of the address. |
STATE | VARCHAR(100) | State or province. |
POSTAL_CD | VARCHAR(100) | Postal or ZIP code. |
IS_DEFAULT | CHAR(1) | Indicates if this is the default address (Y or N). |
DESCRIPTION | VARCHAR(100) | Additional description. |
ACTIVE | CHAR(1) | Indicates if the address is active (Y or N). |
PARENT_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the user. |
LAST_UPDATE | TIMESTAMP | Timestamp of last modification. |
CREATE_DATE | TIMESTAMP | Timestamp of creation. |
TYPE_ID | VARCHAR(32) | Foreign key linking to METADATA_TYPE.TYPE_ID, defining the address type. |
COPY_FROM_LOCATION_ID | VARCHAR(32) | If copied from another location, references that location. |
PUBLISHED | CHAR(1) | Indicates if the address is published (Y or N). |
Table: AUTH_STATE
| Column Name | Data Type | Description |
|---|---|---|
USER_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the user. |
AUTH_STATE | DECIMAL(5,1) | Authentication state of the user. |
LAST_LOGIN | DATETIME | Timestamp of the last login. |
IP_ADDRESS | VARCHAR(30) | IP address of the last login. |
SESSION_ID | VARCHAR(32) | Unique session identifier. |
CONTENT_PROVIDER_ID | VARCHAR(32) | Foreign key linking to CONTENT_PROVIDER.CONTENT_PROVIDER_ID, identifying the content provider. |
IS_NEW_FLAGS | INT(5) | Flags for additional authentication-related data. |
Table: AUTH_STATE_AUTH_PARAM_XREF
| Column Name | Data Type | Description |
|---|---|---|
XREF_ID | VARCHAR(32) | Unique identifier for the cross-reference record. |
USER_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the user. |
SESSION_ID | VARCHAR(32) | Foreign key linking to AUTH_STATE.SESSION_ID, identifying the session. |
PARAM_ID | VARCHAR(32) | Foreign key linking to USER_AUTH_PARAM.PARAM_ID, identifying the authentication parameter. |
Table: ADDRESS
Purpose: Stores physical address information associated with users.
| Column Name | Data Type | Description |
|---|---|---|
ADDRESS_ID | VARCHAR(32) | Unique identifier for the address. |
NAME | VARCHAR(100) | Name associated with the address (e.g., Home, Work). |
COUNTRY | VARCHAR(100) | Country of the address. |
BLDG_NUM | VARCHAR(100) | Building number. |
STREET_DIRECTION | VARCHAR(20) | Street direction (e.g., N, S, E, W). |
SUITE | VARCHAR(20) | Suite or apartment number. |
ADDRESS1 - ADDRESS7 | VARCHAR(400) | Additional address lines for extended addresses. |
CITY | VARCHAR(100) | City of the address. |
STATE | VARCHAR(100) | State or province. |
POSTAL_CD | VARCHAR(100) | Postal or ZIP code. |
IS_DEFAULT | CHAR(1) | Indicates if this is the default address (Y or N). |
DESCRIPTION | VARCHAR(100) | Additional description. |
ACTIVE | CHAR(1) | Indicates if the address is active (Y or N). |
PARENT_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the user. |
LAST_UPDATE | TIMESTAMP | Timestamp of last modification. |
CREATE_DATE | TIMESTAMP | Timestamp of creation. |
TYPE_ID | VARCHAR(32) | Foreign key linking to METADATA_TYPE.TYPE_ID, defining the address type. |
COPY_FROM_LOCATION_ID | VARCHAR(32) | If copied from another location, references that location. |
PUBLISHED | CHAR(1) | Indicates if the address is published (Y or N). |
Relationships:
• Many-to-One: An Address is associated with a User based on PARENT_ID. A user can have amy Addresses.
Table: EMAIL_ADDRESS
Purpose: Stores email addresses associated with users.
| Column Name | Data Type | Description |
|---|---|---|
EMAIL_ID | VARCHAR(32) | Unique identifier for the email record. |
NAME | VARCHAR(100) | Name associated with the email (e.g., Personal, Work). |
DESCRIPTION | VARCHAR(100) | Additional description. |
EMAIL_ADDRESS | VARCHAR(320) | The actual email address. |
IS_DEFAULT | CHAR(1) | Indicates if this is the default email (Y or N). |
ACTIVE | CHAR(1) | Indicates if the email is active (Y or N). |
PARENT_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the user. |
LAST_UPDATE | TIMESTAMP | Timestamp of last modification. |
CREATE_DATE | TIMESTAMP | Timestamp of creation. |
TYPE_ID | VARCHAR(32) | Foreign key linking to METADATA_TYPE.TYPE_ID, defining the email type. |
PUBLISHED | CHAR(1) | Indicates if the email is published (Y or N). |
Relationships:
- Many-to-One: An Email Address is associated with a User based on PARENT_ID
Table: PHONE
Purpose: Stores phone number information associated with users
| Column Name | Data Type | Description |
|---|---|---|
PHONE_ID | VARCHAR(32) | Unique identifier for the phone record. |
NAME | VARCHAR(40) | Name associated with the phone (e.g., Mobile, Office). |
AREA_CD | VARCHAR(20) | Area code of the phone number. |
COUNTRY_CD | VARCHAR(20) | Country code of the phone number. |
DESCRIPTION | VARCHAR(100) | Additional description. |
PHONE_NBR | VARCHAR(50) | The actual phone number. |
PHONE_EXT | VARCHAR(20) | Extension number (if applicable). |
IS_DEFAULT | CHAR(1) | Indicates if this is the default phone (Y or N). |
ACTIVE | CHAR(1) | Indicates if the phone number is active (Y or N). |
PARENT_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the user. |
LAST_UPDATE | TIMESTAMP | Timestamp of last modification. |
CREATE_DATE | TIMESTAMP | Timestamp of creation. |
TYPE_ID | VARCHAR(32) | Foreign key linking to METADATA_TYPE.TYPE_ID, defining the phone type. |
PUBLISHED | CHAR(1) | Indicates if the phone is published (Y or N). |
IS_FOR_SMS | CHAR(1) | Indicates if the phone number is used for SMS communication (Y or N). |
Relationships:
- Many-to-One: A Phone record is associated with a User based on PARENT_ID
Table: COMPANY
Purpose: Stores information about companies or organizations.
| Column Name | Data Type | Description |
|---|---|---|
COMPANY_ID | VARCHAR(32) | Unique identifier for the company. |
COMPANY_NAME | VARCHAR(200) | Full name of the company. |
LST_UPDATE | DATETIME | Last update timestamp. |
LST_UPDATED_BY | VARCHAR(32) | User who last updated the record. |
PARENT_ID | VARCHAR(32) | Foreign key linking to the parent company. |
STATUS | VARCHAR(20) | Current status of the company. |
CREATE_DATE | DATETIME | Date when the company was created. |
CREATED_BY | VARCHAR(32) | User who created the company record. |
ALIAS | VARCHAR(200) | Alias or alternative name for the company. |
DESCRIPTION | VARCHAR(512) | Detailed description of the company. |
DOMAIN_NAME | VARCHAR(250) | Domain name associated with the company. |
LDAP_STR | VARCHAR(255) | LDAP string for directory integrations. |
CLASSIFICATION | VARCHAR(40) | Classification of the company. |
ORG_TYPE_ID | VARCHAR(32) | Foreign key linking to ORGANIZATION_TYPE.ORG_TYPE_ID. |
IS_SELECTABLE | CHAR(1) | Determines if the company is selectable (Y or N). |
TYPE_ID | VARCHAR(32) | Foreign key linking to METADATA_TYPE.TYPE_ID. |
POLICY_ID | VARCHAR(32) | Foreign key linking to POLICY.POLICY_ID. |
CERTIFIER_ID | VARCHAR(32) | Foreign key linking to USERS.USER_ID, identifying the certifier. |
Relationships:
- One-to-Many: A Company can have multiple Users affiliated with it → Referenced by USER_AFFILIATION.COMPANY_ID.
- One-to-Many: A Company can have multiple Company Attributes → Referenced by COMPANY_ATTRIBUTE.COMPANY_ID.
- One-to-Many: A Company can have multiple Company Memberships → Referenced by COMPANY_TO_COMPANY_MEMBERSHIP.COMPANY_ID.
- Many-to-One: A Company can belong to a parent company → Self-referencing COMPANY.PARENT_ID.
Table: COMPANY_ATTRIBUTE
Purpose: Stores custom attributes that can be assigned to companies.
| Column Name | Data Type | Description |
|---|---|---|
COMPANY_ATTR_ID | VARCHAR(32) | Unique identifier for the company attribute. |
COMPANY_ID | VARCHAR(32) | Foreign key linking to COMPANY.COMPANY_ID. |
NAME | VARCHAR(100) | Name of the attribute. |
METADATA_ID | VARCHAR(32) | Foreign key linking to METADATA_ELEMENT.METADATA_ID. |
Relationships:
- Many-to-One: A Company Attribute is linked to a Company → References COMPANY.COMPANY_ID.
- One-to-Many: A Company Attribute can have multiple Company Attribute Values → Referenced by COMPANY_ATTRIBUTE_VALUES.ATTR_ID.
Table: COMPANY_ATTRIBUTE_VALUES
Purpose: Stores values assigned to company attributes.
| Column Name | Data Type | Description |
|---|---|---|
COMPANY_ATTRIBUTE_ID | VARCHAR(32) | Foreign key linking to COMPANY_ATTRIBUTE.COMPANY_ATTR_ID. |
ATTR_VALUE | TEXT | Value of the company attribute. |
Relationships:
- Many-to-One: A Company Attribute Value is linked to a Company Attribute → References COMPANY_ATTRIBUTE.COMPANY_ATTR_ID.
Table: COMPANY_TO_COMPANY_MEMBERSHIP
Purpose: Defines relationships between companies, such as parent-subsidiary relationships.
| Column Name | Data Type | Description |
|---|---|---|
COMPANY_ID | VARCHAR(32) | Foreign key linking to COMPANY.COMPANY_ID, identifying the parent company. |
MEMBER_COMPANY_ID | VARCHAR(32) | Foreign key linking to COMPANY.COMPANY_ID, identifying the subsidiary or member company. |
CREATE_DATE | TIMESTAMP | Timestamp of membership creation. |
UPDATE_DATE | TIMESTAMP | Timestamp of last membership update. |
CREATED_BY | VARCHAR(32) | User who created the membership. |
UPDATED_BY | VARCHAR(32) | User who last updated the membership. |
MEMBERSHIP_ID | VARCHAR(32) | Unique identifier for the membership. |
START_DATE | DATETIME | Start date of the membership. |
END_DATE | DATETIME | End date of the membership. |
DESCRIPTION | VARCHAR(255) | Additional details about the membership. |
EDGE_ID | VARCHAR(40) | Identifier used for graphical representation. |
Relationships:
- Many-to-Many: A Company can be a parent to multiple companies → References COMPANY.COMPANY_ID as COMPANY_ID and MEMBER_COMPANY_ID.
- Many-to-One: A Company Membership is linked to a Parent Company → References COMPANY.COMPANY_ID as COMPANY_ID.
- Many-to-One: A Company Membership is linked to a Subsidiary Company → References COMPANY.COMPANY_ID as MEMBER_COMPANY_ID.