Data Structures for Business Directory Support

After you acquire the business directory data and invoke the appropriate procedure to load it into the database, the procedure populates the following tables, all owned by the MDSYS schema, which are used for business directory support.

In some tables, some rows have null values for some columns, because the information does not apply in this instance or because the data provider did not supply a value.

The following sections describe these tables, in alphabetical order by table name.

OPENLS_DIR_BUSINESSES Table

The OPENLS_DIR_BUSINESSES table stores information about each business (that is, each business that has an address). If the business is part of a larger business chain, the CHAIN_ID column is a foreign key to the CHAIN_ID column in the OPENLS_DIR_BUSINESS_CHAINS table (described in OPENLS_DIR_BUSINESS_CHAINS Table).

The OPENLS_DIR_BUSINESSES table contains one row for each business, and it contains the columns shown in Table: OPENLS_DIR_BUSINESSES Table.

Table: OPENLS_DIR_BUSINESSES Table

Column Name Data Type Description
BUSINESS_ID NUMBER Business ID number. (Required)
BUSINESS_NAME VARCHAR2(128) Area name. (Required)
CHAIN_ID NUMBER ID number of the business chain (in the OPENLS_BIR_BUSINESS_CHAIN table), if the business is part of a chain.
DESCRIPTION VARCHAR2(1024) Description of the business.
PHONE VARCHAR2(64) Phone number, in an appropriate format for the location.
COUNTRY VARCHAR2(64) Country code or name. (Required)
COUNTRY_SUBDIVISION VARCHAR2(128) Subdivision of the country, if applicable.
COUNTRY_SECONDARY_SUBDIVISION VARCHAR2(128) Subdivision within COUNTRY_SUBDIVISION, if applicable.
MUNICIPALITY VARCHAR2(128) Municipality name.
MUNICIPALITY_SUBDIVISION VARCHAR2(128) Subdivision within MUNICIPALITY, if applicable.
POSTAL_CODE VARCHAR2(32) Postal code (for example, 5-digit ZIP code in the United Stated and Canada). (Required)
POSTAL_CODE_EXT VARCHAR2(32) Postal code extension (for example, 4-digit extension if the 5-4 ZIP code format is used).
STREET VARCHAR2(128) Street address, including house or unit number. (Required)
INTERSECTING_STREET VARCHAR2(128) Name of the street (if any) that intersects STREET at this address.
BUILDING VARCHAR2(128) Name of the building that includes this address.
PARAMETERS XMLTYPE XML document with additional information about the business.
GEOM SDO_GEOMETRY Point geometry representing the address of the business.

OPENLS_DIR_BUSINESS_CHAINS Table

The OPENLS_DIR_BUSINESS_CHAINS table stores information about each business chain. A business chain is a business that has multiple associated businesses; for example, a restaurant chain has multiple restaurants that have the same name and offer basically the same menu. If the business is part of a business chain, the row for that business in the OPENLS_DIR_BUSINESSES table (described in OPENLS_DIR_BUSINESSES Table) contains a CHAIN_ID column value that matches a value in the CHAIN_ID column in the OPENLS_DIR_BUSINESS_CHAINS table.

The OPENLS_DIR_BUSINESS_CHAINS table contains one row for each business chain, and it contains the columns shown in Table: OPENLS_DIR_BUSINESS_CHAINS Table.

Table: OPENLS_DIR_BUSINESS_CHAINS Table

Column Name Data Type Description
CHAIN_ID NUMBER Business chain ID number. (Required)
CHAIN_NAME VARCHAR2(128) Business chain name.

OPENLS_DIR_CATEGORIES Table

The OPENLS_DIR_CATEGORIES table stores information about each category into which a business can be placed. If the data provider uses a category hierarchy, this table contains rows for categories at all levels of the hierarchy, using the PARENT_ID column to indicate the parent category of a child category. For example, a Restaurants category might be the parent of several child categories, one of which might be Chinese.

