Price calculation is a very complex topic in Magento. Here is a bird's eye view on how this is done for a simple product:
- Magento gets the "base" price. This is the price you have defined in the price column
- The "group" price is applied. This is the part where the tier price is taken in consideration based on the ordered quantity, website and customer group.
- The special price is applied if a special price is defined for the current date.
- The "catalog_product_get_final_price" event is fired. This is where your custom module that tinkers with the price should come in. As a working example, the CatalogRule module catches this event and changes the price based on the defined catalog rules
- The prices of the selected options is taken into consideration
Since this process is extremely time consuming, Magento creates a price index and will go through the above only when reindexing. The price index makes use of a bunch of tables that has info about the "final" price (calculated using the steps above) along with things like minimum / maximum prices for configurables and bundles. This table has an entry for each website/product/customer group combination, so the system will only need to select the proper row based on the customer data, making the whole process run in decent time.