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
  #31  
Old September 26th, 2005, 05:30 PM
Roger Carlson
external usenet poster
 
Posts: n/a
Default


wrote in message
oups.com...

Roger Carlson wrote:
Autonumber fields make excellent Primary Keys.


You've misunderstood what PRIMARY KEY means. An unique integer which
has no meaning in respect fo the entities being modelled makes a lousy
PRIMARY KEY. Google for "clustered index" in the Access groups.

An autonumber is a convenient uniqueifier but unquieness for its own
sake make not be such a good thing.



  #33  
Old September 26th, 2005, 06:20 PM
Rick Brandt
external usenet poster
 
Posts: n/a
Default

BruceM wrote:
"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. [snip]


The basic argument is that if you enforce rules via your front end then the
rules are ONLY enforced in your front end. If someone else created another
front end linked to your tables then the rules would not be enforced. Or if
you yourself end up creating another front end or form to edit the same
tables you have to replicate your "code rules" over and over each time. If
the rules are enforced by the database engine then they are written once and
are automatically enforced in all situations.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com



  #34  
Old September 26th, 2005, 07:09 PM
BruceM
external usenet poster
 
Posts: n/a
Default

I see. Thanks for pointing it out. I am in a situation where I am pretty
much the only one working on database design, and my projects have all been
on a rather modest scale. I had not considered the problems of somebody
else making another front end linked to the existing tables, because it
isn't going to happen in the current scheme of things. I can see where it
would definitely be worth considering in other situations.
I am still not sure what I would do on a table level in a situation where a
field is required only if another field is filled in. I guess that's where
that MSDE business comes in (if one is to avoid front-end validation rules).

"Rick Brandt" wrote in message
. ..
BruceM wrote:
"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. [snip]


The basic argument is that if you enforce rules via your front end then
the
rules are ONLY enforced in your front end. If someone else created
another
front end linked to your tables then the rules would not be enforced. Or
if
you yourself end up creating another front end or form to edit the same
tables you have to replicate your "code rules" over and over each time.
If
the rules are enforced by the database engine then they are written once
and
are automatically enforced in all situations.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com





  #35  
Old September 26th, 2005, 08:11 PM
Amy Blankenship
external usenet poster
 
Posts: n/a
Default

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?





  #36  
Old September 26th, 2005, 08:36 PM
BruceM
external usenet poster
 
Posts: n/a
Default

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?







  #37  
Old September 26th, 2005, 08:54 PM
external usenet poster
 
Posts: n/a
Default


Roger Carlson wrote:
I don't recall talking about clustered indexes at all.


That's exactly where you are going wrong. For Access/Jet, you need to
start thinking in terms of PK = clustered index.

I also DO
NOT
believe that a clustered index is a requirement for a primary key.


In Access/Jet, you get a clustered index with every PK, whether you
like it or not. That's why you need to choose your PK carefully.

Certainly, EF Codd said nothing about it. This is an implementation issue,
not a design issue.


Your knowledge of database theory seems to be outdated. Some recycling:

Why are we only allowed one PRIMARY KEY per table? Allow me to quote my
old pal Joe Celko:

"In the first papers that Dr. Codd wrote, he talked about
candidate keys -- all the possible keys that exist in a table. Then
you
were to pick one of them to be called the PRIMARY KEY.

"Frankly, this was a hold-over from the days of sequential files --
hey,
Dr. Codd did not come up with the whole RDBMS model all at once. When
we did EDP (Electronic Data Processing) on magnetic tapes in the 1950's

and 1960's, the typical application merged tapes together, so both the
master tape and the transaction tapes had to be sorted on the same key
(account numbers, or whatever). You do not do random seeks on a
magnetic tape.

"Very quickly, The Good Doctor realized that a key is a key, and giving
a
special name to one of them changes nothing about its nature. Primary
keys were then dropped from database theory.

"However, System R and SQL software had been built on top of old file
systems and Dr. Codd's first papers. The PRIMARY KEY was implemented
using the existing keys and indexing methods in the old file systems.
And it has stayed there since.

"Some SQL systems assumed that the PRIMARY KEY would be the preferred
access path and optimized for it."

This last sentence is significant. The question now is: What special
meaning was given to PRIMARY KEY in the Jet implementation of SQL? To
cut a long story short, I'll give you the answer: clustered indexes
i.e. physical ordering on disk. You can only have one physical order
(think paper copy telephone directory: its physical order is fixed)
hence only one PRIMARY KEY. In Jet there is no other way of specifying
the physical ordering for than to use PRIMARY KEY.

If you are using PRIMARY KEY to merely mean NOT NULL UNIQUE then you
are under-utilizing it at best. If you designate a sole autonumber as
PRIMARY KEY you are certainly using it incorrectly because an
incrementing integer (worse, random GUID) makes for a lousy physical
order (think paper copy telephone directory ordered on telephone number
when your primary usage is to retrieve data by last name then first
name). Choosing a bad PRIMARY KEY can result in placing a performance
hit on your database.

