Would the following approach be reasonable?
The Goal: queries have to be relatively fast, but without the hassles incurred by writing to a de-normalized database.
The Context: A database for handling user profiles for some CMS. User profiles sometimes change, but are not updated too regularly; so for the most part the information for each user remains the same for some time.
The Database Design:
First, we put together a highly normalized database, call it "
N". User profiles are stored in
Being highly normalized,
Nis secure and elegant to update, but slow to query. So we take all the data stored in
Nand we create a single de-normalized table. We call this table
Tis used for the sole purpose of retrieving user information. It is only read from, not written to, because it's prone to anomalies and would be a mess to update.
With one exception:
- To table
T, we add a boolean column, "
hasChanged", default value "
The CMS's algorithm for handling queries:
Suppose we want to load user 47's profile.
hasChanged == FALSEfor a row with
ID == 47, we can safely retrieve the user profile from this row. (Because we know nothing has changed.)
hasChanged == TRUE, then row 47 in
Tcannot be trusted (something has changed), so the query is passed on to the reliable but slow
When a user updates his profile,
Nis updated. When
hasChangedis set to
TRUEon all rows in
Tcontaining information that could be affected by this change.
Tis completely rewritten by the system whenever it becomes too unreliable (this is determined by how frequently queries are passed on to