1NF Violations *promoted* in MS Access?

[Bob Johnson]

This post is NOT a rant. I'm really wondering about this:

The following quote is from
http://office.microsoft.com/en-us/access/HA100678311033.aspx

<< "Multivalued lookup fields: Most database programs, including earlier
versions of Access, allow you to store only a single value in each field. In
Office Access 2007, however, you can create a lookup field that allows you
to store more than one value in each field. In effect, Access creates a
many-to-many relationship within the field and hides the details of the
implementation by using system tables." >>


1NF tells us that we should never have multi-valued columns. What's going on
here if the database engine promotes 1NF violations by offering multi-valued
columns?

How is this feature a good thing? What am I supposed to tell those I train
on database design when I tell them all about the virtues of 1NF and all of
the evils of multi-valued columns - and one of them asks, "why does Access
support multi-valued columns if they are so bad to have?"

What am I supposed to say?

 

[Pat Hartman \(MVP\)]

Most Access MVPs agree with you. This "feature" was added to Access/Jet to
improve compatibility with SharePoint. Unfortunately, it reduced
compatibility with SQL Server. The good news is that behind the scenes, the
multi-valued field is actually implemented as a many-to-many relationship.
The bad news is that the development team chose to hide the real structure
from us.

We are hoping that in a future release of Access this blackbox will be
opened so we can see the created tables and actually make use of the
"feature" to support many-to-many relationships that we have already
defined. The form representation of the multi-value list box is excellent
and many UIs could make good use of it for little "lists" that you need to
attach to a record. When your user wants to have the many-side represented
as a multi-value listbox, there is a lot of code behind the scenes to manage
this UI. It was a real disappointment to me and the other MVPs when
Microsoft chose to prevent us from making use of this great UI feature.

The other two new data structure "features" have similar issues. They look
to the user as if they violate 1st normal form but in reality they are
implemented properly. It's just that the real structure is hidden from us.

Write to Microsoft with your comments. The more people they hear from, the
more likely they are to act on the issue.

 

[Jeff Boyce]

"They didn't ask me."?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

What am I supposed to say?

If I was teaching gun safety, I'd tell my students that shooting oneself in
the head is a bad idea even if the gun will allow it.

 

[George Hepworth]

My momma used to ask me, "If all your friends jumped off a bridge, would you
do it too?"
The SMART answer is, of course, "Just because some people do it, doesn't
make it a good idea."

George

 

[Albert D. Kallal]

It not actually storing the values in the one field.

It actually creating a another table for you. So, it not like storing
1,2,3,4 in one field.....

So, for users that know nothing about MV fields..they can store a persons
favorite colors or whatever in a field with a neat-o pick list box..and not
have to write one line of code.

It certainly opens up the doors for a lot of users of ms-success who other
wise WOULD IN FACT stuff multiple values into a field. In a way, ms-access
is actually tricking these users into using the CORRECT design, they just
don't know it!!


And, if they drive a automatic car...they don't need to learn the clutch...

I see this issue as making users choose the correct design. Since now we
will not see users throwing in a whole bunch of values in a field separated
by comma....

 

[Bob Johnson]

RE:
<< ms-access is actually tricking these users into using the CORRECT design,
they just don't know it >>

Okay, then two followup questions:

1. Say I come along and have to query this database. How do I query against
this multi-valued field if I don't have access to the underlying "correct"
tables?

SELECT blah blah blah WHERE MyMultiValuedField = ?????

What do I put for the ????? in the above query?

2. Say I have to extract data from the database and export it to
godknowswhat. How do I extract this in a way that preserves data integrity
if I don't have access to the underlying "correct" tables? Or will it be
exported as a multivalued filed (comma separated or whatever)?

Again, not ranting here, just wondering how to deal with data integrity
issues related to this new "feature."

Thanks.

 

[Tony Toews [MVP]]

Okay, then two followup questions:

1. Say I come along and have to query this database. How do I query against
this multi-valued field if I don't have access to the underlying "correct"
tables?

SELECT blah blah blah WHERE MyMultiValuedField = ?????

What do I put for the ????? in the above query?

MyMultiValuedField.Value = ?????
http://office.microsoft.com/en-ca/access/HA012337221033.aspx?pid=CH100645681033

