Product Catalog Tables

ATG Commerce uses the following tables to store product catalog information:

dcs_folder

This table contains information that describes a folder.

Column	Data Type	Constraint
`folder_id`	VARCHAR(40)	NOT NULL UNIQUE
(primary key)	The unique identifier associated with the folder.
`version`	INTEGER	NOT NULL
	Manages the optimistic locking feature of the SQL Repository. This value is automatically incremented by the SQL Repository when any value of the item is modified. If you change rows directly outside of the ATG platform, you should also increment the version number.
`creation_date`	DATE	NULL
	The date this folder was created.
`start_date`	DATE	NULL
	The date on which this folder will become available. This is an optional field that can be used by the SQL Repository as part of an RQL filter to prevent items from being loaded from the database.
`end_date`	DATE	NULL
	The last date on which this folder will be available. This is an optional field that can be used by the SQL Repository as part of an RQL filter to prevent items from being loaded from the database.
`description`	VARCHAR(254)	NULL
	A text description of the folder.
`name`	VARCHAR(254)	NOT NULL
	The name of the folder.
`path`	VARCHAR(254)	NOT NULL
	A String that represents the folder in the context of all the ancestor folders. This value is similar to the complete absolute path of a file.
`parent_folder_id`	VARCHAR(40)	NOT NULL
	The ID of the folder that contains this folder in the catalog hierarchy. References `dcs_folder(folder_id)`.

dcs_media

This table contains information that describes a media item.

Column	Data Type	Constraint
`media_id`	VARCHAR(40)	NOT NULL UNIQUE
(primary key)	The unique identifier associated with the media item.
`version`	INTEGER	NOT NULL
	Manages the optimistic locking feature of the SQL Repository. This value is automatically incremented by the SQL Repository when any value of the item is modified. If you change rows directly outside of the ATG framework, you should also increment the version number.
`creation_date`	DATE	NULL
	The date this media item was created.
`start_date`	DATE	NULL
	The date on which this media item will become available. This is an optional field that can be used by the SQL Repository as part of an RQL filter to prevent items from being loaded from the database.
`end_date`	DATE	NULL
	The last date on which this media item will be available. This is an optional field that can be used by the SQL Repository as part of an RQL filter to prevent items from being loaded from the database.
`description`	VARCHAR(254)	NULL
	A text description of the media item.
`name`	VARCHAR(254)	NOT NULL
	The name of the media item.
`path`	VARCHAR(254)	NOT NULL
	A String, which represents the folder in the context of all the ancestor folders. This value is similar to the complete absolute path of a file.
`parent_folder_id`	VARCHAR(40)	NOT NULL
	The ID of the folder that contains this media item in the catalog hierarchy. References `dcs_folder(folder_id)`
`media_type`	INTEGER	NULL
	Used as an enumerated data type that indicates what form of media is stored. By default this includes external, internal binary, and internal text media items.

dcs_media_ext

This table contains information that describes extended attributes of a media item.

Column	Data Type	Constraint
`media_id`	VARCHAR(40)	NOT NULL UNIQUE
(primary key)	The unique identifier associated with the media. References `dcs_media(media_id)`.
`url`	VARCHAR(254)	NOT NULL
	The external URL that references media content.

dcs_media_bin

This table contains information that describes the size of a media item.

Column	Data Type	Constraint
`media_id`	VARCHAR(40)	NOT NULL UNIQUE
(primary key)	The unique identifier associated with the media. References `dcs_media(media_id)`.
`length`	INTEGER	NOT NULL
	The number of bytes of data stored in the data column.
`last_modified`	DATE	NOT NULL
	The date this item was last modified.
`data`	LONG VARBINARY	NOT NULL
	The raw bytes of content.

dcs_media_txt

This table contains information about the text features of a media item.

Column	Data Type	Constraint
`media_id`	VARCHAR(40)	NOT NULL UNIQUE
(primary key)	The unique identifier associated with the media. References `dcs_media(media_id)`.
`length`	INTEGER	NOT NULL
	The number of bytes of data stored in the data column.
