A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Autonumber Fields



 
 
Thread Tools Display Modes
  #41  
Old September 27th, 2005, 08:18 AM
external usenet poster
 
Posts: n/a
Default


BruceM wrote:
I am not going to use a
multi-field PK.


Why? You seem to be basing this on 'gut feeling'. Remember PK =
clustered index (physical order), and shouldn't be confused with a
simply NOT NULL UNIQUE candidate key.

A name and address combination
is a poor choice for PK.


Remember PK = clustered index. If you have relevant queries which use
these columns then they stand a good chance of making an excellent PK.

Updating many records if somebody's address
changes makes no sense whatever. It destroys the whole point of using a
relational database.


No, it tells you that using an name + address doesn't make a very good
key (as you said earlier) but if it's all that you've got then you are
stuck with it (or change the system). You seem to be assuming a key
cannot change, again based on gut feeling alone. I ask again, why do
you think ON UPDATE CASCADE was invented?

  #42  
Old September 27th, 2005, 08:38 AM
external usenet poster
 
Posts: n/a
Default

John Vinson wrote:
For a Contacts table, last_name, first_name and postal_address makes a
fine natural key


Fred Brown, xxx D Street, Parma, Idaho
Fred Brown, xxx D Street, Parma, Idaho


How on earth do you tell them apart!? You'd better issue your friends
with ID numbers and start calling them "Fred Brown 1" and "Fred Brown
2". Or do you have some sort of advanced biometrics testing or visual
recognition system going on there? Do they ever try and pretend to be
the other one, and really mess up the data you are collecting about
them?

This week, I have spoken to two organizations - supermarket home
delivery service and bank respectively - on the phone without my
customer reference to hand and they used my 'name' + 'address' + 'has
an account with us' as a key i.e. a way of identifying me. Thankfully,
the latter had some additional security information I had to supply but
the former were just happy to get a truck full of melting ice cream off
their hands.

  #43  
Old September 27th, 2005, 09:47 AM
external usenet poster
 
Posts: n/a
Default


BruceM wrote:
I am inclined to use autonumber or some other fixed value (e.g. Invoice
Number) as the PK.


Word of warning: an incrementing autonumber can leave small gaps in a
sequence e.g. a autonumber value was served out but the transaction was
rolled back and the served number is never reused. I've heard said that
if exposed, e.g. as an invoice number, you may have to account for the
missing numbers to an auditor. I know from a previous career that an
auditor will ask about missing numbers in a sequence of cheque/check
numbers. The only 'orders' database application I've worked on was
'life critical', hence we used no autonumbers and all data had to be
retained for audit trail purposes, even on rollback.

  #44  
Old September 27th, 2005, 12:14 PM
BruceM
external usenet poster
 
Posts: n/a
Default

I use some sort of Dmax +1 code to assign the number when I need sequential
numbering. I had intended by the use of "or" to convey that invoice number
is not autonumber.
wrote in message
oups.com...

BruceM wrote:
I am inclined to use autonumber or some other fixed value (e.g. Invoice
Number) as the PK.


Word of warning: an incrementing autonumber can leave small gaps in a
sequence e.g. a autonumber value was served out but the transaction was
rolled back and the served number is never reused. I've heard said that
if exposed, e.g. as an invoice number, you may have to account for the
missing numbers to an auditor. I know from a previous career that an
auditor will ask about missing numbers in a sequence of cheque/check
numbers. The only 'orders' database application I've worked on was
'life critical', hence we used no autonumbers and all data had to be
retained for audit trail purposes, even on rollback.



  #45  
Old September 27th, 2005, 12:27 PM
external usenet poster
 
Posts: n/a
Default


BruceM wrote:
I had intended by the use of "or" to convey that invoice number
is not autonumber.


I totally read that wrong ;-)

  #46  
Old September 27th, 2005, 12:50 PM
BruceM
external usenet poster
 
Posts: n/a
Default

