The Master Index Database Structure (Repository)

The following topics provide information about the master index database, including descriptions of each table and a sample entity relationship diagram. All information in these topics pertains to the default version of the database. Your implementation might vary depending on the customization made to the Object Definition and to the scripts used to create the master index database.

About the Master Index Database (Repository)
Master Index Database Table Details (Repository)
Sample Master Index Database Model (Repository)

About the Master Index Database (Repository)

The master index database stores information about the entities being indexed, such as people or businesses. The database stores records from local systems in their original form and also stores a record for each object that is considered to be the single best record (SBR).

The structure of the database tables that store object information is dependent on the information specified in the Object Definition file created by the wizard. Oracle Java CAPS Master Index generates a script to create the tables and fields in the database based on the information in the Object Definition file. If you update the Object Definition file, regenerating the application updates the database scripts accordingly. This allows you to define the database as you define the object structure.

While most of the structures created in the database are based on information in the Object Definition file, some of the tables, such as sbyn_seq_table and sbyn_common_detail, are standard for all implementations. The database includes tables that store information about the objects defined for the master index application as well as tables that store common maintenance information, transactional information, and external system information. The database includes the tables listed in Table 2.

Table 2 Master Index Database Tables

Table Name	Description
SBYN_`OBJECT_NAME`	Stores information for the parent objects associated with local system records. This database table is named by the parent object name. For example, a table storing company objects is named sbyn_company; a table storing person objects is named sbyn_person. Only one table stores parent object information for system records.
SBYN`_OBJECT_NAME`SBR	Stores information for the parent objects associated with single best records. This database table is named by the parent object name followed by “SBR”. For example, a table storing company objects is named sbyn_companysbr; a table storing person objects is named sbyn_personsbr. Only one table stores parent object information for SBRs.
SBYN_`CHILD_OBJECT`	Stores information for child objects associated with local system records. These database tables are named by their object name. For example, a table storing address objects is named sbyn_address; a table storing comment objects is named sbyn_comment. One database table is created for each child object defined in the object structure.
SBYN_`CHILD_OBJECT`SBR	Stores information for child objects associated with a single best record. These database tables are named by their object name followed by “SBR”. For example, a table storing address objects is named sbyn_addresssbr; a table storing comment objects is named sbyn_commentsbr. One SBR database table is created for each child object defined in the object structure.
SBYN_APPL	Lists the applications with which each item in stc_common_header is associated.
SBYN_ASSUMEDMATCH	Stores information about records that were automatically matched by the master index application.
SBYN_AUDIT	Stores audit information about each time object information is accessed from the EDM. Note - If audit logging is enabled, this table can grow very large and might require periodic archiving.
SBYN_COMMON_DETAIL	Contains all of the processing codes associated with the items listed in sbyn_common_header.
SBYN_COMMON_HEADER	Contains a list of the different types of processing codes used by the master index application. These types are also associated with the drop-down lists you can specify for the EDM.
SBYN_ENTERPRISE	Stores the local ID and system pairs, along with their associated EUID.
SBYN_MERGE	Stores information about all merge and unmerge transactions processed from either external systems or the EDM.
SBYN_OVERWRITE	Stores information about fields that are locked for updates in an SBR.
SBYN_POTENTIALDUPLICATES	Stores a list of potential duplicate records and flags potential duplicate pairs that have been resolved.
SBYN_SEQ_TABLE	Stores the sequential codes that are used in other tables in the database, such as EUIDs, transaction numbers, and so on.
SBYN_SYSTEMOBJECT	Stores information about the system objects in the database, including the local ID and system, create date and user, status, and so on.
SBYN_SYSTEMS	Stores a list of systems in your organization, along with defining information.
SBYN_SYSTEMSBR	Stores transaction information about an SBR, such as the create or update date, status, and so on.
SBYN_TRANSACTION	Stores a history of changes to each record stored in the database.
SBYN_USER_CODE	Like the sbyn_common_detail table, this table stores processing codes and drop-down list values. This table contains additional validation information that allows you to validate information in a dependent field (for example, to validate cities against the entered postal code).

Master Index Database Table Details (Repository)

