When should I use the category flat table option?

If you have PHPMyAdmin installed on your server you can take a look at Magento's tables. You'll see that there are a lot starting with catalog_product_... and catalog_category_....

All these are used for each product and category so that means a lot of complicated and heavy queries for every page you call.

To lighten the load on MySQL and speed up your shop Magento offers flat tables. For example catalog_product_flat_1. If you take a look at the structure of this table you'll see it has a lot of the basic product data which means that, for example in the category product list, only one query has to be executed to this table instead of querying the multiple other tables containing the attribute data.

However, this data is aggregated from the data in the other tables, that means that every time you save a product several indexes (System > Configuration > Index Management) are refreshed to compile this product data. In some cases like for example while developing this might be unnecessary. You want to see all changes immediately even though the page load time could take a while.

So in conclusion, whenever you launch a shop to the public, turn them on and when programming try not to use full product or catalog objects but instead use the data provided by the flat indexes.