The Catalog, Customer and Sales modules are using Entity-Attribute-Value model to store the data (http://en.wikipedia.org/wiki/Entity-Attribute-Value_model)
It allows great flexibility that Magento uses for product attributes and future customizations with categories, customers and orders.
All the entity types are stored in `eav_entity_type` table.
All the attributes are in `eav_attribute` table.
These and few other tables define the data structure for these module’s entities.
Entity data is stored in header file and at least 5 additional tables that store the data by the type.
For example products:
`catalog_product_entity`: entity header table with system fields (all system attributes probably will migrate here)
`catalog_product_entity_datetime`: table containing datetime values of products
`catalog_product_entity_decimal`, `catalog_product_entity_int`, `catalog_product_entity_varchar`, `catalog_product_entity_text`: same as above, other data types
datetime, decimal, int and varchar values are indexed to be quickly found.
In same tables few entity types can be stored. For example in `sales_quote_entity*` tables are storing information about quotes, items, addresses, payments.
There is Mage_Eav module that provides an interface which allows to use these db structures without overhead in object models.
That means, if someone likes static database structure they will be able to create their own resource models that will utilize native database tables.
But even this is not needed because it is possible to move all the entity attributes into header table and then it will be equivalent to regular table usage without changing line of code.