Bob Johnson said:
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!!