`last_modified`	DATE	NOT NULL
	The date this item was last modified.
`data`	LONG VARCHAR	NOT NULL
	Text content that can be indexed by a search engine.

dcs_category

This table contains information that describes a category.

Column	Data Type	Constraint
`category_id`	VARCHAR(40)	NOT NULL UNIQUE
(primary key)	The unique identifier associated with the category.
`catalog_id`	VARCHAR(40)
	The unique identifier associated with the catalog that contains this category.
`version`	INTEGER	NOT NULL
	Manages the optimistic locking feature of the SQL Repository. This value is automatically incremented by the SQL Repository when any value of the item is modified. If you change rows directly outside of the ATG framework, you should also increment the version number.
`creation_date`	DATE	NULL
	The date this category was created.
`start_date`	DATE	NULL
	The date on which this category will become available. This is an optional field that can be used by the SQL Repository as part of an RQL filter to prevent items from being loaded from the database.
`end_date`	DATE	NULL
	The last date on which this category will be available. This is an optional field that can be used by the SQL Repository as part of an RQL filter to prevent items from being loaded from the database.
`display_name`	VARCHAR(254)	NULL
	The name of the category that displays in the ACC.
`description`	VARCHAR(254)	NULL
	A text description of the category.
`long_description`	LONG VARCHAR	NULL
	A long text description of the category.
`parent_cat_id`	VARCHAR(40)	NULL
	The ID of the immediate and default parent category. References `dcs_category(category_id)`
`parent_cat_id`	VARCHAR(40)	NULL
	The ID of the immediate and default parent category.
`category_type`	INTEGER	NULL
	An enumerated value used for defining sub-types of the initial ATG Commerce category item descriptor.
`root_category`	NUMERIC(1)	NULL CHECK`(root_category in (01))`
	A boolean (1 or 0) indicator that represents which categories should be considered the root of the catalog hierarchy.

dcs_product

This table contains information that describes a product item.

Column	Data Type	Constraint
`product_id`	VARCHAR(40)	NOT NULL UNIQUE
(primary key)	The unique identifier associated with the product.
`version`	INTEGER	NOT NULL
	Manages the optimistic locking feature of the SQL Repository. This value is automatically incremented by the SQL Repository when any value of the item is modified. If you change rows directly outside of the ATG framework, you should also increment the version number.
`creation_date`	DATE	NULL
	The date this product was created.
`start_date`	DATE	NULL
	The date on which this product will become available. This is an optional field that can be used by the SQL Repository as part of an RQL filter to prevent items from being loaded from the database.
`end_date`	DATE	NULL
	The last date on which this product will be available. This is an optional field that can be used by the SQL Repository as part of an RQL filter to prevent items from being loaded from the database.
`display_name`	VARCHAR(254)	NULL
	The name of the product that displays in the ACC.
`description`	VARCHAR(254)	NULL
	A text description of the product.
`long_description`	LONG VARCHAR	NULL
	A long text description of the product.
`parent_cat_id`	VARCHAR(40)	NULL
	The ID of the immediate and default parent category. References `dcs_category(category_id)`
`parent_cat_id`	VARCHAR(40)	NULL
	The ID of the immediate and default parent category.
`product_type`	INTEGER	NULL
	An enumerated value used for defining sub-types of the initial ATG Commerce product item descriptor.

dcs_product_acl

The table stores security information for each product repository item.

