roger.plowman (7/28/2011)
Due to the extreme flexibility requirements of our reporting system users need at least a nodding acquaintence with the database schema--primarily how specific data can be found in specific tables, and how the tables are related to each other.Rather than try and explain anything about normal forms we use the SPOT and Path metaphor. SPOT = Single Point Of Truth (ie the data is only stored in one single place *once*) and there are always paths between tables, so for example a Zone (set of contiguous zip codes) is always connected to only one Office which in turn is in one Region.
Of course the data has to be 3NF to support this, but I always thought SPOT and Path was a much more natural way to visualize 3NF anyway.
The trouble with the Spot and Path approach is that there are some collections of business rules for which it doesn't work, because some data has to be stored in more than one place (but a combination of foreign key constraints, null constraints, and unique constraints ensure that any attempt to update one copy without updating the other fails). There's an example of this in Wikipedia, on the BCNF page, in the section "Achievability of BCNF". What's going on is that some data is involved in several business rules (which should become database constraints) which can't all be represented in one table.
Where Spot and Path is applicable (which is very often) it seems to be a reasonable approach.
Tom