The tables in the following topics describe each column in the default database tables.

SBYN_OBJECT_NAME
SBYN_OBJECT_NAMESBR
SBYN_CHILD_OBJECT
SBYN_CHILD_OBJECTSBR
SBYN_APPL
SBYN_ASSUMEDMATCH
SBYN_AUDIT
SBYN_COMMON_DETAIL
SBYN_COMMON_HEADER
SBYN_ENTERPRISE
SBYN_MERGE
SBYN_OVERWRITE
SBYN_POTENTIALDUPLICATES
SBYN_SEQ_TABLE
SBYN_SYSTEMOBJECT
SBYN_SYSTEMS
SBYN_SYSTEMSBR
SBYN_TRANSACTION
SBYN_USER_CODE

The columns are identical for Oracle and SQL Server databases, but the data types differ in some cases. Table 3 lists the data type differences, and the differences are noted in the Data Type column for each table in this section.

Table 3 Oracle and SQL Server Data Type Differences

Oracle Data Type	SQL Server Data Type
BLOB	Varbinary(MAX)
DATE	DateTime
INTEGER	Int
LONG	Varchar(MAX)
NUMBER	Numeric
TIMESTAMP	DateTime
VARCHAR2	Varchar

SBYN_`OBJECT_NAME`

This table stores the parent object in each system record received by the master index application. It is linked to the tables that store each child object in the system record by the object_nameid column (where object_name is the name of the parent object). This table contains the columns listed below regardless of the design of the object structure, and also contains a column for each field you defined for the parent object in the Object Definition file. Columns to store standardized or phonetic versions of certain fields are automatically added when you specify certain match types in the wizard.

The differences in data types between Oracle and SQL Server are noted in Table 4. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 4 SBYN_OBJECT_NAME Table Description

Column Name	Data Type	Column Description
SYSTEMCODE	VARCHAR2(20) Varchar(20)	The system code for the system record.
LID	VARCHAR2(25) Varchar(25)	A local identification code assigned by the specified system.
`OBJECT_NAME`ID	VARCHAR2(20) Varchar(20)	A unique ID for the parent object in a system record. This is named according to the parent object. For example, if the parent object is “Company”, the name of this column is “companyid”; if the parent object is “Person”, the name of this column is “personid”.
`FIELD_NAME`	Varies	The name of each field in the parent object. A database column is created for each field, and the data type depends on the type specified in the Object Definition file.

SBYN_`OBJECT_NAME`SBR

This table stores the parent object of the SBR for each enterprise object in the master index database. It is linked to the tables that store each child object in the SBR by the object_nameid column (where object_name is the name of the parent object). This table contains the columns listed below regardless of the design of the object structure, and also contains a column for each field defined for the parent object in the Object Definition file. In addition, columns to store standardized or phonetic versions of certain fields are automatically added when you specify certain match types in the wizard.

The differences in data types between Oracle and SQL Server are noted in Table 5. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 5 SBYN_OBJECT_NAMESBR Table Description

Column Name	Data Type	Column Description
EUID	VARCHAR2(20) Varchar(20)	The enterprise unique identifier assigned by the master index application.
`OBJECT_NAME`ID	VARCHAR2(20) Varchar(20)	A unique ID for the parent object in a system record. This is named according to the parent object. For example, if the parent object is “Company”, the name of this column is “companyid”; if the parent object is “Person”, the name of this column is “personid”.
`FIELD_NAME`	Varies	The name of each field in the parent object. A database column is created for each field, and the data type depends on the type specified in the Object Definition file.

SBYN_`CHILD_OBJECT`

The sbyn_child_object tables (where child_object is the name of a child object in the object structure) store information about the child objects associated with a system record in the master index application. All tables storing child object information for system records contain the columns listed below. The remaining columns are defined by the fields you specify for each child object in the object structure, including any standardized or phonetic fields.

The differences in data types between Oracle and SQL Server are noted in Table 6. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 6 SBYN_CHILD_OBJECT and SBYN_CHILD_OBJECTSBR Table Description