Column	Data Type	Constraint
`product_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the product.
`item_acl`	LONG VARCHAR	NULL
	Stores the actual access control list. The access control list defines who can read, edit and delete an item.

dcs_sku

This table contains information that describes a SKU item.

Column	Data Type	Constraint
`sku_id`	VARCHAR(40)	NOT NULL UNIQUE
(primary key)	The unique identifier associated with the SKU.
`version`	INTEGER	NOT NULL
	Manages the optimistic locking feature of the SQL Repository. This value is automatically incremented by the SQL Repository when any value of the item is modified. If you change rows directly outside of the ATG framework, you should also increment the version number.
`creation_date`	DATE	NULL
	The date this SKU was created.
`start_date`	DATE	NULL
	The date on which this SKU will become available. This optional field can be used by the SQL Repository as part of an RQL filter to prevent items from being loaded from the database.
`end_date`	DATE	NULL
	The last date on which this SKU will be available. This is an optional field that can be used by the SQL Repository as part of an RQL filter to prevent items from being loaded from the database.
`display_name`	VARCHAR(254)	NULL
	The name of the SKU that displays in the ACC.
`description`	VARCHAR(254)	NULL
	A text description of the SKU.
`sku_type`	INTEGER	NULL
	An enumerated value used for defining sub-types of the initial ATG Commerce SKU item descriptor.
`wholesale_price`	DOUBLE PRECISION	NULL
	The wholesale price of the SKU.
`list_price`	DOUBLE PRECISION	NULL
	The list price of the SKU.
`sale_price`	DOUBLE PRECISION	NULL
	The sale price of the SKU.
`on_sale`	NUMERIC(1)	NULL CHECK`(on_sale in (01))`
	Determines whether the SKU is on sale.
`tax_status`	INTEGER	NULL
	An optional field that may be used to determine the taxable status of the SKU.
`fulfiller`	INTEGER	NULL
	An enumerated value that indicates which ATG Commerce fulfiller should attempt to process the SKU in the submitted order.

dcs_cat_groups

This table contains information about groups in a category.

Column	Data Type	Constraint
`category_id`	VARCHAR(40)	NOT NULL UNIQUE
(primary key)	The unique identifier associated with the category. References `dcs_category(category_id)`
`child_prd_group`	VARCHAR(254)	NULL
	Stores the name of a Content Group that should return a list of products that should be children of this category.
`child_cat_group`	VARCHAR(254)	NULL
	Stores the name of a Content Group that should return a list of categories that should be children of this category.
`related_cat_group`	VARCHAR(254)	NULL
	Stores the name of a Content Group that should return a list of categories that are related to this category.

dcs_cat_chldprd

This table contains information about child products within a category.

Column	Data Type	Constraint
`category_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the category. References `dcs_category(category_id)`
`sequence_num`	INTEGER	NOT NULL
(primary key)	Used to order rows in the table.
`child_prd_id`	VARCHAR(40)	NOT NULL
	A product ID that should be considered a child of this category. References `dcs_product(product_id)`

dcs_cat_chldcat

This table contains information about children of categories.

Column	Data Type	Constraint
`category_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the category. References `dcs_category(category_id)`
`sequence_num`	INTEGER	NOT NULL
(primary key)	Used to order rows in this table.
`child_cat_id`	VARCHAR(40)	NOT NULL
	A category ID that should be considered a child of this category. References `dcs_category(category_id)`

dcs_cat_ancestors

This table contains information about category ancestors.

Column	Data Type	Constraint
`category_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the category.
`anc_cat_id`	VARCHAR(40)	NOT NULL
(primary key)	The ID of a category that is an ancestor in the category. A category has multiple rows in this table representing all the ancestors of a category. A query of this value determines whether a category is a child of another category.

dcs_cat_rltdcat

This table contains information about category relationships.

Column	Data Type	Constraint
`category_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the category.
`sequence_num`	INTEGER	NOT NULL
(primary key)	Used to order rows in this table.
`related_cat_id`	VARCHAR(40)	NOT NULL
	A category ID that should be considered related to the category. References `dcs_category(category_id)`

dcs_cat_keywrds

This table contains information about category keywords.

Column	Data Type	Constraint
`category_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the category. References `dcs_category(category_id)`
`sequence_num`	INTEGER	NOT NULL
(primary key)	Used to order rows in this table.
`Keyword`	VARCHAR(254)	NOT NULL
	A String value used in searches.