To clarify, I am not going to use a multi-field PK when there are other (and
simpler) means to guarantee uniqueness. If I have PartNumber and Process
(plating, welding, etc.) it makes sense to combine the two, since the
combination of Blade and Plating should appear just once. Combining
FirstName, MI, LastName, and a few address fields PLUS an autonumber or some
other kind of ID, then storing all of those fields in the Child table, makes
no sense to me. I can't believe that it is more efficient storing so much
redundant data then to have the index be based on an artificial number. It
has a lot to do with reading what others have written here (people who offer
a lot pragmatic and practical advice, and who have demonstrated again and
again their command of the program). EmployeeID (a four-digit number) works
quite well. I have no intention of bloating my database by storing several
other (and unnecessary) fields in the interest of a physical order that is
as arbitrary as any other way of arranging the data. Sometimes I need to
arrange Employee information by criteria other than LastName (by Department,
for instance, or by date). Even if I cared about physical order, one way of
ordering the records in my table is as good as another when I need to
arrange them in so many different ways in the course of using the database.
EmployeeID is as good a choice as any. Same with an autonumber.
"Destroys" is too strong a word. However, you will not be able to convince
me that continually updating multiple records and storing so much redundant
data improves anything in a situation where the unique ID number is part of
the record anyhow.
You will need to look elsewhere for a convert. Your thoughts have been
interesting, and have prompted me to investigate some new areas, but are not
going to inspire me to rework my basic approach of basing my PK on
uniqueness rather than on a particular physical order.

wrote in message
oups.com...

BruceM wrote:
I am not going to use a
multi-field PK.


Why? You seem to be basing this on 'gut feeling'. Remember PK =
clustered index (physical order), and shouldn't be confused with a
simply NOT NULL UNIQUE candidate key.

A name and address combination
is a poor choice for PK.


Remember PK = clustered index. If you have relevant queries which use
these columns then they stand a good chance of making an excellent PK.

Updating many records if somebody's address
changes makes no sense whatever. It destroys the whole point of using a
relational database.


No, it tells you that using an name + address doesn't make a very good
key (as you said earlier) but if it's all that you've got then you are
stuck with it (or change the system). You seem to be assuming a key
cannot change, again based on gut feeling alone. I ask again, why do
you think ON UPDATE CASCADE was invented?



  #47  
Old September 27th, 2005, 12:57 PM
BruceM
external usenet poster
 
Posts: n/a
Default

Interesting article. I'll check out the site when I have more time. MSDE
may well be useful at some point, but I do not have the extra time to look
into it right now. The reality of my world is that I have a certain number
of things I need to accomplish and a limited amount of time in which to do
so. SQL server in any form is not on my horizon.

wrote in message
oups.com...

BruceM wrote:
I really don't know what you mean by "porting to MSDE".


By MSDE I mean SQL Server, the free version. The support for
constraints is IMO slightly worse than Jet 4.0 but what MSDE does have
that Jet doesn't is triggers. If there isn't a suitable constraint,
then a trigger may be employed (a bit like your Before Update, I
assume, but at the database level).

You can strongly discourage using
the front end for such purposes, but have not provided a reason why.


Suggested reading:

Mop the Floor and Fix the Leak, Part 1, by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko25



  #48  
Old September 27th, 2005, 01:06 PM
BruceM
external usenet poster
 
Posts: n/a
Default

An ID can be arbitrary, sequential, based on a combination of date and
sequential number or letter and sequential number, etc. Where I work
EmployeeID is assigned sequentially, and is not seen except when adding or
editing an employee record. If two people have the same name it does not
help to distinguish them as John Doe 4421 and John Doe 4241. If with about
100 employee records I confront that kind of duplication I will need to come
up with something "real" to distinguish them (MI, Department, Title, or
whatever) in the real world. For the purposes of the database, ID number is
fine. It is a compact and efficient PK and FK.

"Amy Blankenship" wrote in message
...
Yes, and StudentID is usually an AutoNumber in my applications, because
the way I know which student it is is usually an artificially created but
exposed Login ID that will be unique but makes a lousy Foriegn Key to
other tables. So I associate that with the actual primary key that's an
autonumber. I guess you could do it the other way, associating some
arbitrarily assigned Integer with the exposed LoginID and use the integer
to make your application development easier (as discussed earlier), but it
seems six of one and half dozen of the other...

-Amy

"BruceM" wrote in message
...
Let's suppose I add an arbitrary number to each record. I still can't
tell them apart without some other sort of information. What that may be
depends on the circumstances. If I am storing course information I will
probably use StudentID. Since StudentID is what distinguishes them from
each other, I will just use that as the PK. If two people with the same
name enter the school the same year, are in the same graduating class,
and have the same middle initial, I will need to come up with something
else to assure they are not confused with each other in the records. I
am not going to use a multi-field PK. Names and addresses change. A
name and address combination is a poor choice for PK. Updating many
records if somebody's address changes makes no sense whatever. It
destroys the whole point of using a relational database.

"Amy Blankenship" wrote in message
...
And what if you absolutely have to tell Miss NE personID = 1 from Miss
NE personID = 2, for exam results and such? For a contact database it
may NOT be important who it is, but for other purposes, like storing
course completion results, etc., it is VERY important!