Column Name	Data Type	Column Description
`OBJECT_NAME`ID	VARCHAR2(20) Varchar(20)	The unique ID for the parent object associated with the child object in the system record.
`CHILD_OBJECT`ID	VARCHAR2(20) Varchar(20)	The unique ID for each record in the table. This column cannot be null.
`FIELD_NAME`	Varies	The name of each field in the child object. A database column is created for each field, and the data type depends on the type specified in the Object Definition file.

SBYN_`CHILD_OBJECT`SBR

The sbyn_child_objectsbr tables (where child_object is the name of a child object in the object structure) store information about the child objects associated with an SBR in the master index application. All tables storing child object information for SBRs contain the columns listed below. The remaining columns are defined by the fields you specify for each child object in the Object Definition file, including any standardized or phonetic fields.

The differences in data types between Oracle and SQL Server are noted in Table 7. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 7 SBYN_CHILD_OBJECT and SBYN_CHILD_OBJECTSBR Table Description

Column Name	Data Type	Column Description
`OBJECT_NAME`ID	VARCHAR2(20) Varchar(20)	The unique ID for the parent object associated with the child object in the SBR.
`CHILD_OBJECT`ID	VARCHAR2(20) Varchar(20)	The unique ID for each record in the table. This column cannot be null.
`FIELD_NAME`	Varies	The name of each field in the child object. A database column is created for each field, and the data type depends on the type specified in the Object Definition file.

SBYN_APPL

This table stores information about the applications used in the master index system. The differences in data types between Oracle and SQL Server are noted in Table 8. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 8 SBYN_APPL Table Description

Column Name	Data Type	Description
APPL_ID	NUMBER(10) Numeric(10, 0)	The unique sequence number code for the listed application.
CODE	VARCHAR2(8) Varchar(8)	A unique code for the application.
DESCR	VARCHAR2(30) Varchar(30)	A brief description of the application.
READ_ONLY	CHAR(1)	An indicator of whether the current entry can be modified. If the value of this column is “Y”, the entry cannot be modified.
CREATE_DATE	DATE datetime	The date the application entry was created.
CREATE_USERID	VARCHAR2(20) Varchar(20)	The logon ID of the user who created the application entry.

SBYN_ASSUMEDMATCH

This table maintains a record of each assumed match transaction that occurs in the master index application, allowing you to review these transactions and, if necessary, reverse an assumed match. This table can grow quite large over time and might require periodic archiving. The differences in data types between Oracle and SQL Server are noted in Table 9. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 9 SBYN_ASSUMEDMATCH Table Description

Column Name	Data Type	Description
ASSUMEDMATCHID	VARCHAR2(20) Varchar(20)	The unique ID for the assumed match transaction.
EUID	VARCHAR2(20) Varchar(20)	The EUID into which the incoming record was merged.
SYSTEMCODE	VARCHAR2(20) Varchar(20)	The system code for the source system (that is, the system from which the incoming record originated).
LID	VARCHAR2(25) Varchar(25)	The local ID of the record in the source system.
WEIGHT	VARCHAR2(20) Varchar(20)	The matching weight between the incoming record and the EUID record into which it was merged.
TRANSACTION NUMBER	VARCHAR2(20) Varchar(20)	The transaction number associated with the assumed match.

SBYN_AUDIT

This table maintains a log of each instance in which any of the master index database tables are accessed through the EDM. This includes each time a record appears on a search results page, a comparison page, the View/Edit page, and so on. This log is only maintained if the EDM is configured for it. This table can grow very large over time and might require periodic archiving. The differences in data types between Oracle and SQL Server are noted in Table 10. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 10 SBYN_AUDIT Table Description