dcs_cat_media

This table contains information about media in a category.

Column	Data Type	Constraint
`category_id`	VARCHAR(40)	NOT NULL UNIQUE
(primary key)	The unique identifier associated with the category. References `dcs_category(category_id)`
`template_id`	VARCHAR(40)	NULL
	The ID of a media item that represents the template that renders the category. References `dcs_media(media_id)`
`thumbnail_image_id`	VARCHAR(40)	NULL References `dcs_media(media_id)`
	The ID of a media item that represents a thumbnail image of a category that can be displayed in the template.
`small_image_id`	VARCHAR(40)	NULL
	The ID of a media item that represents a small image of a category that can be displayed in the template. References `dcs_media(media_id)`
`large_image_id`	VARCHAR(40)	NULL
	The ID of a media item that represents a large image of a category that can be displayed in the template. References `dcs_media(media_id)`

dcs_cat_aux_media

This table contains information about an auxiliary media image associated with a category.

Column	Data Type	Constraint
`category_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the category. References `dcs_category(category_id)`
`tag`	VARCHAR(42)	NOT NULL
(primary key)	Represents the key.
`media_id`	VARCHAR(40)	NOT NULL
	The value that points to a media item. References `dcs_media(media_id)`

dcs_prd_keywrds

This table contains information about product keywords.

Column	Data Type	Constraint
`product_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the product. References `dcs_product(product_id)`
`sequence_num`	INTEGER	NOT NULL
(primary key)	Used to order rows in this table.
`keyword`	VARCHAR(254)	NOT NULL
	A String value used in searches.

dcs_prd_media

This table contains information about product media items.

Column	Data Type	Constraint
`product_id`	VARCHAR(40)	NOT NULL UNIQUE
(primary key)	The unique identifier associated with the product. References `dcs_product(product_id)`
`template_id`	VARCHAR(40)	NULL
	The ID of a media item that represents the template that renders the product. References `dcs_media(media_id)`
`thumbnail_image_id`	VARCHAR(40)	NULL
	The ID of a media item that represents a thumbnail image of a product that can be displayed in the template. References `dcs_media(media_id)`
`small_image_id`	VARCHAR(40)	NULL
	The ID of a media item that represents a small image of a category that can be displayed in the template. References `dcs_media(media_id)`
`large_image_id`	VARCHAR(40)	NULL
	The ID of a media item that represents a large image of a category that can be displayed in the template. References `dcs_media(media_id)`

dcs_prd_aux_media

This table contains information about an auxiliary media image associated with a product.

Column	Data Type	Constraint
`product_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the product. References `dcs_product(product_id)`
`tag`	VARCHAR(42)	NOT NULL
(primary key)	Represents the key.
`media_id`	VARCHAR(40)	NOT NULL
	The value that points to a media item. References `dcs_media(media_id)`

dcs_prd_chldsku

This table contains information about children of products.

Column	Data Type	Constraint
`product_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the product. References `dcs_product(product_id)`
`sequence_num`	INTEGER	NOT NULL
(primary key)	Used to order rows in the table.
`sku_id`	VARCHAR(40)	NOT NULL
	The ID of a SKU that is a child of this product. References `dcs_sku(sku_id)`

dcs_prd_skuattr

This table contains information about which attributes of a SKU should be displayed.

Column	Data Type	Constraint
`product_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the product. References `dcs_product(product_id)`
`sequence_num`	INTEGER	NOT NULL
(primary key)	Used to order rows in the table.
`attribute_name`	VARCHAR(40)	NOT NULL
	The name of a SKU property that should be used to display relevant information about a SKU. For example not all the attributes of a SKU need to be displayed, perhaps only color, size, and display-name are necessary.

dcs_prd_groups

This table contains information about related product groups.

Column	Data Type	Constraint
`product_id`	VARCHAR(40)	NOT NULL UNIQUE
(primary key)	The unique identifier associated with the product. References `dcs_product(product_id)`
`related_prd_group`	VARCHAR(254)	NULL
	Stores the name of a Content Group that should return a list of products that are related to this product.

dcs_prd_rltdprd

This table contains information about related products.

Column	Data Type	Constraint
`product_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the product. References `dcs_product(product_id)`
`sequence_num`	INTEGER	NOT NULL
	Used to order rows in the table.
