Table vs. multiple tables

On Dec 21, 3:09 pm, "Pat Hartman" please no wrote:

Perhaps you are getting me confused with someone else...

the only time you would create a "third" table to hold the
relationship is if the relationship were m:m.

That's not what I said. I said in this thread, "The relationships [in
Northwind] between entities order:customer and order:employee should
(IMO) be modelled using distinct relationship tables." I'm really
confused that you would seemingly try to tell me I do something that I
explicitly said I would not.

We had a
discussion regarding one not too long ago. I don't remember the details but
it had something to do with someone wanting to implement a "cascade to null"
action with Access. Since this feature is not supported, I suggested using
a junction table which would allow the relationship to be deleted as a
result of cascading the delete of the one side row.

I don't think it could have been with me because I rarely have
nullable columns at all and I have never used the ON DELETE SET NULL
referential action BTW this is supported in Jet 4.0 (and above) but
not ON UPDATE SET NULL despite what the Access Help says.

Jamie.

--

Jamie Collins wrote:

On Dec 21, 1:01 am, "Tony Toews [MVP]" wrote:
Whereas I don't have a clue about entities and attributes and all that
other fancy, schmancy terminology and definitions. I think in terms
of tables and relationships.

You are probably being flippant for comic effect but I'll make the
obvious point anyway...

Only somewhat. I have no format training in databases or computers or
software.

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/

On Dec 21, 3:09 pm, "Pat Hartman" please no wrote:
I don't remember the details but
it had something to do with someone wanting to implement a "cascade to null"
action with Access. Since this feature is not supported

Just to clarify: the SQL-92 standard, to which Jet 'aspires', supports
four referential actions:

CASCADE
| SET NULL
| SET DEFAULT
| NO ACTION

Jet 4.0 (and above) supports CASCADE, SET NULL and NO ACTION, though
it supports SET NULL for the delete rule only.

Please clarify what you mean by, "a 'cascade to null' action... is not
supported."

TIA.

Jamie.

--

On Dec 22, 5:59 pm, "Tony Toews [MVP]" wrote:
I have no format training in databases or computers or
software.

You certainly do not require any to be able to express opinions in
this public Usenet discussion group.

Jamie.

--

Thanks. I stand corrected on that. I rarely use DDL to create or modify
table objects so I forget the things that the GUI does not support. I'm
going to add "updating the GUI to reflect current Jet options" to the wish
list I send to the Access team every year.

"Jamie Collins" wrote in message
...
On Dec 21, 3:09 pm, "Pat Hartman" please no wrote:
I don't remember the details but
it had something to do with someone wanting to implement a "cascade to
null"
action with Access. Since this feature is not supported

Just to clarify: the SQL-92 standard, to which Jet 'aspires', supports
four referential actions:

CASCADE
| SET NULL
| SET DEFAULT
| NO ACTION

Jet 4.0 (and above) supports CASCADE, SET NULL and NO ACTION, though
it supports SET NULL for the delete rule only.

Please clarify what you mean by, "a 'cascade to null' action... is not
supported."

TIA.

Jamie.

--

On Dec 26 2007, 3:40 pm, "Pat Hartman" please no
wrote:
I rarely use DDL to create or modify
table objects so I forget the things that the GUI does not support. I'm
going to add "updating the GUI to reflect current Jet options" to the wish
list I send to the Access team every year.

I guess there will always be advanced features and settings only
accessible via code but, sure, there are glaring omissions in the
Access UI.

Your wish is a good one but a little non-specific for me. I'd request
you make a specific proposal e.g. for the exposure of Jet's CHECK
constraints in the UI, which would have a positive knock on effect for
the Query Builder Tool Thing as they'd need to expand it to be able to
generate subqueries and should also enhance our understanding of Jet
CHECK constraints (to date Microsoft have published only scant
information).

If non-specific wishes are the norm (I really don't know how these
things work, what with the MVP program being non-transparent) then
something more achievable IMHO for the Access team would be to address
the impedance between DAO and Jet (again, as an example, CHECK
constraints are not exposed via DAO).

Perhaps someone reading this with an Access-related website will be
inspired to create some handy reference lists of Jet features not
exposed in the Access UI and DAO respectively.

PS my wish would be to get more details about the features already
present in the engine e.g. the equivalent of the below article for
Jet's DECIMAL data type (this kind of stuff must be documented
somewhere, surely we have a right to know g?!):

Precision, Scale, and Length
http://msdn2.microsoft.com/en-us/lib...4(SQL.80).aspx

PPS just think of the features and settings that must exist in the Jet
engine but are not exposed at all (DAO, DDL, OLE DB provider, registry
key) by accident or design ;-)

Jamie.

--

I was just generalizing here. When I send the list, I will provide specific
examples.

The Jet Programmer's Guide used to provide a lot of useful information but
it hasn't been updated since Jet 3.5 and so is somewhat out of date. You
might want to contact the author/publisher to see if you can repubulish it
if you have any interest in writing. There is lots of new stuff to include
now that the Access team has taken over Jet (now called ACE?) from the SQL
Server team.