Column Name	Data Type	Description
AUDIT_ID	VARCHAR2(20) Varchar(20)	The unique identification code for the audit record. This column cannot be null.
PRIMARY_OBJECT_TYPE	VARCHAR2(20) Varchar(20)	The name of the parent object as defined in the Object Definition file.
EUID	VARCHAR2(15) Varchar(15)	The EUID whose information was accessed during an EDM transaction.
EUID_AUX	VARCHAR2(15) Varchar(15)	The second EUID whose information was accessed during an EDM transaction. A second EUID appears when viewing information about merge and unmerge transactions, comparisons, and so on.
FUNCTION (Oracle) OPERATION (SQL Server)	VARCHAR2(32) Varchar(32)	The type of transaction that caused the audit record to be written. This column cannot be null.
DETAIL	VARCHAR2(120) Varchar(120)	A brief description of the transaction that caused the audit record to be written.
CREATE_DATE	DATE datetime	The date the transaction that created the audit record was performed. This column cannot be null.
CREATE_BY	VARCHAR2(20) Varchar(20)	The user ID of the person who performed the transaction that caused the audit log. This column cannot be null.

SBYN_COMMON_DETAIL

This table stores the processing codes and descriptions for all of the common maintenance data elements. This is the detail table for sbyn_common_header. Each data element in sbyn_common_detail is associated with a data type in sbyn_common_header by the common_header_id column. None of the columns in this table can be null. The differences in data types between Oracle and SQL Server are noted in Table 11. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 11 SBYN_COMMON_DETAIL Table Description

Column Name	Data Type	Description
COMMON_DETAIL_ID	NUMBER(10) numeric(10, 0)	The unique identification code of the common table data element.
COMMON_HEADER_ID	NUMBER(10) numeric(10, 0)	The unique identification code of the common table data type associated with the data element (as stored in the common_header_id column of the sbyn_common_header table).
CODE	VARCHAR2(20) Varchar(20)	The processing code for the common table data element.
DESCR	VARCHAR2(50) Varchar(50)	A description of the common table data element.
READ_ONLY	CHAR(1)	An indicator of whether the common table data element can be modified.
CREATE_DATE	DATE datetime	The date the data element record was created.
CREATE_USERID	VARCHAR2(20) Varchar(20)	The user ID of the person who created the data element record.

SBYN_COMMON_HEADER

This table stores a description of each type of common maintenance data and is the header table for sbyn_common_detail. Together, these tables store the processing codes and drop-down menu descriptions for each common table data type. For a person index, common table data types might include Religion, Language, Marital Status, and so on. For a business index, common table data types might include Address Type, Phone Type, and so on. None of the columns in this table can be null.

The differences in data types between Oracle and SQL Server are noted in Table 12. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 12 SBYN_COMMON_HEADER Table Description

Column Name	Data Type	Description
COMMON_HEADER_ID	VARCHAR2(10) Varchar(10)	The unique identification code of the common table data type.
APPL_ID	VARCHAR2(10) Varchar(10)	The application ID from sbyn_appl that corresponds to the application for which the common table data type is used.
CODE	VARCHAR2(8) Varchar(8)	A unique processing code for the common table data type.
DESCR	VARCHAR2(50) Varchar(50)	A description of the common table data type.
READ_ONLY	CHAR(1)	An indicator of whether an entry in the table is read-only (if this column is set to “Y”, the entry is read-only).
MAX_INPUT_LEN	NUMBER(10) numeric(10, 0)	The maximum number of characters allowed in the code column for the common table data type.
TYP_TABLE_CODE	VARCHAR2(3) Varchar(3)	This column is not currently used.
CREATE_DATE	DATE datetime	The date the common table data type record was created.
CREATE_USERID	VARCHAR2(20) Varchar(20)	The user ID of the person who created the common table data type record.

SBYN_ENTERPRISE

This table stores a list of all the system and local ID pairs assigned to the enterprise records in the database, along with the associated EUID for each pair. This table is linked to sbyn_systemobject by the systemcode and lid columns, and is linked to sbyn_systemsbr by the euid column. This table maintains links between the SBR and its associated system objects. None of the columns in this table can be null.

The differences in data types between Oracle and SQL Server are noted in Table 13. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 13 SBYN_ENTERPRISE Table Description

Column Name	Data Type	Description
SYSTEMCODE	VARCHAR2(20) Varchar(20)	The processing code of the system associated with the local ID.
LID	VARCHAR2(25) Varchar(25)	The local ID associated with the system and EUID.
EUID	VARCHAR2(20) Varchar(20)	The EUID associated with the local ID and system.

SBYN_MERGE