2. Say I have to extract data from the database and export it to
godknowswhat. How do I extract this in a way that preserves data integrity
if I don't have access to the underlying "correct" tables? Or will it be
exported as a multivalued filed (comma separated or whatever)?

You can do a sum query against the above value and get the data in the
lookup/master table which you would then append into new table. Then
you can do a similar query to pull in the "junction" table data to
create that table.

Remember the key phrase here "billable hours"

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

<< "Multivalued lookup fields: Most database programs, including earlier
versions of Access, allow you to store only a single value in each field. In
Office Access 2007, however, you can create a lookup field that allows you
to store more than one value in each field. In effect, Access creates a
many-to-many relationship within the field and hides the details of the
implementation by using system tables." >>


1NF tells us that we should never have multi-valued columns. What's going on
here if the database engine promotes 1NF violations by offering multi-valued
columns?

How is this feature a good thing? What am I supposed to tell those I train
on database design when I tell them all about the virtues of 1NF and all of
the evils of multi-valued columns - and one of them asks, "why does Access
support multi-valued columns if they are so bad to have?"

While I will never use them, based on my experiences with folks who
are new to Access I can see this being quite useful. We want to get
folks off of spreadsheets. This feature is a stepping stone.

And there are ways of getting at the underlying data using queries and
using DAO, actually ACE as that is the new name for DAO.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Bob Johnson]

Re:
<<We want to get folks off of spreadsheets.>>

We all know that some people (heck, *many many many* people; dept
secretaries etc) will *never* get away from their spreadsheet mentality. So
why are we making it easier for them to implement spreadsheet (flat) designs
IN a database? They should just stick with the spreadsheet. At least it's
easier to untangle later on when the app grows and needs to be ported to
something more robust (happens all the time).

Features like that in question here (plus others added to MS access in the
past couple of years, like a column definition for which the value is
defined on a freaking query) make a database look and feel like a
spreadsheet - thereby DECREASING the likelihood that these folks will ever
make that mental leap from spreadsheet to relational database design.


<<This feature is a stepping stone.>>
This "stepping stone" only serves to blur the lines between spreadsheet and
database. I've been hired many times to teach people relational database
design. A clean break from the spreadsheet mentality has been most helpful -
at least in my experience. Once they understand that a table is NOT a
spreadsheet - only then are they able to gain some traction and really "get
it" regarding relational design.

I'd bet that anyone who needs or would even benefit such a stepping stone
will NEVER understand relational database design as a separate concept from
a spreadsheet.

-Bob

 

[Bob Johnson]

RE:
<< Remember the key phrase here "billable hours" >>

No joke here: I was just paid over $150K during the past 10 months to
untangle the absolutely worse MS Access nightmare I've ever seen. Clients
don't like those "billable hours" of which you speak. It was spreadsheet
design all the way through - with tables storing data for multiple entities,
data for multiple entities being stored in multiple tables, 1NF violations
at every turn, etc. IMHO, MS Access, itself, is at *some* fault here for
ENABLING the idiot who created the mess to get as far as he did. NO, I"m not
blaming the gun for a murder - but if you notice, guns typically have safety
locks and switches that make it hard to do the wrong thing easily. It seems
that with every new version of MS Access, the product makes it progressively
and substantially *easier* to do the wrong thing (specifically, easier to
implement spreadsheet designs in a db). Data integrity goes out the window,
along with runtime performance.

-"Bob"

 

[Albert D. Kallal]

SELECT blah blah blah WHERE MyMultiValuedField = ?????

SELECT blah blah blah WHERE
MyMultiValuedColor = "red" and MyMultiValuecolorField = "blue"

The above answer in regular sql is a question I often ask prospective hires
of mine.

care to offer a solution? (the multi-value answer as above does work).

Read carefully the question:

I want all customers who have a favorite color of red and ALSO blue....

I talk about this problem here:

http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000006.html

 

[Douglas J. Steele]

I suspect Albert meant:

SELECT blah blah blah WHERE
MyMultiValuecolorField = "red" and MyMultiValuecolorField = "blue"

 

[Bob Johnson]

RE:
<< The above answer in regular sql is a question I often ask prospective
hires of mine >>

