I'm actually on board with these MV columns now... not for Albert's reasons,
although they are good, but primarily for the following:
IMHO, the biggest single cause of denormalized and otherwise totally "WTF"
Access table designs is the binding of the UI directly to underlying tables
and queries. The neophyte user only points-n-clicks to create an Access UI.
Binding is their best friend (no code needed). But because of binding they
often (practically all the time that I've seen) design tables to reflect the
UI. Their table designs do not event attempt to correctly model the
real-world entities that should be modeled because they don't know how to do
that and still use binding. 1NF violations galore. You want multiple phone
numbers? Then you get columns like Phone1, Phone2, Phone3, PhoneN. That
story, alone, is told over and over again - thus go the evils of binding
[directly to tables]. Yes, they could jolly well create a 1:M relationship
with a PhoneNumbers table and have a subform, but it frequently doesn't
happen as the 1NF violation is easier for the spreadsheet mentality to grasp
(repeating groups is akin to multiple columns in a spreadsheet).
So, these MV columns can, it appears to me, provide some relief for such a
scenario. Let-em create the MV column for phone numbers. The MV column and
its apparent implementation actually provides an entire abstraction layer
between the user and the *real* underlying normalized tables. Thus we get to
allow the neophytes to employ binding (their best friend) and still get the
correct table design - something many of them would *never* do. Brilliant,
really, now that I think about it. And everybody wins because the seasoned
dba and developer can come along when the whole thing needs to be ported to
something more robust and get to the "real" underlying table structure that
properly implements a normalized design. Nice.
It seems to be we would be within our right minds to *encourage* neophytes
to use MV columns. That way we get normalized data from people who have no
idea of what a normalized table design is or should be - right?
RE:
<< ...because, behind the scenese, they've been properly implemented and a
programmer can get access to those behind-the-scenes structures. >>
And that was my biggest concern. As a programmer migrating data from MS
Access to a more robust solution (I've done a lot of that for many years) I
want to get to the behind-the-scenes structures and pull out normalized
data.
RE:
<< It certainly makes Albert's example query a helluva lot easier, don't you
think? >>
Yep
-"Bob"