This table maintains a record of each merge transaction that occurs in the master index application, both through the EDM and from external systems. It also records any unmerges that occur. The differences in data types between Oracle and SQL Server are noted in Table 14. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 14 SBYN_MERGE Table Description

Column Name	Data Type	Description
MERGE_ID	VARCHAR2(20) Varchar(20)	The unique, sequential identification code of merge record. This column cannot be null.
KEPT_EUID	VARCHAR2(20) Varchar(20)	The EUID of the record that was retained after the merge transaction. This column cannot be null.
MERGED_EUID	VARCHAR2(20) Varchar(20)	The EUID of the record that was not retained after the merge transaction.
MERGE_TRANSACTIONNUM	VARCHAR2(20) Varchar(20)	The transaction number associated with the merge transaction. This column cannot be null.
UNMERGE_TRANSACTIONNUM	VARCHAR2(20) Varchar(20)	The transaction number associated with the unmerge transaction.

SBYN_OVERWRITE

This table stores information about the fields that are locked for updates in the SBRs. It stores the EUID of the SBR, the ePath to the field, and the current locked value of the field. The differences in data types between Oracle and SQL Server are noted in Table 15. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 15 SBYN_OVERWRITE Table Description

Column Name	Data Type	Description
EUID	VARCHAR2(20) Varchar(20)	The EUID of an SBR containing fields for which the overwrite lock is set.
PATH	VARCHAR2(200) Varchar(20)	The ePath to a field that is locked in an SBR from the EDM.
TYPE	VARCHAR2(20) Varchar(20)	The data type of a field that is locked in an SBR.
INTEGERDATA	NUMBER(38) numeric(38, 0)	The data that is locked for overwrite in an integer field.
BOOLEANDATA	NUMBER(38) numeric(38, 0)	The data that is locked for overwrite in a boolean field.
STRINGDATA	VARCHAR2(200) Varchar(200)	The data that is locked for overwrite in a string field.
BYTEDATA	CHAR(2)	The data that is locked for overwrite in a byte field.
LONGDATA	LONG varchar(MAX)	The data that is locked for overwrite in a long integer field.
DATEDATA	DATE datetime	The data that is locked for overwrite in a date field.
FLOATDATA	NUMBER(38,4) numeric(38, 4)	The data that is locked for overwrite in a floating decimal field.
TIMESTAMPDATA	DATE datetime	The data that is locked for overwrite in a timestamp field.

SBYN_POTENTIALDUPLICATES

This table maintains a list of all records that are potential duplicates of one another. It also maintains a record of whether a potential duplicate pair has been resolved or permanently resolved. The differences in data types between Oracle and SQL Server are noted in Table 16. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 16 SBYN_POTENTIALDUPLICATES Table Description

Column Name	Data Type	Description
POTENTIALDUPLICATEID	VARCHAR2(20) Varchar(20)	The unique identification number of the potential duplicate transaction.
WEIGHT	VARCHAR2(20) Varchar(20)	The matching weight of the potential duplicate pair.
TYPE	VARCHAR2(15) Varchar(15)	This column is reserved for future use.
DESCRIPTION	VARCHAR2(120) Varchar(120)	A description of what caused the potential duplicate flag.
STATUS	VARCHAR2(15) Varchar(15)	The status of the potential duplicate pair. The possible values are: U – Unresolved R – Resolved A – Resolved permanently
HIGHMATCHFLAG	VARCHAR2(15) Varchar(15)	This column is reserved for future use.
RESOLVEDUSER	VARCHAR2(30) Varchar(30)	The user ID of the person who resolved the potential duplicate status.
RESOLVEDDATE	DATE datetime	The date the potential duplicate status was resolved.
RESOLVEDCOMMENT	VARCHAR2(120) Varchar(120)	Comments regarding the resolution of the duplicate status. This is not currently used.
EUID2	VARCHAR2(20) Varchar(20)	The EUID of the second record in the potential duplicate pair.
TRANSACTIONNUMBER	VARCHAR2(20) Varchar(20)	The transaction number associated with the transaction that produced the potential duplicate flag.
EUID1	VARCHAR2(20) Varchar(20)	The EUID of the first record in the potential duplicate pair.