I hope you don't let them pass simply because they got the SQL statement
"correct" (correct for the MV column deal). I'd also look for them to be
able to articulate the important tradeoffs of MV columns and why they run
contrary to 1NF - and they do from the user's perspective even if "under the
covers" the db engine is creating multiple proper supporting table. I'd also
ensure the candidate can articulate specific problems incurred from 1NF
violations.


RE the following quote from your article (first bullet point)
<quote> We did not have to create another table to hold the additional phone
numbers (as we *should* in a traditional sql database system create a new
table) </quote>

How is the beginner dba supposed to learn how we *should* implement proper
designs (I agree with your parenthetical comment) if we give them MV
columns? MS Access is busy at work here making the improper thing very easy
to do... promoting the spreadsheet metaphore of a database (which is
completely wrong) by providing MV columns - which are akin to merging
columns vertically in Excel. What a shame!

Trying to be generious, I guess MV columns and other such
should-be-non-starters in a db (like a table column defined to get its value
from a query - "geeze") can be understood as possibly okay to have when the
following two assumptions are met:
1. the person using these things is incapable of learning the proper way of
designing a normalized database. If not incapable, then incredibly unlikely
to learn how to do it right anyway...
AND (not OR)...
2. The application in question is guaranteed to never grow beyond the
generally advertised/intended use as a desktop db in support of non
mission-critical data and with relatively few users.

It's like we're saying, "if you're never going to get there anyway, and who
really cares about your data anyway, then, well, let's make your life
easier." The application and it's creator and users are operating in a sort
of sandbox where things don't really matter. At least we're providing them
with a screaming great way to crank out mailing labels, for example. In that
sort of situation I can agree that MS Access would be better than Excel.

Again - please understand that I'm not having a rant here. I'm just
surprised to find such a feature as MV columns and wanted to know what the
Access community thinks about it, and trying for myself to come up with a
reasonable justification for having this and other features that promote the
spreadsheet metaphore of a database - as improper as that metaphore is. It's
kind of like situational ethics... "when is it right to do the wrong thing?"

-"Bob"

 

[David W. Fenton]

Features like that in question here (plus others added to MS
access in the past couple of years, like a column definition for
which the value is defined on a freaking query)

Can you unpack that and explain what you mean? All I can think of is
lookukp fields, and those have been around since at least Access 95.

 

[Bob Johnson]

