This was posted in the OpenQM NG this morning.


Mutivalued datatypes considered harmful
http://www.regdeveloper.co.uk/2006/0...atypes_access/

--
Kevin Powick

"Kevin Powick" <nospam (AT) spamless (DOT) com> wrote

<rant>
Not surprising, except for the fact that Microsoft has brought the MV concept to
Mr. Whitehorn's attention at all. As the author says,

"... if you are a database freak like me ("If Ted Codd forbad it, I want no
further truck with your multi-valued data types.") but I quite understand
that, if you are an application developer, the finer points of relational
database theory often sound like just so much academic nonsense. If a new
feature makes life easier, who cares if it happens to break some arbitrary
rule written over 20 years ago?"

Only a "database freak" could love the solution of adding another table (or
two in this case) to solve a problem that does not exist. The entire
argument against the use of multi-values rests with the inability of
standard SQL to properly deal with them. It would only take an "ANY"
modifier to inform the query that any element equal to (or greater than...)
the value specified would meet the criteria, making the default being "ALL"
rather than "ANY".

The example in the article states that the query:

SELECT FName FROM CUSTOMER WHERE Hobby = "Rollerball"

would return zero rows since "Hobby" contains multiple values (atomic
elements?). Ok, so what if the following query was substituted:

SELECT FName FROM CUSTOMER WHERE <b>ANY</b> Hobby = "Rollerball"

In use, this modification could be switchable within the database where a
flag is set to specify whether the default is to assume "ANY" or "NO"; as in
....WHERE NO Hobby = ...., problem solved.

And before anyone adds the obvious, let's just agree that upgrading software
on a constant cycle is an Microsoft mainstay. Microsoft Access will need upgrading
(v2007) and all query engines would need upgrading too, ok, as if that's
never happened before.
</rant>

Nice to see SQL pukes taking an interest though...

BFaux

Kevin Powick wrote:
Very interesting, and much more down-to-earth explanation of why MV = Bad
than I've yet seen in cdt, for example. It seems to boil down to "Try
constructing an SQL query that does <x>," where <x> is trivial with an
English statement. So the argument seems to be that MV = Bad because
SQL can't deal with MV. (Bruce addressed this in another response.)

And how to reconcile this: "The development team feels that power users
find the creation of many-to-many joins using three tables conceptually
very difficult and will find multi-valued data types a much easier solution.
Having taught Access to such users since Access 1.0 I cannot help but agree
with this. Access power users will find this solution easier."

with this: "People who understand databases already have a good way of
implementing many to many relationships and will gain no benefit from
multi-valued fields."

Are "power users" different than "people who understand databases?"
Or, does "a much easier solution" provide "no benefit?"

--
frosty

Well - maybe when they adopt sql3
http://www.objs.com/x3h7/sql3.htm
and we start having collection sets .... maybe then multivalue will become
more standard....
Maybe...maybe...

"Kevin Powick" <nospam (AT) spamless (DOT) com> wrote

**warning - rambling text follows**

I think the term "power users" refers to people who aren't versed in
Relational Database Design, but specifically are "end-users" who use the
product to a high-level but are not developers.

Certainly, the concept of creating a MV field in Access would make logical
sense to this type of user as in the example given (Hobbies). It sounds
like Access almost reconciles this with "normal" relational stuff by
internally storing this as a seperate table, but, like all first attempts by
Microsoft to do anything, it doesn't quite complete the job by exposing the
new table directly.

However, it must be clear, that to support MV correctly, SQL would need to
evolve slightly.

As for the query (for selecting people with specific multiple hobbies),
wouldn't it be logical that the following would work:

select * from file where Hobby = "Fishing" and Hobby = "Chess"

I'm not quite sure what the resultant query would return though!! Using
standard RD theory, this would return two rows, with all the fields being
identical except for the "Hobby" field... I'm not a massive expert in SQL,
but wouldn't the "DISTINCT" modifier then restrict this to one row ? If so I
wonder what would happen to the Hobbies field!