If you have the time and inclination to create the cross-reference, I can
get it into the hands of the Access team for review.

"Jamie Collins" wrote in message
...
On Dec 26 2007, 3:40 pm, "Pat Hartman" please no
wrote:
I rarely use DDL to create or modify
table objects so I forget the things that the GUI does not support. I'm
going to add "updating the GUI to reflect current Jet options" to the
wish
list I send to the Access team every year.

I guess there will always be advanced features and settings only
accessible via code but, sure, there are glaring omissions in the
Access UI.

Your wish is a good one but a little non-specific for me. I'd request
you make a specific proposal e.g. for the exposure of Jet's CHECK
constraints in the UI, which would have a positive knock on effect for
the Query Builder Tool Thing as they'd need to expand it to be able to
generate subqueries and should also enhance our understanding of Jet
CHECK constraints (to date Microsoft have published only scant
information).

If non-specific wishes are the norm (I really don't know how these
things work, what with the MVP program being non-transparent) then
something more achievable IMHO for the Access team would be to address
the impedance between DAO and Jet (again, as an example, CHECK
constraints are not exposed via DAO).

Perhaps someone reading this with an Access-related website will be
inspired to create some handy reference lists of Jet features not
exposed in the Access UI and DAO respectively.

PS my wish would be to get more details about the features already
present in the engine e.g. the equivalent of the below article for
Jet's DECIMAL data type (this kind of stuff must be documented
somewhere, surely we have a right to know g?!):

Precision, Scale, and Length
http://msdn2.microsoft.com/en-us/lib...4(SQL.80).aspx

PPS just think of the features and settings that must exist in the Jet
engine but are not exposed at all (DAO, DDL, OLE DB provider, registry
key) by accident or design ;-)

Jamie.

--

On Jan 3, 8:10 pm, "Pat Hartman" please no wrote:
The Jet Programmer's Guide used to provide a lot of useful information but
it hasn't been updated since Jet 3.5 and so is somewhat out of date. You
might want to contact the author/publisher to see if you can repubulish it
if you have any interest in writing. There is lots of new stuff to include
now that the Access team has taken over Jet (now called ACE?) from the SQL
Server team.

It's a fantastic idea but the underlying problem is catch-22 in
natu where do I get the information on which to write the
revision?

Let's take a first step and say I was interested in publishing an
article on Jet's DECIMAL data type, introduced in Jet 4.0.

Jet is a Window component 'owned' by the SQL Server team (IIRC the
Access take have merely been granted a private branch of the code).
[Aside: yes, 'ACE' is the accepted term in these groups and the
general term 'Jet' is taken to encompass ACE].

From the aforementioned SQL Server 2000 article I note, "In previous
versions of SQL Server, the default maximum was 28."From the few
details Microsoft has given us about Jet's DECIMAL data type we know
that it has a maximum precision of 28; from experience I've noted that
the default precision is often 28 e.g.

? CurrentProject.Connection.Execute("SELECT 0.5;")(0).Precision
28

So I would speculate that a good starting point would be the
equivalent SQL Server 7.0 article; a quick Google suggest such a
document did exist because I found a broken URL.

[Aside: the chapters from the Jet Database Engine Programmer's Guide,
which I believe is the book you referred to, I found on Microsoft's
site are classed as "SQL Server 7.0 Product Documentation", which may
just be a coincidence (http://www.microsoft.com/technet/prodtechnol/
sql/70/proddocs/msjet/jetch04.mspx).]

I could then test behaviour in Jet against the SQL Server 7.0
article... but I foresee problems. If I experience an inconsistency,
how do I know whether it is a bug or behaviour by design? (assuming I
was able to ascertain that the behaviour was in fact Jet rather than
another component) Surely, I first need to know what the designer's
intention was i.e. a spec?

Allen Browne once wrote of sorting a column of DECIMAL values: "Nulls
and zeros sort unpredictably - at the beginning, middle or end,
depending on the data." (http://allenbrowne.com/bug-08.html).
Microsoft have told us that Jet's NULL collation is "that null values
are sorted at the low end of the list" (http://msdn2.microsoft.com/en-
us/library/aa140022(office.10).aspx), based on the reasonable
assumption that the OLE DB provider reflects its underlying engine. I
cannot reproduce a simple sort where NULLs in a DECIMAL column sort
anywhere other than and the end of a resultset, even when using the
mdb file supplied by the author. I think if NULL collation was broken
in Jet 4.0 then it would be significant enough to warrant its own MSDN
article. I've nothing to suggest the NULL collation is broken other
than the anecdotal evidence above i.e. no description of the steps to
reproduce such a scenario.

In case you think I've gone OT let me make my point: observation alone
doesn't amount to proof. That is why IMO the onus is on Microsoft to
provide such information rather than for me to discover their designs
by trial and error.

Jamie.

--