If you decide (and I urge you to resist doing so) to use an autonumber
(ID) to force your rows to be unique for uniqueness' sake where you
have no natural key, then put it last in your PRIMARY KEY definition
and put the columns for your clustered index in appropriate order first
e.g.

PRIMARY KEY (last_name, first_name, ID)

This way, the physical order for the table will be rebuilt in last_name
then first_name order with ID merely to satisfy the uniqueness
requirement.

If you are using autonumber as an artificial/surrogate key on
performance grounds e.g. on the basis that a compound natural key is
less efficient for table joins etc, then NOT NULL UNIQUE is sufficient.
But to use an autonumber for efficientcy then take a performance hit by
making it PRIMARY KEY makes no sense!

  #38  
Old September 26th, 2005, 09:00 PM
Roger Carlson
external usenet poster
 
Posts: n/a
Default

The physical ordering of the records is NOT the most important database
design issue. I've used clustered indexes profitably in SQL Server and in
fact did not know you could create a clustered index in Access. (I never
claimed to know everything.) But a clustered index is not the
be-all-and-end-all of database design. I doubt very much if a custered
index on 3 text fields like LastName, FirstName, Address will be more
efficient than a non-clustered index on an autonumber field. But even if it
is, if the database is that big and performance is such an issue, you should
move up to SQL server anyway.

I have no more time to waste on arguing with you. If it pleases you to
believe you have won, then do so.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


wrote in message
ups.com...

Roger Carlson wrote:
I don't recall talking about clustered indexes at all.


That's exactly where you are going wrong. For Access/Jet, you need to
start thinking in terms of PK = clustered index.

I also DO
NOT
believe that a clustered index is a requirement for a primary key.


In Access/Jet, you get a clustered index with every PK, whether you
like it or not. That's why you need to choose your PK carefully.

Certainly, EF Codd said nothing about it. This is an implementation

issue,
not a design issue.


Your knowledge of database theory seems to be outdated. Some recycling:

Why are we only allowed one PRIMARY KEY per table? Allow me to quote my
old pal Joe Celko:

"In the first papers that Dr. Codd wrote, he talked about
candidate keys -- all the possible keys that exist in a table. Then
you
were to pick one of them to be called the PRIMARY KEY.

"Frankly, this was a hold-over from the days of sequential files --
hey,
Dr. Codd did not come up with the whole RDBMS model all at once. When
we did EDP (Electronic Data Processing) on magnetic tapes in the 1950's

and 1960's, the typical application merged tapes together, so both the
master tape and the transaction tapes had to be sorted on the same key
(account numbers, or whatever). You do not do random seeks on a
magnetic tape.

"Very quickly, The Good Doctor realized that a key is a key, and giving
a
special name to one of them changes nothing about its nature. Primary
keys were then dropped from database theory.

"However, System R and SQL software had been built on top of old file
systems and Dr. Codd's first papers. The PRIMARY KEY was implemented
using the existing keys and indexing methods in the old file systems.
And it has stayed there since.

"Some SQL systems assumed that the PRIMARY KEY would be the preferred
access path and optimized for it."

This last sentence is significant. The question now is: What special
meaning was given to PRIMARY KEY in the Jet implementation of SQL? To
cut a long story short, I'll give you the answer: clustered indexes
i.e. physical ordering on disk. You can only have one physical order
(think paper copy telephone directory: its physical order is fixed)
hence only one PRIMARY KEY. In Jet there is no other way of specifying
the physical ordering for than to use PRIMARY KEY.

If you are using PRIMARY KEY to merely mean NOT NULL UNIQUE then you
are under-utilizing it at best. If you designate a sole autonumber as
PRIMARY KEY you are certainly using it incorrectly because an
incrementing integer (worse, random GUID) makes for a lousy physical
order (think paper copy telephone directory ordered on telephone number
when your primary usage is to retrieve data by last name then first
name). Choosing a bad PRIMARY KEY can result in placing a performance
hit on your database.

If you decide (and I urge you to resist doing so) to use an autonumber
(ID) to force your rows to be unique for uniqueness' sake where you
have no natural key, then put it last in your PRIMARY KEY definition
and put the columns for your clustered index in appropriate order first
e.g.

PRIMARY KEY (last_name, first_name, ID)

This way, the physical order for the table will be rebuilt in last_name
then first_name order with ID merely to satisfy the uniqueness
requirement.

If you are using autonumber as an artificial/surrogate key on
performance grounds e.g. on the basis that a compound natural key is
less efficient for table joins etc, then NOT NULL UNIQUE is sufficient.
But to use an autonumber for efficientcy then take a performance hit by
making it PRIMARY KEY makes no sense!



  #39  
Old September 26th, 2005, 09:04 PM
external usenet poster
 
Posts: n/a
Default


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

  #40  
Old September 26th, 2005, 09:36 PM
Amy Blankenship
external usenet poster
 
Posts: n/a
Default

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?









 




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 12:42 PM.


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