The OPENLS_DIR_CATEGORIES table contains one row for each category, and it contains the columns shown in Table: OPENLS_DIR_CATEGORIES Table.

Table: OPENLS_DIR_CATEGORIES Table

Column Name Data Type Description
CATEGORY_ID VARCHAR2(32) Category ID string. (Required)
CATEGORY_TYPE_ID NUMBER Category type ID number. Must match a value in the CATEGORY_TYPE_ID column of the OPENLS_DIR_CATEGORY_TYPES table (described in OPENLS_DIR_CATEGORY_TYPES Table). (Required)
CATEGORY_NAME VARCHAR2(128) Category name. (Required)
PARENT_ID VARCHAR2(32) CATEGORY_ID value of the parent category, if any, for this category.
PARAMETERS XMLTYPE XML document with additional information about the category.

OPENLS_DIR_CATEGORIZATIONS Table

The OPENLS_DIR_CATEGORIZATIONS table stores information about associations of businesses with categories. Each business can be in multiple categories; and the categories for a business can be independent of each other or in a parent-child relationship, or both. For example, a store that sells books and music CDs might be in the categories for Bookstores, Music, and its child category Music Stores, in which case there will be three rows for that business in this table.

The OPENLS_DIR_CATEGORIZATIONS table contains one row for each association of a business with a category, and it contains the columns shown in Table: OPENLS_DIR_CATEGORIZATIONS Table.

Table: OPENLS_DIR_CATEGORIZATIONS Table

Column Name Data Type Description
BUSINESS_ID NUMBER Business ID. Must match a value in the BUSINESS_ID column of the OPENLS_DIR_BUSNESSES table (described in OPENLS_DIR_BUSINESSES Table). (Required)
CATEGORY_ID VARCHAR2(32) Category ID string. The CATEGORY_ID and CATEGORY_TYPE_ID values must match corresponding column values in a single row in the OPENLS_DIR_CATEGORIES table (described in OPENLS_DIR_CATEGORIES Table). (Required)
CATEGORY_TYPE_ID NUMBER Category type ID number. The CATEGORY_ID and CATEGORY_TYPE_ID values must match corresponding column values in a single row in the OPENLS_DIR_CATEGORIES table (described in OPENLS_DIR_CATEGORIES Table). (Required)
CATEGORIZATION_TYPE VARCHAR2(8) EXPLICIT (the default) or IMPLICIT.
USER_SPECIFIC_CATEGORIZATION VARCHAR2(32) User-specified categorization, if any.
PARAMETERS XMLTYPE XML document with additional information about the association of the business with the category.

OPENLS_DIR_CATEGORY_TYPES Table

The OPENLS_DIR_CATEGORY_TYPES table stores information about category types. This table contains the columns shown in Table: OPENLS_DIR_CATEGORY_TYPES Table.

Table: OPENLS_DIR_CATEGORY_TYPES Table

Column Name Data Type Description
CATEGORY_TYPE_ID NUMBER Category type ID number. (Required)
CATEGORY_TYPE_NAME VARCHAR2(128) Name of the category type. (Required)
PARAMETERS XMLTYPE XML document with additional information about the category type.

OPENLS_DIR_SYNONYMS Table

The OPENLS_DIR_SYNONYMS table stores information about synonyms for categories. Synonyms can be created to expand the number of terms (strings) associated with a category, so that users get more complete and meaningful results from a search.

The OPENLS_DIR_SYNONYMS table contains one row for each synonym definition, and it contains the columns shown in Table: OPENLS_DIR_SYNONYMS Table.

Table: OPENLS_DIR_SYNONYMS Table

Column Name Data Type Description
STANDARD_NAME VARCHAR2(128) Standard name of a category, as the user might enter it.
CATEGORY VARCHAR2(128) Category name, as it appears in the OPENLS_DIR_CATEGORIES table (described in OPENLS_DIR_CATEGORIES Table).
AKA VARCHAR2(128) .Additional or alternate name for the category. (“AKA” stands for “also known as.”)