One to Many and Auto Number Problem

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,A BE002. 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.

"David W. Fenton" wrote:

[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

Robert T 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.

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/

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


"Robert T" wrote:

"David W. Fenton" wrote:

[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

=?Utf-8?B?RnJlZA==?= wrote in
:

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/