All this says is that if the Relational world wants to move to multivalues
(which it certainly seems to want to, and needs to to support XML data)
then presumably the SQL language needs modifying to cope... This would
presumably have a benefit for exposing "real" MV data (ie "Pick") through to
SQL as we would no longer presumably need to normalise the data... Perhaps
this will benefit "us" in the longer term, by allowing MV products to be
exposed as "real" databases (all the first normal crap would be well in the
waste-paper-basket!)

Apologies for the rambling.. just about 20 cents worth....

Simon

Regards
Simon

--
================================
Simon Verona
Dealer Management Service Ltd
Stewart House
Centurion Business Park
Julian Way
Sheffield
S9 1GD

Tel: 0870 080 2300
Fax: 0870 735 0011

"frosty" <frostyj (AT) bogus (DOT) tld> wrote

Tom Phillips wrote:
Even where SQL3 features are introduced, they are not heavily used.
There are still large numbers of developers who are certain that 1NF is
required or at least a "best practice" in data modeling. One of the
benefits of SQL is the standardization. If you want a large variety of
reporting and other tools to work against your dbms, 1NF and SQL-92
standards still rule.

However, SQL is a dying standard even if it will be very visible for
years to come.and new standards will have to generate or map to it
(given that it is THE language of many DBMS products). New languages
such as XQuery, XML documents including RSS feeds, language extensions
for current languages (such as MS linq), new databases, and other
multivalue-savvy components will be what drives the industry back to
nested lists.

I'm particularly interested in list-valued attributes (including order)
and not only set-valued attributes (unique values, order not
important), which is what SQL3 has. I'm not sure about bags (not
unique values, order not important) since they can be implemented as
lists. MV folks implement property sets the same way we implement
property lists (with multivalues). This works well for the MV
implementations but will not be as good for other databases where the
lack of order mattering has been a virtue, where they are not storing
delimited strings (which is considered a peasant's approach to storing
data by the industry at large, it seems -- peasant's storage approach
or not, it seems to work remarkably well).

Cheers! --dawn
P.S. Once I can get my mind back to it, my next blog entry is entitled
"To whom should size matter," tangentially related to this.

Simon Verona wrote:


Yikes - I think that is hoping for a bit much. As much as I love
working with Pick, I realise it is far from a "real" database.

Earlier threads have shown that Pick (and clones) in both Queries and
in BASIC handle multivalues very weakly - so I am not sure other
vendors can learn much from "us" about how to handle them.

Maybe this will stimulate us to look at how other products handle
collections and therein see how "multivalues" can be better supported.

I appreciate what you mean by multi-values not being fully supported - I too
have found that a pain.

Still, doesn't prevent all the merits of Multi-value.

I didn't suggest that other vendors would learn from us, but they are
independently coming to the conclusion that "multi-values" is a good thing.

I would agree with you that this will perhaps provide some useful standards
that one or more MV products will migrate towards, and enable them to stand
alongside their RD counterparts.

Regards
Simon

--
================================
Simon Verona
Dealer Management Service Ltd
Stewart House
Centurion Business Park
Julian Way
Sheffield
S9 1GD

Tel: 0870 080 2300
Fax: 0870 735 0011

"Anthony Lauder" <anthony.lauder (AT) gmail (DOT) com> wrote

Anthony Lauder wrote:
You must be trolling.

Not sure he's "trolling" aas such...

Maybe being slightly overharsh..... see previous thread on issues with using
Access with Multivalues - I don't think he got a really good solution to his
issue which should be bread and butter for a MV system!

Simon

--
================================
Simon Verona
Dealer Management Service Ltd
Stewart House
Centurion Business Park
Julian Way
Sheffield
S9 1GD

Tel: 0870 080 2300
Fax: 0870 735 0011

"Mike Preece" <michael (AT) preece (DOT) net> wrote