Re: Mutivalued datatypes considered harmful

**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@xxxxxxxxx> wrote in message
news:BaqdndYAcq9UpCDZnZ2dnUVZ_vGdnZ2d@xxxxxxxxxxxxxxx
Kevin Powick wrote:
This was posted in the OpenQM NG this morning.

Mutivalued datatypes considered harmful
http://www.regdeveloper.co.uk/2006/07/18/multivalued_datatypes_access/

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



.

Relevant Pages

  • Re: SQL DBA Permissions
    ... >the DBA group to the local Power Users group on the SQL ... Local Administrator of the SQL Server wouldn't give them domain admin ... administrators group on your SQL Servers, ...
    (microsoft.public.win2000.security)
  • RE: Trouble connecting user with MS Access adp to SQL Server 2000
    ... It sounds like JT or the group "Power users" was never added as a SQL ... Execute this statement as a stored proc for SQL. ... Exec sp_grantlogin ...
    (microsoft.public.sqlserver.security)
  • "Local" Admin Cannot Assign User/Group Permissions
    ... databases. ... I have created separate Admin groups for each app, ... granted some power users complete Admin rights to their database and all ...
    (microsoft.public.access.security)
  • Re: Lookup fields in tables bad?
    ... where there is no developer, and which are created in minutes and ... used by only on or two power users. ... And, yes, I know it's tilting at windmills to fight this fight in ... job in its sample databases of implementing "easy" things that are ...
    (microsoft.public.access.tablesdbdesign)