Yes - it's lookup fields to which I refer. I guess they've been around a lot
longer than I thought (still doesn't make them a "good thing").

http://www.mvps.org/access/lookupfields.htm

My point isn't to enumerate what I perceive to be the problems of MS Access.
I was just really surprised to see such a blatant encouragement for users to
violate 1NF with these new multi-valued fields (thus my OP here), and was
subsequently referring to other such ways in which Access promotes the
database-table-is-really-a-spreadsheet mentality to neophytes.

-"Bob"

 

[Albert D. Kallal]

RE the following quote from your article (first bullet point)
<quote> We did not have to create another table to hold the additional
phone numbers (as we *should* in a traditional sql database system create
a new table) </quote>

Well, in the above, I talking about a *true* multi-value database, not the
new extensions to "jet" that we have. Note the date of the above article is
December 2001!! -- so, I wrote this LONG before ms-access adopted this. In
those true multi-value database systems, a new table is NOT created. Don't
confuse a true multi-value database system with that of a relational
database like "jet". In a traditional "relational" systems (be it the new
JET, or oracle), to achieve the above..you create a new table. In a MV
system..you do NOT have to do *anything* at all.

How is the beginner dba supposed to learn how we *should* implement proper
designs (I agree with your parenthetical comment) if we give them MV
columns? MS Access is busy at work here making the improper thing very
easy to do... promoting the spreadsheet metaphore of a database (which is
completely wrong) by providing MV columns - which are akin to merging
columns vertically in Excel. What a shame!

Well, actually, since ms-access does in fact create the tables, then you
wind up with normalized data..not a spreadsheet. The end user will benefit
from this, and it will take far longer for the appcation to fall apart, and
have to bring in professional help.

1. the person using these things is incapable of learning the proper way
of designing a normalized database. If not incapable, then incredibly
unlikely to learn how to do it right anyway...
AND (not OR)...

It is question of balance. Should you have to know binary counting, or
assembler to use ms-access? A guy sits down and needs to store a persons
favorite foods..and now in ms-access that is trivial. The fact that this
data is actually normalized is terrific thing. Before, the user would
actually try and stuff data in to the one field separated by comma (or go
food1, food2, food3 to store favorite foods). Now, a new table, and separate
child values are stored. To me, this is step forward, not backwards. Should
we prevent users from driving cars by only offering standard transmissions?

2. The application in question is guaranteed to never grow beyond the
generally advertised/intended use as a desktop db in support of non
mission-critical data and with relatively few users.

Well, sure. But, a very large portion of those applications are written by
those users. There is ZILLIONS of successful ms-access applications running
using ONLY macros. This is a question of empowerment to the people. And,
further, in code (both dao/ado, you can work with the multi-data!).

Which is worse:

having users stuff values into one field separated by comma,
or
having ms-access create another table, and have the user store
normalized data?

It seems to me the 2nd approach is 100 times better. Sure, the driver will
never learn how to use a clutch (or normalize data), but in both cases..the
user does not care one bit. They want their reports..and they want to store
this data. They want to drive to work in their automatic car! That is all
they care about. They also don't care about the c++ language used to write
ms-access.

I see the MV fields as encouraging users to not stuff values into a single
field. That is actually good. As a developer, I can use code to extract and
export this data anyway.

Many of us developers are simply asking that a "toggle" feature be enabled
to display those extra tables. Really, that is all we need here to end this
controversy.

Again - please understand that I'm not having a rant here. I'm just
surprised to find such a feature as MV columns and wanted to know what the
Access community thinks about it

Oh, no rant taken at all. I think it is good you ask, debate..and get a
feeling for what this feature means. When this feature was proposed at the
developers conference, you had a near riot. Some of us had to apologize to
the Microsoft developers because we were not kind!!

You have to understand that I am one of the people that tends to see this
feature as a good thing. I suppose the fact that I worked on d3 (pick),
Revelation, IBM's Universe and several others for about 10 years likely
taints my view. Those mentioned systems are mainframe multi-value database
systems that have been in use for 30+ years. I really do like multi-value
database systems.

I also don't consider the new extensions to ms-access a true multi-value
database because in the systems mentioned, ANY field can instantly become
multi-valued, and no new tables need be crated behind the scenes as in a
relational database.

So, the access community has had some lively debate, and we all grown up
people. so, some hard questions on your part is a very good thing.

At the end of the day, we don't have to use macors, nor do we have to use
these new multi-value fields. I can assumer that a lot of users will use
lookup fields, will use multi-value fields, and will use macros....none of
which I use!!

 

[Bob Johnson]

RE:
<< Many of us developers are simply asking that a "toggle" feature be
enabled to display those extra tables. Really, that is all we need here to
end this controversy >>

Agreed.

If we can never get to those underlying tables, then they might as well not
even exist (given that we could only get to denormalized views of the data).
Getting to that normalized data would be a "life saver" for a real dba
coming along behind a neophyte when the Access app takes on for more
importance and # of users than originally planned (happens all the time) and
has to be scaled to something more robust (both the database and client
application).

Thanks for the lively debate.

-"Bob"

 

[David W. Fenton]

My point isn't to enumerate what I perceive to be the problems of
MS Access. I was just really surprised to see such a blatant
encouragement for users to violate 1NF with these new multi-valued
fields (thus my OP here), and was subsequently referring to other
such ways in which Access promotes the
database-table-is-really-a-spreadsheet mentality to neophytes.

I don't think that multi-value fields are nearly as much of an issue
as lookup tables, because, behind the scenese, they've been properly
implemented and a programmer can get access to those
behind-the-scenes structures. It certainly makes Albert's example
query a helluva lot easier, don't you think?

I won't use them myself, but for end users, I think it really is a
good benefit.

 

[David W. Fenton]

You have to understand that I am one of the people that tends to
see this feature as a good thing. I suppose the fact that I worked
on d3 (pick), Revelation, IBM's Universe and several others for
about 10 years likely taints my view. Those mentioned systems are
mainframe multi-value database systems that have been in use for
30+ years. I really do like multi-value database systems.

I would say, Albert, that your advocacy of MV databases changed my
mind on the subject.