Would you design a data warehouse as a fully normalized database with the referential integrity
and other business rules enforced through the DBMS or as a collection of tables with an application
logic taking care (as much as an application can do it) of the business rules?
The question of referential integrity (RI) is not a question at all. I feel that you always need
to ensure that RI is maintained. RI is critical to successful and repeatable reporting. So I would
encourage you to use the RI in the database. As for business rules I would usually implement these
in the application that will load the warehouse. I don't generally build business rules into the
warehouse. I find that they change too often and at times are too complicated and time-consuming to
include within the warehouse. This logic is contained in your loading routines.
This was first published in October 2004