SBYN_SEQ_TABLE

This table controls and maintains a record of the sequential identification numbers used in various tables in the database, ensuring that each number is unique and assigned in order. Several of the ID numbers maintained in this table are determined by the object structure. The numbers are assigned sequentially, but are cached in chunks of 1000 numbers for optimization (so the application does not need to query the sbyn_seq_table table for each transaction). The chunk size for the EUID sequence is configurable. If the server is reset before all allocated numbers are used, the unused numbers are discarded and never used, and numbering is restarted at the beginning of the next 1000-number chunk.

The differences in data types between Oracle and SQL Server are noted in Table 17. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 17 SBYN_SEQ_TABLE Table Description

Column Name	Data Type	Description
SEQ_NAME	VARCHAR2(20) Varchar(20)	The name of the object for which the sequential ID is stored.
SEQ_COUNT	NUMBER(38) numeric(38, 0)	The current value of the sequence. The next record will be assigned the current value plus one.

The default sequence numbers are listed in Table 18.

Table 18 Default Sequence Numbers

Sequence Name	Description
EUID	The sequence number that determines how EUIDs are assigned to new records. The chunk size for the EUID sequence number is configurable in the Threshold file.
POTENTIALDUPLICATE	The sequence number assigned each potential duplicate transaction record in sbyn_potentialduplicates (column name “potentialduplicateid”).
TRANSACTIONNUMBER	The sequence number assigned to each transaction in the master index application. This number is stored in sbyn_transaction (column name “transactionnumber”).
ASSUMEDMATCH	The sequence number assigned to each assumed match transaction record in sbyn_assumedmatch (column name “assumedmatchid”).
AUDIT	The sequence number assigned to each audit log record in sbyn_audit (column name “audit_id”).
MERGE	The sequence number assigned to each merge transaction in sbyn_merge (column name “merge_id”).
SBYN_APPL	The sequence number assigned to each application listed in sbyn_appl (column name “appl_id”)
SBYN_COMMON_HEADER	The sequence number assigned to each common table data type listed in sbyn_common_header (column name “common_header_id”).
SBYN_COMMON_DETAIL	The sequence number assigned to each common table data element listed in sbyn_common_detail (column name “common_detail_id”).
`OBJECT_NAME`	Each parent and child object system record table is assigned a sequential ID. The column names are named after the object (for example, sbyn_address has a sequential column named “addressid”). The parent object ID is included in each child object table.
`OBJECT_NAME`SBR	Each parent and child object SBR table is assigned a sequential ID. The column names are named after the object (for example, sbyn_addresssbr has a sequential column named “addressid”). The parent object ID is included in each child object SBR table.

SBYN_SYSTEMOBJECT

This table stores information about the system records in the database, including their local ID and source system pairs. It also stores transactional information, such as the create or update date and function. The differences in data types between Oracle and SQL Server are noted in Table 19. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 19 SBYN_SYSTEMOBJECT Table Description

Column Name	Data Type	Description
SYSTEMCODE	VARCHAR2(20) Varchar(20)	The processing code of the system associated with the local ID. This column cannot be null.
LID	VARCHAR2(25) Varchar(25)	The local ID associated with the system and EUID (the associated EUID is found in sbyn_enterprise). This column cannot be null.
CHILDTYPE	VARCHAR2(20) Varchar(20)	The type of object being processed (currently only the name of the parent object). This column is reserved for future use.
CREATEUSER	VARCHAR2(30) Varchar(30)	The user ID of the person who created the system record.
CREATEFUNCTION	VARCHAR2(20) Varchar(20)	The type of transaction that created the system record.
CREATEDATE	DATE datetime	The date the system record was created.
UPDATEUSER	VARCHAR2(30) Varchar(30)	The user ID of the person who last updated the system record.
UPDATEFUNCTION	VARCHAR2(20) Varchar(20)	The type of transaction that last updated the system record.
UPDATEDATE	DATE datetime	The date the system record was last updated.
STATUS	VARCHAR2(15) Varchar(15)	The status of the system record. The status can be one of these values: active inactive merged

SBYN_SYSTEMS

