To import categories in AbleCommerce 7.x, there are three tables that you must be aware of: ac_Categories, ac_CategoryParents, and ac_CatalogNodes. All three tables must have records added when importing a category directly to the database.
This table is the most self explanatory. There is one record for each category.
- CategoryId: This is a unique ID for the category. It is an autonumber field and does not need to be set.
- StoreId: This is the ID of your store. This should almost certainly be 1, unless you have done a non-standard install.
- ParentId: This is the ID of the parent category. Set to 0 if this is a top level category.
- Name: Name of the category.
- VisibilityId: 0 = visible and publicly accessible, 1 = hidden but publicly accessible, 2 = hidden and private
This table is the most misunderstood. It exists to improve the performance of queries that otherwise would need to be recursive. There is one record for each category, plus one more for each level. Top level categories have 2 records, subcategories have 3 records, sub-subcategories have 4, and so on.
- CategoryId: This will always be the ID of the category you are creating the records for.
- ParentId: This is the ParentId for the record (one record for each parent including self)
- ParentLevel: The level of the parent being referenced. The root category (0) is level 0.
- ParentNumber: The distance between the category and the parent. The self-reference is 0, immediate parent is 1, the grandparent is 2, and so on.
Example, Category "Books" is ID 1, "Books:Fiction" is ID 2, "Books:Fiction:Mystery" is ID 3.
The records contain redundant data, but this results in more efficient queries related to catalog browsing and searching. Also be aware there is an API function Category.UpdateParent that can be used in custom import scripts to make setting the parent value easier.
This table is responsible for linking up the four types of content objects supported by our content management system: Categories, Products, Webpages, and Links. You must have one record for each category that links it to the parent category. If it is a top level category it has a record linking it to the root (ID 0) category.
- CategoryId: ID of the parent category that contains the "node"
- CatalogNodeId: ID of the category being linked (this is the node)
- CatalogNodeTypeId: 0 = category, 1 = product, 2 = webpage, 3 = link
- OrderBy: integer value used as default sort for items in the parent category