`related_prd_id`	VARCHAR(40)	NOT NULL
	A product ID that should be considered related to the product. References `dcs_product(product_id)`

dcs_prd_ancestors

Column	Data Type	Constraint
`product_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the product.
`anc_cat_id`	VARCHAR(40)	NOT NULL
(primary key)	The ID of a category that is an ancestor of the product. A product has multiple rows in this table representing all the ancestors of the product. . A query of this value determines whether a product is a child of another category.

dcs_sku_attr

This table holds information about an attribute map associated with a SKU.

Column	Data Type	Constraint
`sku_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the SKU. References `dcs_sku(sku_id)`
`attribute_name`	VARCHAR(42)	NOT NULL
(primary key)	Represents the key.
`attribute_value`	VARCHAR(254)	NOT NULL
	The value that allows arbitrary name property values to be associated with a SKU.

dcs_sku_link

This table describes SKU links, which are used to represent SKU bundles.

Column	Data Type	Constraint
`sku_link_id`	VARCHAR(40)	NOT NULL UNIQUE
(primary key)	The unique identifier of the SKU link, which represents a bundle of SKUs.
`version`	INTEGER	NOT NULL
	Manages the optimistic locking feature of the SQL Repository. This value is automatically incremented by the SQL Repository when any value of the item is modified. If you change rows directly outside of the ATG framework, you should also increment the version number.
`creation_date`	DATE	NULL
	The date this SKU was created.
`start_date`	DATE	NULL
	The date on which this SKU will become available. This is an optional field that can be used by the SQL Repository as part of an RQL filter to prevent items from being loaded from the database.
`end_date`	DATE	NULL
	The last date on which this SKU will become available. This is an optional field that can be used by the SQL Repository as part of an RQL filter to prevent items from being loaded from the database.
`display_name`	VARCHAR(254)	NULL
	The name of the SKU link that displays in the ACC.
`description`	VARCHAR(254)	NULL
	A text description of the SKU.
`quantity`	INTEGER	NOT NULL
	The number of items to include in the bundle.
`bundle_item`	VARCHAR(40)	NOT NULL
	The specific SKU to include in the bundle. References `dcs_sku(sku_id)`

dcs_sku_bndllnk

This table contains information that associated SKU links with SKU objects.

Column	Data Type	Constraint
`sku_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the SKU. References `dcs_sku(sku_id)`
`sequence_num`	INTEGER	NOT NULL
(primary key)	Used to order rows in the table.
`sku_link_id`	VARCHAR(40)	NOT NULL
	The ID of the SKU link that should be included in the SKU bundle. References `dcs_sku_link(sku_link_id)`

dcs_sku_media

This table contains information about SKU media items.

Column	Data Type	Constraint
`sku_id`	VARCHAR(40)	NOT NULL UNIQUE
(primary key)	The unique identifier associated with the SKU. References `dcs_sku(sku_id)`
`template_id`	VARCHAR(40)	NULL
	The ID of a SKU that represents the template that renders the category. References `dcs_media(media_id)`
`thumbnail_image_id`	VARCHAR(40)	NULL
	The ID of a media item that represents a thumbnail image of a category that can be displayed in the template. References `dcs_media(media_id)`
`small_image_id`	VARCHAR(40)	NULL
	The ID of a media item that represents a small image of a product that can be displayed in the template. References `dcs_media(media_id)`
`large_image_id`	VARCHAR(40)	NULL
	The ID of a media item that represents a large image of a SKU that can be displayed in the template. References `dcs_media(media_id)`

dcs_sku_aux_media

This table contains information about an auxiliary media image associated with a SKU.

Column	Data Type	Constraint
`sku_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the SKU. References `dcs_sku(sku_id)`
`tag`	VARCHAR(42)	NOT NULL
(primary key)	Represents the key.
`media_id`	VARCHAR(40)	NOT NULL
	The value that points to a SKU. References `dcs_media(media_id)`