This table stores information about each system integrated into the master index environment, including the system’s processing code and name, a brief description, the format of the local IDs, and whether any of the system information should be masked. The differences in data types between Oracle and SQL Server are noted in Table 20. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 20 SBYN_SYSTEMS Table Description

Column Name	Data Type	Description
SYSTEMCODE	VARCHAR2(20) Varchar(20)	The unique processing code of the system.
DESCRIPTION	VARCHAR2(120) Varchar(120)	A brief description of the system, or the system name. This is the value that appears in the tree view panes of the EDM for each system and local ID pair.
STATUS	CHAR(1)	The status of the system in the master index application. “A” indicates active and “D” indicates deactivated.
ID_LENGTH	NUMBER numeric(38, 0)	The length of the local identifiers assigned by the system. This length does not include any additional characters added by the input mask.
FORMAT	VARCHAR2(60) Varchar(60)	The required data pattern for the local IDs assigned by the system. For more information about possible values and using Java patterns, see “Patterns” in the class list for `java.util.regex` in the Javadocs provided with the Java™ 2 Platform, Standard Edition (J2SE™ platform). Note that the data pattern is also limited by the input mask described below. All regex patterns are supported if there is no input mask.
INPUT_MASK	VARCHAR2(60) Varchar(60)	A mask used by the EDM to add punctuation to the local ID. For example, the input mask DD-DDD-DDD inserts a hyphen after the second and fifth characters in an 8-digit ID. These character types can be used. D – Numeric character L – Alphabetic character A – Alphanumeric character
VALUE_MASK	VARCHAR2(60) Varchar(60)	A mask used to strip any extra characters that were added by the input mask for database storage. The value mask is the same as the input mask, but with an “x” in place of each punctuation mark. Using the input mask described above, the value mask is DDxDDDxDDD. This strips the hyphens before storing the ID.
CREATE_DATE	DATE datetime	The date the system information was inserted into the database.
CREATE_USERID	VARCHAR2(20) Varchar(20)	The logon ID of the user who inserted the system information into the database.
UPDATE_DATE	DATE datetime	The most recent date the system’s information was updated.
UPDATE_USERID	VARCHAR2(20) Varchar(20)	The logon ID of the user who last updated the system’s information.

SBYN_SYSTEMSBR

This table stores transactional information about the system records for the SBR, such as the create or update date and function. The sbyn_systemsbr table is indirectly linked to the sbyn_systemobjects table through sbyn_enterprise. The differences in data types between Oracle and SQL Server are noted in Table 21. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 21 SBYN_SYSTEMSBR Table Description

Column Name	Data Type	Description
EUID	VARCHAR2(20) Varchar(20)	The EUID associated with system record (the associated system and local ID are found in sbyn_enterprise). This column cannot be null.
CHILDTYPE	VARCHAR2(20) Varchar(20)	The type of object being processed (currently only the name of the parent object). This column is reserved for future use.
CREATESYSTEM	VARCHAR2(20) Varchar(20)	The system in which the system record was created.
CREATEUSER	VARCHAR2(30) Varchar(30)	The user ID of the person who created the system record.
CREATEFUNCTION	VARCHAR2(20) Varchar(20)	The type of transaction that created the system record.
CREATEDATE	DATE datetime	The date the system object was created.
UPDATEUSER	VARCHAR2(30) Varchar(30)	The user ID of the person who last updated the system record.
UPDATEFUNCTION	VARCHAR2(20) Varchar(20)	The type of transaction that last updated the system record.
UPDATEDATE	DATE datetime	The date the system object was last updated.
STATUS	VARCHAR2(15) Varchar(15)	The status of the enterprise record. The status can be one of these values: active inactive merged
REVISIONNUMBER	NUMBER(38) numeric(38, 0)	The revision number of the SBR. This is used for version control.

SBYN_TRANSACTION

This table stores a history of changes made to each record in the master index application, allowing you to view a transaction history and to undo certain actions, such as merging two object records. The differences in data types between Oracle and SQL Server are noted in Table 22. The Oracle type is on the first line and the SQL Server type is on the second. This table also includes one column that has a different name for Oracle and for SQL Server.