-Amy

"BruceM" wrote in message
...
"I do not rely on front end applications to enforce data integrity and
would strongly discourage such development. If I have a business rule
which Jet cannot enforce via constraints then I would recommend porting
to MSDE."

I really don't know what you mean by "porting to MSDE". I found out
what MSDE is, but for my purposes I will find a way to validate the
data, whether at the table level or in the front end. You can strongly
discourage using the front end for such purposes, but have not provided
a reason why. If there is a Spouse First Name field it may be required
if the person is married, but certainly not otherwise. My choice is
the front end for such validation rather than another piece of
software. Before Update works for my purposes. I will continue to use
it. Data integrity is not compromised. The database works smoothly and
quickly. If it is "inefficient" it is so on a level that is not
important to me.

"Miss N E Person ID=1 of Main Street and Miss N E Person ID=2 of Main
Street are different people". Then I will figure out a way of telling
them apart that is useful to the person who needs to call or contact
one or the other. Knowing that they are different records in the
database (because they have different ID numbers) is not helpful in
telling them apart.

wrote in message
oups.com...

BruceM wrote:
I think I understand that your PK and your field(s) on the one side
of
one-to-many may not be the same. If so, and if the PK is not part of
a
relationship, would the purpose of your PK be to guard against
duplication?

No, it would be the 'field(s) on the one side of one-to-many' that
would guard against duplication. The purpose of the PK would be to
avoid a performance-degrading clustered index and, if possible, to
provide for a performance-enhancing clustered index.

If so, do you regard that as a more efficient use of recources than
data
validation code in the form's Before Update event?

I do not rely on front end applications to enforce data integrity and
would strongly discourage such development. If I have a business rule
which Jet cannot enforce via constraints then I would recommend
porting
to MSDE. For me, 'efficiency' doesn't come anything close to data
integrity in terms of importance.

Also, if by "expose it" (in reference to an artificial key) you mean
show it
to the user, why would that be necessary?

You know Miss N E Person ID=1 of Main Street and Miss N E Person ID=2
of Main Street are different people, but when you speak to one of them
on the phone, how do *they* tell you which one they are?











  #49  
Old September 27th, 2005, 01:59 PM
external usenet poster
 
Posts: n/a
Default


BruceM wrote:
To clarify, I am not going to use a multi-field PK when there are other (and
simpler) means to guarantee uniqueness. If I have PartNumber and Process
(plating, welding, etc.) it makes sense to combine the two, since the
combination of Blade and Plating should appear just once.


I'll try one last attempt at getting the message across. Use your
multi-field PK to build the clustered index. The clustered index is
for that table and that table alone. Use your simpler 'PartNumber and
Process' in the FOREIGN KEY relationship. If it isn't already,
constrain 'PartNumber and Process' with NOT NULL UNIQUE. Remember that
you can have many NOT NULL UNIQUE constraints in a table but only one
clustered index (=PK)

It has a lot to do with reading what others have written here (people who
offer a lot pragmatic and practical advice, and who have demonstrated
again and again their command of the program)


Ask yourself: do these other people recommend an autonumber as PRIMARY
KEY in the knowledge that it creates a clustered index (physical
ordering)? I've yet to hear anyone here say, 'Yes, autonumber makes for
a fine clustered index.'

  #50  
Old September 27th, 2005, 02:41 PM
external usenet poster
 
Posts: n/a
Default


Roger Carlson wrote:
data type = INTEGER


autonumbers are Long Integer, not Integer


Have you heard of the expression, "Win win"?

I use the uppercase INTEGER when referring to the Jet data type (see
http://office.microsoft.com/en-us/as...322291033.aspx) which
coincides nicely with the ANSI data type.

It's not about losing. Open you mind and you may learn something.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting a table by concatenating several fields in the same table salsaguy Running & Setting Up Queries 3 March 6th, 2005 09:41 PM
Sorting a table by concatenating several fields in the same table salsaguy Running & Setting Up Queries 0 March 6th, 2005 02:33 AM
Additional fields for form based parameter query/null fields geeksdoitbetter Running & Setting Up Queries 2 January 7th, 2005 11:05 PM
Selecting Fields for Update Steve Daigler Page Layout 4 October 15th, 2004 02:13 PM
My tables lost their AutoNumber fields Bill Nicholson Database Design 2 July 2nd, 2004 02:20 AM


All times are GMT +1. The time now is 01:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.