I'm posting a link here because I'm hoping it will be as enlightening to other
Access developers as it has been to me.
http://citeseer.ist.psu.edu/cache/pa...nent-based.pdf
Having been a database application developer for a long time, I have now
realized that the SQL bias tends to leads to an excessively white-boxed
thinking about applications. A little component-based thinking can go a long
way in terms of figuring out what parts of an application should perhaps -not-
share a logical database and use the database schema as the prime arbiter of
data integrity and consistency.
The link above is the best explanation I've been able to find about how
Component-Based Design works, and how to decide what aspects of a system
should be components.
From a database perspective, I would simply add to what's in the linked
article that the most frequently useful component split for a database-focused
application is at the OLTP/OLAP boundary.
By having one highly normalized schema used for entering and editing data
(OLTP), another schema designed for fast and easy querying and reporting
(OLAP), and a process for periodically updating the OLAP data from the OLTP,
one can substantially reduce the ripple effect of an application change on
distant parts of the application and avoid many of the compromises otherwise
necessary to support both transaction processing and reporting needs with a
single schema.
Having a separate OLAP database can also be a way to avoid having to merge 2
or more other, mostly separate applications simply to facilitate reporting
needs that span data from all of them.