Table 22 SBYN_TRANSACTION Table Description

Column Name	Data Type	Description
TRANSACTIONNUMBER	VARCHAR2(20) Varchar(20)	The unique number of the transaction.
LID1	VARCHAR2(25) Varchar(25)	This column is reserved for future use.
LID2	VARCHAR2(25) Varchar(25)	The local ID of the second system record involved in the transaction.
EUID1	VARCHAR2(20) Varchar(20)	This column is reserved for future use.
EUID2	VARCHAR2(20) Varchar(20)	The EUID of the second object record involved in the transaction.
FUNCTION (Oracle) OPERATION (SQL Server)	VARCHAR2(20) Varchar(20)	The type of transaction that occurred, such as update, add, merge, and so on.
SYSTEMUSER	VARCHAR2(30) Varchar(30)	The logon ID of the user who performed the transaction.
TIMESTAMP	TIMESTAMP datetime	The date and time the transaction occurred.
DELTA	BLOB varbinary(MAX)	A list of the changes that occurred to system records as a result of the transaction.
SYSTEMCODE	VARCHAR2(20) Varchar(20)	The processing code of the source system in which the transaction originated.
LID	VARCHAR2(25) Varchar(25)	The local ID of the system record involved in the transaction.
EUID	VARCHAR2(20) Varchar(20)	The EUID of the enterprise record involved in the transaction.

SBYN_USER_CODE

This table is similar to the sbyn_common_header and sbyn_common_detail tables in that it stores processing codes and drop-down list values. This table is used when the value of one field is dependent on the value of another. For example, if you store credit card information, you could list each credit card type and specify a required format for the credit card number field. The data stored in this table includes the processing code, a brief description, and the format of the dependent fields.

The differences in data types between Oracle and SQL Server are noted in Table 23. The Oracle type is on the first line, and the SQL Server type is on the second.

Table 23 SBYN_USER_CODE Table Description

Column Name	Data Type	Description
CODE_LIST	VARCHAR2(20) Varchar(20)	The code list name of the user code type (using the credit card example above, this might be similar to “CREDCARD”). This column links the values for each list.
CODE	VARCHAR2(20) Varchar(20)	The processing code of each user code element.
DESCRIPTION	VARCHAR2(50) Varchar(50)	A brief description or name for the user code. This is the value that appears in the drop-down list.
FORMAT	VARCHAR2(60) Varchar(60)	The required data pattern for the field that is constrained by the user code. For more information about possible values and using Java patterns, see “Patterns” in the class list for `java.util.regex` in the Javadocs provided with the J2SE platform. Note that the data pattern is also limited by the input mask described below. All regex patterns are supported if there is no input mask.
INPUT_MASK	VARCHAR2(60) Varchar(60)	A mask used by the EDM to add punctuation to the constrained field. For example, the input mask DD-DDD-DDD inserts a hyphen after the second and fifth characters in an 8-digit ID. These character types can be used. D – Numeric character L – Alphabetic character A – Alphanumeric character
VALUE_MASK	VARCHAR2(60) Varchar(60)	A mask used to strip any extra characters that were added by the input mask for database storage. The value mask is the same as the input mask, but with an “x” in place of each punctuation mark. Using the input mask described above, the value mask is DDxDDDxDDD. This strips the hyphens before storing the ID.

Sample Master Index Database Model (Repository)

The diagrams on the following pages illustrate the table structure and relationships for a sample Oracle database designed for storing information about companies. The diagrams display attributes for each database column, such as the field name, data type, whether the field can be null, and primary keys. They also show directional relationships between tables and the keys by which the tables are related. This diagram is very similar to SQL Server, with the exception of a few column name changes and some different data types as noted in the tables above.

image:Diagram shows a sample database structure for a master index application (along with the following two diagrams).

image:Diagram shows a sample database structure for a master index application (along with the previous and following diagrams).

image:Diagram shows a sample database structure for a master index application (along with the previous two diagrams).

Skip Navigation Links
Exit Print View
	Understanding Oracle Java CAPS Master Index Processing (Repository) Java CAPS Documentation