Database Table Details
The tables on this and the following pages describe each column in
the default database tables. The columns are identical for Oracle and SQL Server
databases, but the data types differ in some cases. Table 2 lists the
data type differences.
Table 2 Oracle and SQL Server Data Type Differences
|
|
|
BLOB |
Varbinary(MAX) |
blob |
DATE |
DateTime |
datetime |
INTEGER |
Int |
integer |
LONG |
Varchar(MAX) |
mediumtext |
NUMBER |
Numeric |
decimal |
TIMESTAMP |
DateTime |
datetime |
VARCHAR2 |
Varchar |
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 object.xml. Columns to store standardized or
phonetic versions of certain fields are automatically added when you specify certain match
types in the wizard.
The following table lists Oracle data types. For information about the differences in
data types between database vendors, see Table 3.
Table 3 SBYN_OBJECT_NAME Table Description
|
|
|
|
VARCHAR2(20) |
The system
code for the system record. |
|
VARCHAR2(25) |
A local identification code assigned by the specified
system. |
OBJECT_NAMEID |
VARCHAR2(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”. |
|
SBYN_OBJECT_NAMESBR
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 object.xml. 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 following table lists Oracle data types. For information about the differences in
data types between database vendors, see Table 3.
Table 4 SBYN_OBJECT_NAMESBR Table Description
|
|
|
|
VARCHAR2(20) |
The enterprise
unique identifier assigned by the master index application. |
OBJECT_NAMEID |
VARCHAR2(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”. |
|
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 definition file,
including any standardized or phonetic fields.
The following table lists Oracle data types. For information about the differences in
data types between database vendors, see Table 3.
Table 5 SBYN_CHILD_OBJECT and SBYN_CHILD_OBJECTSBR Table Description
|
|
|
OBJECT_NAMEID |
VARCHAR2(20) |
The unique
ID for the parent object associated with the child object in the system
record. |
CHILD_OBJECTID |
VARCHAR2(20) |
The unique ID for each record in the table. This column cannot be
null. |
|
SBYN_CHILD_OBJECTSBR
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 ojbect.xml, including any standardized or phonetic fields.
The following table lists Oracle data types. For information about the differences in
data types between database vendors, see Table 3.
Table 6 SBYN_CHILD_OBJECT and SBYN_CHILD_OBJECTSBR Table Description
|
|
|
OBJECT_NAMEID |
VARCHAR2(20) |
The unique
ID for the parent object associated with the child object in the SBR. |
CHILD_OBJECTID |
VARCHAR2(20) |
The
unique ID for each record in the table. This column cannot be null. |
|
SBYN_APPL
This table stores information about the applications used in the master index system. Currently,
there is only one entry, “eView”. The following table lists Oracle data types.
For information about the differences in data types between database vendors, see Table 3.
Table 7 SBYN_APPL Table Description
|
|
|
|
NUMBER(10) |
The unique sequence number code for the listed application. |
|
VARCHAR2(8) |
A unique
code for the application. |
|
VARCHAR2(30) |
A brief description of the application. |
|
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. |
|
DATE |
The date the application entry was created. |
|
VARCHAR2(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; it is
recommended that the table be archived periodically.
The following table lists Oracle data types. For information about the differences in
data types between database vendors, see Table 3.
Table 8 SBYN_ASSUMEDMATCH Table Description
|
|
|
|
VARCHAR2(20) |
The unique ID
for the assumed match transaction. |
|
VARCHAR2(20) |
The EUID into which the incoming record was
merged. |
|
VARCHAR2(20) |
The system code for the source system (that is, the system from which
the incoming record originated). |
|
VARCHAR2(25) |
The local ID of the record in the source
system. |
|
VARCHAR2(20) |
The matching weight between the incoming record and the EUID record into which
it was merged. |
|
VARCHAR2(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 MIDM. This includes each time a
record appears on a search results page, a comparison page, the Record Details
page, and so on. This log is only maintained if the MIDM is
configured for it. This table can grow very large over time and might
require periodic archiving.
The following table lists Oracle data types. For information about the differences in
data types between database vendors, see Table 3.
Table 9 SBYN_AUDIT Table Description
|
|
|
|
VARCHAR2(20) |
The unique identification
code for the audit record. This column cannot be null. |
|
VARCHAR2(20) |
The name of the
parent object as defined in object.xml. |
|
VARCHAR2(15) |
The EUID whose information was accessed during
an MIDM transaction. |
|
VARCHAR2(15) |
The second EUID whose information was accessed during an MIDM transaction.
A second EUID appears when viewing information about merge and unmerge transactions, comparisons,
and so on. |
FUNCTION (Oracle) OPERATION (SQL Server) |
VARCHAR2(32) |
The type of transaction that caused the audit record
to be written. This column cannot be null. |
|
VARCHAR2(120) |
A brief description of the transaction
that caused the audit record to be written. |
|
DATE |
The date the transaction that created
the audit record was performed. This column cannot be null. |
|
VARCHAR2(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 description 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 following table lists Oracle data types. For information about the differences in
data types between database vendors, see Table 3.
Table 10 SBYN_COMMON_DETAIL Table Description
|
|
|
|
NUMBER(10) |
The unique identification
code of the common table data element. |
|
NUMBER(10) |
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). |
|
VARCHAR2(20) |
The processing code for the common table data
element. |
|
VARCHAR2(50) |
A description of the common table data element. |
|
CHAR(1) |
An indicator of whether the
common table data element can be modified. |
|
DATE |
The date the data element record was
created. |
|
VARCHAR2(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 following table lists Oracle data types. For information about the differences in
data types between database vendors, see Table 3.
Table 11 SBYN_COMMON_HEADER Table Description
|
|
|
|
VARCHAR2(10) |
The unique identification
code of the common table data type. |
|
VARCHAR2(10) |
The application ID from sbyn_appl that
corresponds to the application for which the common table data type is used. |
|
VARCHAR2(8) |
A
unique processing code for the common table data type. |
|
VARCHAR2(50) |
A description of the
common table data type. |
|
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). |
|
NUMBER(10) |
The
maximum number of characters allowed in the code column for the common table
data type. |
|
VARCHAR2(3) |
This column is not currently used. |
|
DATE |
The date the common table data
type record was created. |
|
VARCHAR2(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 following table lists Oracle data types. For information about the differences in
data types between database vendors, see Table 3.
Table 12 SBYN_ENTERPRISE Table Description
|
|
|
|
VARCHAR2(20) |
The processing code
of the system associated with the local ID. |
|
VARCHAR2(25) |
The local ID associated with the
system and EUID. |
|
VARCHAR2(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 MIDM and from external systems. It also records
any unmerges that occur. The following table lists Oracle data types. For information
about the differences in data types between database vendors, see Table 3.
Table 13 SBYN_MERGE Table Description
|
|
|
|
VARCHAR2(20) |
The unique, sequential identification code of merge record. This column cannot be null. |
|
VARCHAR2(20) |
The
EUID of the record that was retained after the merge transaction. This column
cannot be null. |
|
VARCHAR2(20) |
The EUID of the record that was not retained after
the merge transaction. |
|
VARCHAR2(20) |
The transaction number associated with the merge transaction. This column cannot be
null. |
|
VARCHAR2(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 following table lists Oracle
data types. For information about the differences in data types between database vendors,
see Table 3.
Table 14 SBYN_OVERWRITE Table Description
|
|
|
|
VARCHAR2(20) |
The EUID of an SBR containing fields for
which the overwrite lock is set. |
|
VARCHAR2(200) |
The ePath to a field that is locked
in an SBR from the MIDM. |
|
VARCHAR2(20) |
The data type of a field that
is locked in an SBR. |
|
NUMBER(38) |
The data that is locked for overwrite in
an integer field. |
|
NUMBER(38) |
The data that is locked for overwrite in a boolean
field. |
|
VARCHAR2(200) |
The data that is locked for overwrite in a string field. |
|
CHAR(2) |
The data
that is locked for overwrite in a byte field. |
|
LONG |
The data that is
locked for overwrite in a long integer field. |
|
DATE |
The data that is locked
for overwrite in a date field. |
|
NUMBER(38,4) |
The data that is locked for overwrite
in a floating decimal field. |
|
DATE |
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 following table lists Oracle data types. For
information about the differences in data types between database vendors, see Table 3.
Table 15 SBYN_POTENTIALDUPLICATES Table Description
|
|
|
|
VARCHAR2(20) |
The unique identification number of the potential duplicate transaction. |
|
VARCHAR2(20) |
The matching weight of
the potential duplicate pair. |
|
VARCHAR2(15) |
This column is reserved for future use. |
|
VARCHAR2(120) |
A description of
what caused the potential duplicate flag. |
|
VARCHAR2(15) |
The status of the potential duplicate pair.
The possible values are:
U – Unresolved
R – Resolved
A – Resolved permanently
|
|
VARCHAR2(15) |
This column is reserved for future use. |
|
VARCHAR2(30) |
The user ID of
the person who resolved the potential duplicate status. |
|
DATE |
The date the potential duplicate status
was resolved. |
|
VARCHAR2(120) |
Comments regarding the resolution of the duplicate status. This is not currently
used. |
|
VARCHAR2(20) |
The EUID of the second record in the potential duplicate pair. |
|
VARCHAR2(20) |
The transaction
number associated with the transaction that produced the potential duplicate flag. |
|
VARCHAR2(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 Repository 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 following table lists Oracle data types. For information about the differences in
data types between database vendors, see Table 3.
Table 16 SBYN_SEQ_TABLE Table Description
|
|
|
|
VARCHAR2(20) |
The name of
the object for which the sequential ID is stored. |
|
NUMBER(38) |
The current value of
the sequence. The next record will be assigned the current value plus one. |
|
The default sequence numbers are listed in the following table.
Table 17 Default Sequence Numbers
|
|
|
The sequence
number that determines how EUIDs are assigned to new records. The chunk size
for the EUID sequence number is configurable in the master.xml file. |
|
The sequence number
assigned each potential duplicate transaction record in sbyn_potentialduplicates (column name “potentialduplicateid”). |
|
The sequence number
assigned to each transaction in the master index application. This number is stored
in sbyn_transaction (column name “transactionnumber”). |
|
The sequence number assigned to each assumed match transaction record
in sbyn_assumedmatch (column name “assumedmatchid”). |
|
The sequence number assigned to each audit log
record in sbyn_audit (column name “audit_id”). |
|
The sequence number assigned to each merge transaction
in sbyn_merge (column name “merge_id”). |
|
The sequence number assigned to each application listed in
sbyn_appl (column name “appl_id”) |
|
The sequence number assigned to each common table data type listed
in sbyn_common_header (column name “common_header_id”). |
|
The sequence number assigned to each common table
data element listed in sbyn_common_detail (column name “common_detail_id”). |
|
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. |
|
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 following table lists Oracle data types.
For information about the differences in data types between database vendors, see Table 3.
Table 18 SBYN_SYSTEMOBJECT Table Description
|
|
|
|
VARCHAR2(20) |
The processing code of the system associated with the local ID.
This column cannot be null. |
|
VARCHAR2(25) |
The local ID associated with the system and
EUID (the associated EUID is found in sbyn_enterprise). This column cannot be null. |
|
VARCHAR2(20) |
The
type of object being processed (currently only the name of the parent object).
This column is reserved for future use. |
|
VARCHAR2(30) |
The user ID of the person
who created the system record. |
|
VARCHAR2(20) |
The type of transaction that created the system
record. |
|
DATE |
The date the system record was created. |
|
VARCHAR2(30) |
The user ID of the person
who last updated the system record. |
|
VARCHAR2(20) |
The type of transaction that last updated
the system record. |
|
DATE |
The date the system record was last updated. |
|
VARCHAR2(15) |
The status of
the system record. The status can be one of these values:
|
|
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
following table lists Oracle data types. For information about the differences in data
types between database vendors, see Table 3.
Table 19 SBYN_SYSTEMS Table Description
|
|
|
|
VARCHAR2(20) |
The unique processing code of
the system. |
|
VARCHAR2(120) |
A brief description of the system, or the system name. This is
the value that appears in the tree view panes of the MIDM
for each system and local ID pair. |
|
CHAR(1) |
The status of the system in the
master index application. “A” indicates active and “D” indicates deactivated. |
|
NUMBER |
The length of the
local identifiers assigned by the system. This length does not include any additional
characters added by the input mask. |
|
VARCHAR2(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. |
|
VARCHAR2(60) |
A mask used by the MIDM
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.
|
|
VARCHAR2(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. |
|
DATE |
The date the system
information was inserted into the database. |
|
VARCHAR2(20) |
The logon ID of the user who
inserted the system information into the database. |
|
DATE |
The most recent date the system’s information
was updated. |
|
VARCHAR2(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 following table lists Oracle data types.
For information about the differences in data types between database vendors, see Table 3.
Table 20 SBYN_SYSTEMSBR Table Description
|
|
|
|
VARCHAR2(20) |
The EUID associated with system record (the associated system and local ID
are found in sbyn_enterprise). This column cannot be null. |
|
VARCHAR2(20) |
The type of object being
processed (currently only the name of the parent object). This column is reserved
for future use. |
|
VARCHAR2(20) |
The system in which the system record was created. |
|
VARCHAR2(30) |
The user
ID of the person who created the system record. |
|
VARCHAR2(20) |
The type of transaction
that created the system record. |
|
DATE |
The date the system object was created. |
|
VARCHAR2(30) |
The user
ID of the person who last updated the system record. |
|
VARCHAR2(20) |
The type of
transaction that last updated the system record. |
|
DATE |
The date the system object was
last updated. |
|
VARCHAR2(15) |
The status of the enterprise record. The status can be one
of these values:
|
|
NUMBER(38) |
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 profiles. The following table lists Oracle data
types. For information about the differences in data types between database vendors, see
Table 3.
Table 21 SBYN_TRANSACTION Table Description
|
|
|
|
VARCHAR2(20) |
The unique number of the transaction. |
|
VARCHAR2(25) |
This column is reserved
for future use. |
|
VARCHAR2(25) |
The local ID of the second system record involved in
the transaction. |
|
VARCHAR2(20) |
This column is reserved for future use. |
|
VARCHAR2(20) |
The EUID of the second
object profile involved in the transaction. |
FUNCTION (Oracle) OPERATION (SQL Server) |
VARCHAR2(20) |
The type of transaction
that occurred, such as update, add, merge, and so on. |
|
VARCHAR2(30) |
The logon ID of
the user who performed the transaction. |
|
TIMESTAMP |
The date and time the transaction occurred. |
|
BLOB |
A
list of the changes that occurred to system records as a result of
the transaction. |
|
VARCHAR2(20) |
The processing code of the source system in which the transaction originated. |
|
VARCHAR2(25) |
The
local ID of the system record involved in the transaction. |
|
VARCHAR2(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 following table lists Oracle data types. For information about the differences in
data types between database vendors, see Table 3.
Table 22 SBYN_USER_CODE Table Description
|
|
|
|
VARCHAR2(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. |
|
VARCHAR2(20) |
The
processing code of each user code element. |
|
VARCHAR2(50) |
A brief description or name for
the user code. This is the value that appears in the drop-down list. |
|
VARCHAR2(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. |
|
VARCHAR2(60) |
A mask
used by the MIDM 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.
|
|
VARCHAR2(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. |
|