dcs_sku_replace

This table contains information about replacing SKUs.

Column	Data Type	Constraint
`sku_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique identifier associated with the SKU. References `dcs_sku(sku_id)`
`sequence_num`	INTEGER	NOT NULL
(primary key)	Used to order the rows in the table.
`replacement`	VARCHAR(40)	NOT NULL
	The ID of a SKU that should be used as a replacement for another SKU if it is not available for purchase.

dcs_sku_conf

The following table contains information related to configurable SKUs.

Column	Data Type	Constraint
`sku_id`	VARCHAR(40)	NOT NULL
(primary key)	References `dcs_sku(sku_id)`.
`config_props`	VARCHAR(40)	NOT NULL
	The configurable properties associated with this configurable SKU.
`sequence_num`	INTEGER	NOT NULL
(primary key)	The sequence number of the configurable properties in a list.

dcs_config_prop

The following table contains information related to configurable properties.

Column	Data Type	Constraint
`config_prop_id`	VARCHAR(40)	NOT NULL
(primary key)	The repository configurable property ID.
`version`	INTEGER	NOT NULL
	The repository version number.
`display_name`	VARCHAR(40)	NULL
	The display name of the configurable property.
`description`	VARCHAR(255)	NULL
	A description of the configurable property.
`item_acl`	LONG VARCHAR	NULL
	The item access control list for this item.

dcs_conf_options

The following table contains information related to configuration options.

Column	Data Type	Constraint
`config_prop_id`	VARCHAR(40)	NOT NULL
(primary key)	References `dcs_config_prop(config_prop_id)`
`config_options`	VARCHAR(40)	NOT NULL
	The configuration options associated with the configurable property.
`sequence_num`	INTEGER	NOT NULL
(primary key)	The sequence number of the configurable option in a list.

dcs_config_opt

The following table contains information related to configuration options.

Column	Data Type	Constraint
`config_opt_id`	VARCHAR(40)	NOT NULL
(primary key)	The repository configuration option ID.
`Version`	INTEGER	NOT NULL
	The repository version number.
`display_name`	VARCHAR(40)	NULL
	The configuration option display name.
`description`	VARCHAR(255)	NULL
	The configuration option description.
`sku`	VARCHAR(40)	NULL
	The configuration option SKU.
`product`	VARCHAR(40)	NULL
	The configuration option product.
`price`	DOUBLE PRECISION	NULL
	The configuration option price.
`item_acl`	LONG VARCHAR	NULL
	The item access control list for this item.

dcs_foreign_cat

The following table contains information related to a foreign catalog (remote catalog) that ATG Commerce would integrate with to support configurable commerce items.

Column	Data Type	Constraint
`catalog_id`	VARCHAR(40)	NOT NULL
(primary key)	The unique repository ID of the catalog.
`type`	INTEGER	NOT NULL
	The type field is used for sub-typing this catalog.
`version`	INTEGER	NOT NULL
	Version property that is used internally by the Repository to maintain data consistency.
`name`	VARCHAR(100)	NULL
	The name for this catalog that will appear in the ACC.
`description`	VARCHAR(255)	NULL
	A description of this catalog that is appropriate in a UI context.
`host`	VARCHAR(100)	NULL
	The host that this foreign catalog lives at.
`port`	INTEGER	NULL
	The port that this catalog can be located on at the host.
`base_url`	VARCHAR(255)	NULL
	The base URL to locate this catalog, at a given host.
`return_url`	VARCHAR(255)	NULL
	The URL that can be used for return.
`item_acl`	LONG VARCHAR	NULL
	Maintains an ACL for security information purposes on instances of this item-descriptor.

ATG Commerce Programming Guide