In a nutshell Magento uses an Entity-Attribute-Value (EAV) database model which gives great versatility but sucks for performance. Effectively the fields and related information are stored in one table and the values are stored in a different table. Which table the values are stored in depends on the type of value - an integer, varchar, text, etc.
By using the flat tables Magento effectively pulls data from all of these different tables and builds a single database table that represents the main fields required for your categories and your products which means queries have to access far fewer tables and therefore run faster.
However, you probably know all that.
Even if the system>configuration>catalog "user flat catalog category" is set to "no" the indexers still do the indexing creating all the contents for the catalog_category_flat_store_1 table. Whether that table gets actually used is another thing.