One to Many and Auto Number Problem

[Stefan Hoffmann]

hi Tony,

This assumes you are American.

Nah. I'm Bavarian. I only know SSN from the TV, which is influenced by
Holloywood, of course


mfG
--> stefan <--

 

[Ken Sheridan]

Don't forget that a primary key does not need to be a single column. A table
must have at least one candidate key, which can be one or a set of columns,
possibly even all columns in the table. Take a look at the Order details
table in the sample Northwind database; you'll see that its primary key is
made up of the OrderID and ProductID columns. When a surrogate prmary key
is introduced for reasons of convenience there will usually be a natural
candidate key already, e.g. a CityID autonumber column might be introduced
into a Cities table to cater for duplicate city names, but there will
probably be a candidate key of City and State already, or even of more
columns if city names can be duplicated within a single state (despite having
a grandmother from Boston, Mass., my knowledge of US geography is deficient
on this point, I'm afraid) For a discussion on the use of surrogate keys
(e.g. an autonumber) versus natural keys see the compilation by Joe Celko of
threads from the old CompuServe CASE forum at:


http://community.netscape.com/n/pfx/forum.aspx?msg=19495.1&nav=messages&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[Robert T]

Tony Toews wrote:

[What about date fields? Do you suggest text fields.]

No, I am not out on some extermist limb. Of course date fields should be
categorized as date fields.

As for my original argument, although I appreciate all of the feedback, we
will have to agree to diagree. I still believe that if you're not going to
perform any calcuations on a field that stores numerals [Phone Number, Zip
Code, etc.], they should be text fields.

Here are some comments from one of the experts in a progam called Alpha Five
that I've used for many years and continue to do such. It's a phenomenal
applicaton and I'm posting the comments I copied form a thread on the Alpha
Forum.

* Numeric fields should be used for any data that is going to be operated on
numerically, where as Character fields are for data that is not.
* An autoincrement field, except for the incrementing part to make a new
key, is not used a a number, but just as a unique key to indicate links
between separate tables.
* Many keys might really be a concatonation of 2 separate autoincrementing
keys, e.g. ABC001,ABC002,ABC003,ABD001,ABD002,ABD003,ABE001,ABE002. These
are used to have autoincrementing within groups as well as incrementing the
groups. While it could be done in two separate keys, since it is being used
as a link, should be in 1 field. This couldn't be done numerically (Well,
not as easily, anyway).
* If you needed to invert a key (essentially to quickly invert the table
entries) and then create an inverted key value to look up a next smaller
record in the table (e.g. using XLOOKUPC ), it is more difficult to do with
a numeric field
* If you want to create a combined index expression for a child database,
sorting by say the link (the autoincrement field from a parent) and combine
it with say, the Item Name to sort the children by link+Name, it is easier to
combine if it's already a character field.

But really, the most important decision is to use the most reliable method
for the particular database program you are using. Auto-incrementing fails
if two records get the same key, and that is the most important aspect.

 

[Robert T]

:

[I've never used a database engine in which integer fields were not
significantly faster than any character fields of more than 4 or 5
characters. The reason was the size of the index pages involved --
if the value takes fewer bytes to store, it means you get more data
into a smaller space, so that scans of the index take less time.

Also, I think that numeric processing functions in our CPUs are
faster than character processing.

I don't know for a fact that either of these are true, but it's
always been my surmise as to why all the database tutorials I ever
encountered recommended integer fields over text where feasible. ]

David:

Now that's a very interesting point, it's certainly going to make me think a
little more on this subject. Hopefully there's some way I can do some
research and ascertain if that is in fact true.

Robert

 

[Tony Toews [MVP]]

[What about date fields? Do you suggest text fields.]

No, I am not out on some extermist limb. Of course date fields should be
categorized as date fields.

Good. I wasn't quite sure.

As for my original argument, although I appreciate all of the feedback, we
will have to agree to diagree. I still believe that if you're not going to
perform any calcuations on a field that stores numerals [Phone Number, Zip
Code, etc.], they should be text fields.

Those specific examples I agree that they should be text fields. I
just wasn't sure what else you meant.

Auto-incrementing fails
if two records get the same key, and that is the most important aspect.

And Access autonumbers have never failed me. There has been a bug in
a Jet SP in the past but that's never affected me.

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/

 

[Fred]

Even if it IS true, I suspect that for a typical application you are saving
nanoseconds or microseconds, i.e. that that aspect is not significant enough
to influence a decision.

Many habits were formed when computers were 100 times slower which wasn't
that long ago.

Sincerely,

Fred

 

[David W. Fenton]

Even if it IS true, I suspect that for a typical application you
are saving nanoseconds or microseconds, i.e. that that aspect is
not significant enough to influence a decision.

Many habits were formed when computers were 100 times slower which
wasn't that long ago.

If you have large tables, and SQL with many joins (especially outer
joins), then these kinds of things can make a significant
performance difference, even with today's advanced hardware and tons
of RAM. And with server databases, that becomes even more important,
since all users are sharing the same processing resources.