I, Business.

A common enough question, one that I myself found a little confusing to begin with. What are the differences between transaction databases and data warehouses?

In effect, a transaction database contains data that handles current operations. This data changes regularly, with the addition/cancellation of new operations (or transactions). A data warehouswe, on the other hand, is a respository for storing and analyzing numberical information - that can be updated as and when required (even regularly - hourly/daily/weekly, etc.)

Transaction databases will provide information about activities, whereas data warehouses will provide you information to plan out future activities. For example, a book store may have a transaction database to collect information about books that have been borrowed or bought. A data warehouse would consist of data that would explain the trends in borrowing books, as per duration, time of year, genre, etc.

A transaction database will consist of data for only one application, whereas a data warehouse will look to combine various transaction databases to provide a base for analysis of all the data.

While data warehouses deal with aggregate values, a transaction database will consist of specifics. Hence, DW will deal with quick retreival of aggregate data, whereas TDB will will deal with retreival of to-the-point data.