Magento Database – Category Tables in magento

Category Creation in magento
Once category is created from magento admin, then the created category information will be reflect or insert  into following tables in magento
1. catalog_category_entity
2. catalog_category_entity_datetime
3. catalog_category_entity_decimal
4. catalog_category_entity_int
5. catalog_category_entity_text
6. catalog_category_entity_varchar
7. catalog_category_flat

Table 1. catalog_category_entity:
This table is act as a main table of creating the category in magento.If you check with table you come to know the category details,will see one by one column in this table
entity_type_id -> This ID is explain about the type Of entity, which means here type of entity is called “catalog_category”. (You can get value from eav_entity_type. Here 3 is called ‘category’)
attribute_set_id -> Category is mapped with which attribute set ? Here i used attribute called “Jutedefault” instead of “default” (‘default’ is magento default attribute set).
parent_id -> This is Parent Category ID.Ex, if you are creating any category, that it may mapped with any of the parent category (except root category). That parent category ID is mapped here.
Path -> Path is, tree of category structure. One category have lot of child category, and also mapped with more than category also.That category tree structure will get it from this value.
Position -> Category position
level -> Which level this is? like 1st level (or) 2nd level (or) 3 level (or) etc…
children_count -> The category have how many child categories ? like child categories count.

Table 2 to 5:
2. catalog_category_entity_datetime
3. catalog_category_entity_decimal
4. catalog_category_entity_int
5. catalog_category_entity_text
6. catalog_category_entity_varchar

The above tables are having the values of created category. While creating the category, you may fill some detail in admin section, like category isactive or not,URL key of category , description, image path, meta keyword details, display settings, if any custom design is available or not, its based on the data type, the values will go to save into the above tables.The category have more than one attribute may present. Those attribute values are stored based on the data type of this tables.
Ex: catalog_category_entity_DATATYPE

Table :7
catalog_category_flat, this table is based on the store and based on the admin configuration. It will create.
Admin -> System -> Config -> Catalog -> Frontend ->  Use Flat Catalog Category is YES.
If Use Flat Catalog Category is Yes then only use of this table else no use.

Once admin is create a category from admin section, the created category values/category attribute values are based on the datatype it store into Table 1 to 6 tables.So in the frontend when customer see on the category, it should refere this 6 tables and fetch those values and display from frotned. Instead of this referring 6 tables, magento will refer only one table.Will get almost all the detail about category from this table (Ie: catalog_category_flat).
If Use Flat Catalog Category option is yes, then what magento will do means, it will flat or dump all the values to this table. (all the attribute value about category will store in this table) when re-indexing is done.
Instead of reffering (or) writing Join of 6 table mysql query will use only table will fetch all the detail will helpful to increase the perfomance of website. Its helpful to reduce the Mysql Queries when customer checking on the frontend.
Based on the store this table count also will increase.
Ex: If am maintaing more than one store then the table format is like below
1. catalog_category_flat_store1
2. catalog_category_flat_store2
3. etc…