Has anyone heard of a way to use the record number to a field in the 
form?  I am trying to find a way to not use the autonumber and have been 
trying to figure out how I can use the record number from the record 
selector as my NumID.  Any ideas?  Thanks.  Mikki

You should use an autonumber instead.  The record number is not 
suitable as
an ID since it will change dynamically depending upon filters, sort 
orders,
etc.

Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx

-----Original Message-----
From: Mikki.Maxwell@a... [mailto:Mikki.Maxwell@a...]
Sent: Thursday, September 26, 2002 6:00 PM
To: Access
Subject: [access] Use record number to create an ID number

Has anyone heard of a way to use the record number to a field in the
form?  I am trying to find a way to not use the autonumber and have 
been
trying to figure out how I can use the record number from the record
selector as my NumID.  Any ideas?  Thanks.  Mikki

While Rand does make a good point - there is a function you might want to use which acts like 
the Autonumber function, but if you delete records with Autonumber fields, you find that the 
Autonumber increments from the old records.
So, say you had 3000 records in a table, then deleted the records, then added some more, 
you'd get the autonumber starting at 3001 - which (I'm assuming) is one of the reasons you 
don't want to use AutoNumber.
All that said - if you only allow users to enter data through forms (Through RWOP or any other 
method you happen to have, or if that's all you trust your users will do) THEN you can just 
have a standard numberfield for the primary key, whether it be Long Integer, Double, 
whatever, with the following expression as the Default Value for the Primary Key field on the 
form:

=DMax("[Field1]","Table1")+1

But a warning with this expression - don't use it if you ever plan on replicating the database - 
it won't work properly. If you will only ever have one instance of the database, then it works 
fine. You might want to also have some Cascade Update relationships on tables that have this 
scenario too.

Steven

Hi all,

Since it appears that we're revisiting the "Should you always use
autonumbers as primary keys?" debate for a moment, I wanted to share
a comment posted by Daniel Walker of Wrox Press on this board in 
February of 2001. This comment helped me see the light, and has spared me
untold suffering in the design process. I always use autonumbers as 
primary keys and would never consider doing otherwise. Read on:

**************

What you describe as "natural" primary keys are actually known as semantic 
keys in database work. A semantic key is a value from the real world which 
appears to identfy a record in a unique way - a National Insurance number 
in an employee database, for instance. Everyone has an NI value, and no 
one 
can share it, right? Right? Reality always throws up exceptions. Say you 
employ a foreign worker on a temporary visa, for example - no NI No., 
suddenly no primary key.

Even in the best of worlds, you now find yourself _inventing_ imanginary-
real values for these records - which is only cool until someone forgets 
that it's an imaginary value and fills out an NI return for the guy. In the
worst of worlds, your DB screws up and starts applying the same NI value to
all the overseas workers, or you end up hiring someone who _actually_has_
the NI no. you just applied to Mr Temp... And then the boss goes and open 
a Paris office!

Semantic keys are a fundamentally bad idea in database design and should
never be used. The database is for _storing_ data. Using that data to do 
the work of the database itself, is like using the books to hold the 
shelves up: looks like a bookshelf, but is actually as useful as a choloate
teapot. Always use an autonumber field as the primary key. You can
always hide it, if it is likely to confuse the users of the database.

Daniel Walker
Wrox Press

**************

Here Here!

One that has always helped me:

"Primary Keys Should Never Be Seen.  They are there to uniquely identify
records and relate tables. Nothing more. Nothing less."

(from a combination of sources)    :=)) 

Cheers,
Andrew

-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Friday, 27 September 2002 12:52 PM
To: Access
Subject: [access] Re: Use record number to create an ID number


Hi all,

Since it appears that we're revisiting the "Should you always use
autonumbers as primary keys?" debate for a moment, I wanted to share
a comment posted by Daniel Walker of Wrox Press on this board in 
February of 2001. This comment helped me see the light, and has spared me
untold suffering in the design process. I always use autonumbers as 
primary keys and would never consider doing otherwise. Read on:

**************

What you describe as "natural" primary keys are actually known as semantic 
keys in database work. A semantic key is a value from the real world which 
appears to identfy a record in a unique way - a National Insurance number 
in an employee database, for instance. Everyone has an NI value, and no 
one 
can share it, right? Right? Reality always throws up exceptions. Say you 
employ a foreign worker on a temporary visa, for example - no NI No., 
suddenly no primary key.

Even in the best of worlds, you now find yourself _inventing_ imanginary-
real values for these records - which is only cool until someone forgets 
that it's an imaginary value and fills out an NI return for the guy. In the
worst of worlds, your DB screws up and starts applying the same NI value to
all the overseas workers, or you end up hiring someone who _actually_has_
the NI no. you just applied to Mr Temp... And then the boss goes and open 
a Paris office!

Semantic keys are a fundamentally bad idea in database design and should
never be used. The database is for _storing_ data. Using that data to do 
the work of the database itself, is like using the books to hold the 
shelves up: looks like a bookshelf, but is actually as useful as a choloate
teapot. Always use an autonumber field as the primary key. You can
always hide it, if it is likely to confuse the users of the database.

Daniel Walker
Wrox Press

**************

Thanks Bob for re posting the post.  But I have a question in mind, what if
you have this field that you think that is also non repeating like for example
a receipt no (01234) may it be manually or programmatically entered.  Is this still
not good enough to be your primary key?  And also what if this field needs
to be in series and no jumping of numbers?

Thanks,

Enzo

-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Friday, September 27, 2002 3:22 AM
To: Access
Subject: [access] Re: Use record number to create an ID number


Hi all,

Since it appears that we're revisiting the "Should you always use
autonumbers as primary keys?" debate for a moment, I wanted to share
a comment posted by Daniel Walker of Wrox Press on this board in 
February of 2001. This comment helped me see the light, and has spared me
untold suffering in the design process. I always use autonumbers as 
primary keys and would never consider doing otherwise. Read on:

**************

What you describe as "natural" primary keys are actually known as semantic 
keys in database work. A semantic key is a value from the real world which 
appears to identfy a record in a unique way - a National Insurance number 
in an employee database, for instance. Everyone has an NI value, and no 
one 
can share it, right? Right? Reality always throws up exceptions. Say you 
employ a foreign worker on a temporary visa, for example - no NI No., 
suddenly no primary key.

Even in the best of worlds, you now find yourself _inventing_ imanginary-
real values for these records - which is only cool until someone forgets 
that it's an imaginary value and fills out an NI return for the guy. In the
worst of worlds, your DB screws up and starts applying the same NI value to
all the overseas workers, or you end up hiring someone who _actually_has_
the NI no. you just applied to Mr Temp... And then the boss goes and open 
a Paris office!

Semantic keys are a fundamentally bad idea in database design and should
never be used. The database is for _storing_ data. Using that data to do 
the work of the database itself, is like using the books to hold the 
shelves up: looks like a bookshelf, but is actually as useful as a choloate
teapot. Always use an autonumber field as the primary key. You can
always hide it, if it is likely to confuse the users of the database.

Daniel Walker
Wrox Press

**************

The primary key should still be an autonumber but to enforce that field
being unique just create an index on it with duplicates not allowed.  Of
course then you will have to deal with the errors that causes if something
violates the uniqueness.

|-----Original Message-----
|From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
|Sent: Friday, September 27, 2002 12:39 AM
|To: Access
|Subject: [access] Re: Use record number to create an ID number
|
|
|
|Thanks Bob for re posting the post.  But I have a question in mind, what if
|you have this field that you think that is also non repeating like
|for example
|a receipt no (01234) may it be manually or programmatically
|entered.  Is this still
|not good enough to be your primary key?  And also what if this field needs
|to be in series and no jumping of numbers?
|
|Thanks,
|
|Enzo
|
|-----Original Message-----
|From: Bob Bedell [mailto:bobbedell15@m...]
|Sent: Friday, September 27, 2002 3:22 AM
|To: Access
|Subject: [access] Re: Use record number to create an ID number
|
|
|Hi all,
|
|Since it appears that we're revisiting the "Should you always use
|autonumbers as primary keys?" debate for a moment, I wanted to share
|a comment posted by Daniel Walker of Wrox Press on this board in
|February of 2001. This comment helped me see the light, and has spared me
|untold suffering in the design process. I always use autonumbers as
|primary keys and would never consider doing otherwise. Read on:
|
|**************
|
|What you describe as "natural" primary keys are actually known as semantic
|keys in database work. A semantic key is a value from the real world which
|appears to identfy a record in a unique way - a National Insurance number
|in an employee database, for instance. Everyone has an NI value, and no
|one
|can share it, right? Right? Reality always throws up exceptions. Say you
|employ a foreign worker on a temporary visa, for example - no NI No.,
|suddenly no primary key.
|
|Even in the best of worlds, you now find yourself _inventing_ imanginary-
|real values for these records - which is only cool until someone forgets
|that it's an imaginary value and fills out an NI return for the guy. In the
|worst of worlds, your DB screws up and starts applying the same NI value to
|all the overseas workers, or you end up hiring someone who _actually_has_
|the NI no. you just applied to Mr Temp... And then the boss goes and open
|a Paris office!
|
|Semantic keys are a fundamentally bad idea in database design and should
|never be used. The database is for _storing_ data. Using that data to do
|the work of the database itself, is like using the books to hold the
|shelves up: looks like a bookshelf, but is actually as useful as a choloate
|teapot. Always use an autonumber field as the primary key. You can
|always hide it, if it is likely to confuse the users of the database.
|
|Daniel Walker
|Wrox Press
|
|**************
|
|
|
|
|

	Thanks Leo for the comment!  Little by little it's coming to my mind
autonumbers is always d primary key no matter what.

Thanks,

Enzo :)

-----Original Message-----
From: Leo Scott [mailto:leoscott@c...]
Sent: Friday, September 27, 2002 4:33 PM
To: Access
Subject: [access] Re: Use record number to create an ID number


The primary key should still be an autonumber but to enforce that field
being unique just create an index on it with duplicates not allowed.  Of
course then you will have to deal with the errors that causes if something
violates the uniqueness.

|-----Original Message-----
|From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
|Sent: Friday, September 27, 2002 12:39 AM
|To: Access
|Subject: [access] Re: Use record number to create an ID number
|
|
|
|Thanks Bob for re posting the post.  But I have a question in mind, what if
|you have this field that you think that is also non repeating like
|for example
|a receipt no (01234) may it be manually or programmatically
|entered.  Is this still
|not good enough to be your primary key?  And also what if this field needs
|to be in series and no jumping of numbers?
|
|Thanks,
|
|Enzo
|
|-----Original Message-----
|From: Bob Bedell [mailto:bobbedell15@m...]
|Sent: Friday, September 27, 2002 3:22 AM
|To: Access
|Subject: [access] Re: Use record number to create an ID number
|
|
|Hi all,
|
|Since it appears that we're revisiting the "Should you always use
|autonumbers as primary keys?" debate for a moment, I wanted to share
|a comment posted by Daniel Walker of Wrox Press on this board in
|February of 2001. This comment helped me see the light, and has spared me
|untold suffering in the design process. I always use autonumbers as
|primary keys and would never consider doing otherwise. Read on:
|
|**************
|
|What you describe as "natural" primary keys are actually known as semantic
|keys in database work. A semantic key is a value from the real world which
|appears to identfy a record in a unique way - a National Insurance number
|in an employee database, for instance. Everyone has an NI value, and no
|one
|can share it, right? Right? Reality always throws up exceptions. Say you
|employ a foreign worker on a temporary visa, for example - no NI No.,
|suddenly no primary key.
|
|Even in the best of worlds, you now find yourself _inventing_ imanginary-
|real values for these records - which is only cool until someone forgets
|that it's an imaginary value and fills out an NI return for the guy. In the
|worst of worlds, your DB screws up and starts applying the same NI value to
|all the overseas workers, or you end up hiring someone who _actually_has_
|the NI no. you just applied to Mr Temp... And then the boss goes and open
|a Paris office!
|
|Semantic keys are a fundamentally bad idea in database design and should
|never be used. The database is for _storing_ data. Using that data to do
|the work of the database itself, is like using the books to hold the
|shelves up: looks like a bookshelf, but is actually as useful as a choloate
|teapot. Always use an autonumber field as the primary key. You can
|always hide it, if it is likely to confuse the users of the database.
|
|Daniel Walker
|Wrox Press
|
|**************
|
|
|
|
|

> Has anyone heard of a way to use the record number to a field in the 
f> orm?  I am trying to find a way to not use the autonumber and have been 
t> rying to figure out how I can use the record number from the record 
s> elector as my NumID.  Any ideas?  Thanks.  Mikki

Thanks for all of the input.  What I am looking at is the possibility of 
the backend being moved to a SQL Server which does not have autonumber.  
My effort is to prevent having to make changes later.  Any thoughts...  
Thanks again.  Mikki

I fully agree with all of this, but I would like to add that understanding
your 'semantic keys', and your data overall, is crucial to creating a good
design (I almost said "key to creating..." haha).  The semantic keys should
be identified as part of the data modeling process, in the logical model,
even if sequential numeric keys are assigned for the physical model and
construction.

When it's time to build a GUI for your system, use of the semantic keys is
very appropriate for lookups and identifiers because the users will
understand them immediately.  That is, if your system allows lookups by
Social Security number, any user will get that immediately (in the USA
anyway).  But if you use "Customer ID Number" that may work just as well and
is almost as easy to understand, but what does a CID look like? How do I
know that number from any other number?  It's not that big a difference but
it can be the difference between a good design and an excellent design.

Thanks Bob I think I'll save that off to my "good advice" folder...

-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Thursday, September 26, 2002 10:22 PM
To: Access
Subject: [access] Re: Use record number to create an ID number


Hi all,

Since it appears that we're revisiting the "Should you always use
autonumbers as primary keys?" debate for a moment, I wanted to share
a comment posted by Daniel Walker of Wrox Press on this board in 
February of 2001. This comment helped me see the light, and has spared me
untold suffering in the design process. I always use autonumbers as 
primary keys and would never consider doing otherwise. Read on:

**************

What you describe as "natural" primary keys are actually known as semantic 
keys in database work. A semantic key is a value from the real world which 
appears to identfy a record in a unique way - a National Insurance number 
in an employee database, for instance. Everyone has an NI value, and no 
one 
can share it, right? Right? Reality always throws up exceptions. Say you 
employ a foreign worker on a temporary visa, for example - no NI No., 
suddenly no primary key.

Even in the best of worlds, you now find yourself _inventing_ imanginary-
real values for these records - which is only cool until someone forgets 
that it's an imaginary value and fills out an NI return for the guy. In the
worst of worlds, your DB screws up and starts applying the same NI value to
all the overseas workers, or you end up hiring someone who _actually_has_
the NI no. you just applied to Mr Temp... And then the boss goes and open 
a Paris office!

Semantic keys are a fundamentally bad idea in database design and should
never be used. The database is for _storing_ data. Using that data to do 
the work of the database itself, is like using the books to hold the 
shelves up: looks like a bookshelf, but is actually as useful as a choloate
teapot. Always use an autonumber field as the primary key. You can
always hide it, if it is likely to confuse the users of the database.

Daniel Walker
Wrox Press

**************

if you have a requirement like "the field cannot skip any values in the
number sequence" then it is NOT a abstract unique ID, it is a data field
with business rules that only LOOKS like a sequential key ;)

-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 27, 2002 2:39 AM
To: Access
Subject: [access] Re: Use record number to create an ID number



Thanks Bob for re posting the post.  But I have a question in mind, what if
you have this field that you think that is also non repeating like for
example
a receipt no (01234) may it be manually or programmatically entered.  Is
this still
not good enough to be your primary key?  And also what if this field needs
to be in series and no jumping of numbers?

Thanks,

Enzo

-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Friday, September 27, 2002 3:22 AM
To: Access
Subject: [access] Re: Use record number to create an ID number


Hi all,

Since it appears that we're revisiting the "Should you always use
autonumbers as primary keys?" debate for a moment, I wanted to share
a comment posted by Daniel Walker of Wrox Press on this board in 
February of 2001. This comment helped me see the light, and has spared me
untold suffering in the design process. I always use autonumbers as 
primary keys and would never consider doing otherwise. Read on:

**************

What you describe as "natural" primary keys are actually known as semantic 
keys in database work. A semantic key is a value from the real world which 
appears to identfy a record in a unique way - a National Insurance number 
in an employee database, for instance. Everyone has an NI value, and no 
one 
can share it, right? Right? Reality always throws up exceptions. Say you 
employ a foreign worker on a temporary visa, for example - no NI No., 
suddenly no primary key.

Even in the best of worlds, you now find yourself _inventing_ imanginary-
real values for these records - which is only cool until someone forgets 
that it's an imaginary value and fills out an NI return for the guy. In the
worst of worlds, your DB screws up and starts applying the same NI value to
all the overseas workers, or you end up hiring someone who _actually_has_
the NI no. you just applied to Mr Temp... And then the boss goes and open 
a Paris office!

Semantic keys are a fundamentally bad idea in database design and should
never be used. The database is for _storing_ data. Using that data to do 
the work of the database itself, is like using the books to hold the 
shelves up: looks like a bookshelf, but is actually as useful as a choloate
teapot. Always use an autonumber field as the primary key. You can
always hide it, if it is likely to confuse the users of the database.

Daniel Walker
Wrox Press

**************

Mikki,

You are correct that SQL Server does not list "autonumber" as a data
type. However, the SQL Server equivalent for the Access autonumber is
achieved by setting the Identity property of an Integer datatype
field to "yes". This will force the field to be used as an identity
column and automatically increment values stored in that field.

Strictly speaking, the Access autonumber is not a data type, and really
shouldn't be referred to as such. It is actually a property setting for
a Long Integer data type field. Access hides that extra bit of
complexity and simplifies what in SQL Server is a two step process:
defining the fields data type and setting it's identity property.

>From: Mikki.Maxwell@a...
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Use record number to create an ID number
>Date: Fri, 27 Sep 2002 15:15:22
>
>Thanks for all of the input.  What I am looking at is the possibility of
>the backend being moved to a SQL Server which does not have autonumber.
>My effort is to prevent having to make changes later.  Any thoughts...
>Thanks again.  Mikki




_________________________________________________________________
Join the world?s largest e-mail service with MSN Hotmail. 
http://www.hotmail.com

SQL Server does have an IDENTITY field which is effectively an AUTONUMBER
datatype




                                                                                          
                    Mikki.Maxwell@                                                        
                    amlife.com            To:     "Access" <access@p...>          
                                          cc:                                             
                    27/09/2002            Subject:     [access] Re: Use record number to  
                    15:15                 create an ID number                             
                    Please respond                                                        
                    to "Access"                                                           
                                                                                          
                                                                                          




Thanks for all of the input.  What I am looking at is the possibility of
the backend being moved to a SQL Server which does not have autonumber.
My effort is to prevent having to make changes later.  Any thoughts...
Thanks again.  Mikki



*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************

Ummmm....

yes it does

it's called IDENTITY

it works just like Access autonumber, but is easier to manipulate values
when you need to.



-----Original Message-----
From: Mikki.Maxwell@a... [mailto:Mikki.Maxwell@a...]
Sent: Friday, September 27, 2002 10:15 AM
To: Access
Subject: [access] Re: Use record number to create an ID number


Thanks for all of the input.  What I am looking at is the possibility of 
the backend being moved to a SQL Server which does not have autonumber.  
My effort is to prevent having to make changes later.  Any thoughts...  
Thanks again.  Mikki

Hi Enzo,

I'm a little nervous about "always".  There are some exceptions.  If 
you
have multiple users creating new entries at multiple locations with
replicated database you can have problems with key duplication.  In 
that
case you will need to use a GUID as the key.

One more point - if you do use autonumber keys and want to maintain the
sequence without gaps - you will need to prohibit record deletions.   
This
can be of use when you wish to have an audit trail on records.

Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx

-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 27, 2002 4:40 AM
To: Access
Subject: [access] Re: Use record number to create an ID number


        Thanks Leo for the comment!  Little by little it's coming to my 
mind
autonumbers is always d primary key no matter what.

Thanks,

Enzo :)

-----Original Message-----
From: Leo Scott [mailto:leoscott@c...]
Sent: Friday, September 27, 2002 4:33 PM
To: Access
Subject: [access] Re: Use record number to create an ID number


The primary key should still be an autonumber but to enforce that field
being unique just create an index on it with duplicates not allowed.  
Of
course then you will have to deal with the errors that causes if 
something
violates the uniqueness.

|-----Original Message-----
|From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
|Sent: Friday, September 27, 2002 12:39 AM
|To: Access
|Subject: [access] Re: Use record number to create an ID number
|
|
|
|Thanks Bob for re posting the post.  But I have a question in mind, 
what if
|you have this field that you think that is also non repeating like
|for example
|a receipt no (01234) may it be manually or programmatically
|entered.  Is this still
|not good enough to be your primary key?  And also what if this field 
needs
|to be in series and no jumping of numbers?
|
|Thanks,
|
|Enzo
|
|-----Original Message-----
|From: Bob Bedell [mailto:bobbedell15@m...]
|Sent: Friday, September 27, 2002 3:22 AM
|To: Access
|Subject: [access] Re: Use record number to create an ID number
|
|
|Hi all,
|
|Since it appears that we're revisiting the "Should you always use
|autonumbers as primary keys?" debate for a moment, I wanted to share
|a comment posted by Daniel Walker of Wrox Press on this board in
|February of 2001. This comment helped me see the light, and has spared 
me
|untold suffering in the design process. I always use autonumbers as
|primary keys and would never consider doing otherwise. Read on:
|
|**************
|
|What you describe as "natural" primary keys are actually known as 
semantic
|keys in database work. A semantic key is a value from the real world 
which
|appears to identfy a record in a unique way - a National Insurance 
number
|in an employee database, for instance. Everyone has an NI value, and 
no
|one
|can share it, right? Right? Reality always throws up exceptions. Say 
you
|employ a foreign worker on a temporary visa, for example - no NI No.,
|suddenly no primary key.
|
|Even in the best of worlds, you now find yourself _inventing_ 
imanginary-
|real values for these records - which is only cool until someone 
forgets
|that it's an imaginary value and fills out an NI return for the guy. 
In the
|worst of worlds, your DB screws up and starts applying the same NI 
value to
|all the overseas workers, or you end up hiring someone who 
_actually_has_
|the NI no. you just applied to Mr Temp... And then the boss goes and 
open
|a Paris office!
|
|Semantic keys are a fundamentally bad idea in database design and 
should
|never be used. The database is for _storing_ data. Using that data to 
do
|the work of the database itself, is like using the books to hold the
|shelves up: looks like a bookshelf, but is actually as useful as a 
choloate
|teapot. Always use an autonumber field as the primary key. You can
|always hide it, if it is likely to confuse the users of the database.
|
|Daniel Walker
|Wrox Press
|
|**************
|
|
|
|
|

Hi Enzo,

The best I can do in answering your question is share a bit of my own
experience to date.

The bulk of the database app development I have done has been,
of all places, in the public sector behavioral health industry,
especially since the advent of managed care. In the vast majority
of cases, clients moving through these systems are attached to
a plethora of "unique" identifiers: modified social security
numbers, Medicaid numbers, state, county and ferederal probation and
parole numbers, department of human services case numbers. The
dulication of these numbers across two or three or four clients happens
with alarming regularity. If one believes, for example, that social
security numbers are "unique", they havn't spent much time around the
Medicaid billing cycle. So I'd be very careful about a "field that
you think is also non-repeating."

I don't have a good answer to the second part of your question because
I've never needed to generate an identifier that had to be sequential
in the way you're describing. In the agencies I've done work for, even
internal client identifiers are appropriated from other systems,
such as state systems.  They're usually taken from some form of
documentation that accompanies the client and is manually input.
That has never struck me as the optimal arrangement. I've always felt
that internally generated client IDs are a good idea and there are
algorithms out there to produce them. I've simply never used one. I
would, however, enjoy hearing from anyone who has, particularly if
they feel like doing a little "unique identifier code generator"
swapping.

Best,

Bob

>From: "Lorenzo Amado Zaragoza" <enzaux@g...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Use record number to create an ID number
>Date: Fri, 27 Sep 2002 15:39:16 +0800
>
>
>Thanks Bob for re posting the post.  But I have a question in mind, what if
>you have this field that you think that is also non repeating like for 
>example
>a receipt no (01234) may it be manually or programmatically entered.  Is 
>this still
>not good enough to be your primary key?  And also what if this field needs
>to be in series and no jumping of numbers?
>
>Thanks,
>
>Enzo
>
>-----Original Message-----
>From: Bob Bedell [mailto:bobbedell15@m...]
>Sent: Friday, September 27, 2002 3:22 AM
>To: Access
>Subject: [access] Re: Use record number to create an ID number
>
>
>Hi all,
>
>Since it appears that we're revisiting the "Should you always use
>autonumbers as primary keys?" debate for a moment, I wanted to share
>a comment posted by Daniel Walker of Wrox Press on this board in
>February of 2001. This comment helped me see the light, and has spared me
>untold suffering in the design process. I always use autonumbers as
>primary keys and would never consider doing otherwise. Read on:
>
>**************
>
>What you describe as "natural" primary keys are actually known as semantic
>keys in database work. A semantic key is a value from the real world which
>appears to identfy a record in a unique way - a National Insurance number
>in an employee database, for instance. Everyone has an NI value, and no
>one
>can share it, right? Right? Reality always throws up exceptions. Say you
>employ a foreign worker on a temporary visa, for example - no NI No.,
>suddenly no primary key.
>
>Even in the best of worlds, you now find yourself _inventing_ imanginary-
>real values for these records - which is only cool until someone forgets
>that it's an imaginary value and fills out an NI return for the guy. In the
>worst of worlds, your DB screws up and starts applying the same NI value to
>all the overseas workers, or you end up hiring someone who _actually_has_
>the NI no. you just applied to Mr Temp... And then the boss goes and open
>a Paris office!
>
>Semantic keys are a fundamentally bad idea in database design and should
>never be used. The database is for _storing_ data. Using that data to do
>the work of the database itself, is like using the books to hold the
>shelves up: looks like a bookshelf, but is actually as useful as a choloate
>teapot. Always use an autonumber field as the primary key. You can
>always hide it, if it is likely to confuse the users of the database.
>
>Daniel Walker
>Wrox Press
>
>**************
>
>
>
>






_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

Hi Rand,

You just shattered my whole primary key world view. I hadn't factored
replication into the equation. Bummer. How would you handle that? Is it
possible to create a GUID field in Access?

>From: "Gerald, Rand" <RGerald@u...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Use record number to create an ID number
>Date: Fri, 27 Sep 2002 09:43:35 -0500
>
>Hi Enzo,
>
>I'm a little nervous about "always".  There are some exceptions.  If you
>have multiple users creating new entries at multiple locations with
>replicated database you can have problems with key duplication.  In that
>case you will need to use a GUID as the key.
>
>One more point - if you do use autonumber keys and want to maintain the
>sequence without gaps - you will need to prohibit record deletions.   This
>can be of use when you wish to have an audit trail on records.
>
>Rand E Gerald
>Database Specialist
>Information Services / Operations
>Bahá'í National Office
>1233 Central St.
>Evanston IL 60201
>(xxx) xxx-xxxx
>
>-----Original Message-----
>From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
>Sent: Friday, September 27, 2002 4:40 AM
>To: Access
>Subject: [access] Re: Use record number to create an ID number
>
>
>         Thanks Leo for the comment!  Little by little it's coming to my 
>mind
>autonumbers is always d primary key no matter what.
>
>Thanks,
>
>Enzo :)
>
>-----Original Message-----
>From: Leo Scott [mailto:leoscott@c...]
>Sent: Friday, September 27, 2002 4:33 PM
>To: Access
>Subject: [access] Re: Use record number to create an ID number
>
>
>The primary key should still be an autonumber but to enforce that field
>being unique just create an index on it with duplicates not allowed.  Of
>course then you will have to deal with the errors that causes if something
>violates the uniqueness.
>
>|-----Original Message-----
>|From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
>|Sent: Friday, September 27, 2002 12:39 AM
>|To: Access
>|Subject: [access] Re: Use record number to create an ID number
>|
>|
>|
>|Thanks Bob for re posting the post.  But I have a question in mind, what 
>if
>|you have this field that you think that is also non repeating like
>|for example
>|a receipt no (01234) may it be manually or programmatically
>|entered.  Is this still
>|not good enough to be your primary key?  And also what if this field needs
>|to be in series and no jumping of numbers?
>|
>|Thanks,
>|
>|Enzo
>|
>|-----Original Message-----
>|From: Bob Bedell [mailto:bobbedell15@m...]
>|Sent: Friday, September 27, 2002 3:22 AM
>|To: Access
>|Subject: [access] Re: Use record number to create an ID number
>|
>|
>|Hi all,
>|
>|Since it appears that we're revisiting the "Should you always use
>|autonumbers as primary keys?" debate for a moment, I wanted to share
>|a comment posted by Daniel Walker of Wrox Press on this board in
>|February of 2001. This comment helped me see the light, and has spared me
>|untold suffering in the design process. I always use autonumbers as
>|primary keys and would never consider doing otherwise. Read on:
>|
>|**************
>|
>|What you describe as "natural" primary keys are actually known as semantic
>|keys in database work. A semantic key is a value from the real world which
>|appears to identfy a record in a unique way - a National Insurance number
>|in an employee database, for instance. Everyone has an NI value, and no
>|one
>|can share it, right? Right? Reality always throws up exceptions. Say you
>|employ a foreign worker on a temporary visa, for example - no NI No.,
>|suddenly no primary key.
>|
>|Even in the best of worlds, you now find yourself _inventing_ imanginary-
>|real values for these records - which is only cool until someone forgets
>|that it's an imaginary value and fills out an NI return for the guy. In 
>the
>|worst of worlds, your DB screws up and starts applying the same NI value 
>to
>|all the overseas workers, or you end up hiring someone who _actually_has_
>|the NI no. you just applied to Mr Temp... And then the boss goes and open
>|a Paris office!
>|
>|Semantic keys are a fundamentally bad idea in database design and should
>|never be used. The database is for _storing_ data. Using that data to do
>|the work of the database itself, is like using the books to hold the
>|shelves up: looks like a bookshelf, but is actually as useful as a 
>choloate
>|teapot. Always use an autonumber field as the primary key. You can
>|always hide it, if it is likely to confuse the users of the database.
>|
>|Daniel Walker
>|Wrox Press
>|
>|**************
>|
>|
>|
>|
>|
>
>
>
>
>
>
>




_________________________________________________________________
MSN Photos is the easiest way to share and print your photos: 
http://photos.msn.com/support/worldwide.aspx

If you have data created in two or more locations and then funnel that 
data
into a central repository, you will need to use a GUID (Globally Unique
Identifier).

Go to your autoNumber field and change the data type from LongInteger 
to
Replication ID.

This will "guarantee?" that all record identifiers are globally unique.
Unfortunately, you will lose sequentiallity.

Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx

-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Friday, September 27, 2002 11:31 AM
To: Access
Subject: [access] Re: Use record number to create an ID number

Hi Rand,

You just shattered my whole primary key world view. I hadn't factored
replication into the equation. Bummer. How would you handle that? Is it
possible to create a GUID field in Access?

>From: "Gerald, Rand" <RGerald@u...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Use record number to create an ID number
>Date: Fri, 27 Sep 2002 09:43:35 -0500
>
>Hi Enzo,
>
>I'm a little nervous about "always".  There are some exceptions.  If 
you
>have multiple users creating new entries at multiple locations with
>replicated database you can have problems with key duplication.  In 
that
>case you will need to use a GUID as the key.
>
>One more point - if you do use autonumber keys and want to maintain 
the
>sequence without gaps - you will need to prohibit record deletions.   
This
>can be of use when you wish to have an audit trail on records.
>
>Rand E Gerald
>Database Specialist
>Information Services / Operations
>Bah=E1'=ED National Office
>1233 Central St.
>Evanston IL 60201
>(xxx) xxx-xxxx
>
>-----Original Message-----
>From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
>Sent: Friday, September 27, 2002 4:40 AM
>To: Access
>Subject: [access] Re: Use record number to create an ID number
>
>
>         Thanks Leo for the comment!  Little by little it's coming to 
my
>mind
>autonumbers is always d primary key no matter what.
>
>Thanks,
>
>Enzo :)
>
>-----Original Message-----
>From: Leo Scott [mailto:leoscott@c...]
>Sent: Friday, September 27, 2002 4:33 PM
>To: Access
>Subject: [access] Re: Use record number to create an ID number
>
>
>The primary key should still be an autonumber but to enforce that 
field
>being unique just create an index on it with duplicates not allowed.  
Of
>course then you will have to deal with the errors that causes if 
something
>violates the uniqueness.
>
>|-----Original Message-----
>|From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
>|Sent: Friday, September 27, 2002 12:39 AM
>|To: Access
>|Subject: [access] Re: Use record number to create an ID number
>|
>|
>|
>|Thanks Bob for re posting the post.  But I have a question in mind, 
what
>if
>|you have this field that you think that is also non repeating like
>|for example
>|a receipt no (01234) may it be manually or programmatically
>|entered.  Is this still
>|not good enough to be your primary key?  And also what if this field 
needs
>|to be in series and no jumping of numbers?
>|
>|Thanks,
>|
>|Enzo
>|
>|-----Original Message-----
>|From: Bob Bedell [mailto:bobbedell15@m...]
>|Sent: Friday, September 27, 2002 3:22 AM
>|To: Access
>|Subject: [access] Re: Use record number to create an ID number
>|
>|
>|Hi all,
>|
>|Since it appears that we're revisiting the "Should you always use
>|autonumbers as primary keys?" debate for a moment, I wanted to share
>|a comment posted by Daniel Walker of Wrox Press on this board in
>|February of 2001. This comment helped me see the light, and has 
spared me
>|untold suffering in the design process. I always use autonumbers as
>|primary keys and would never consider doing otherwise. Read on:
>|
>|**************
>|
>|What you describe as "natural" primary keys are actually known as 
semantic
>|keys in database work. A semantic key is a value from the real world 
which
>|appears to identfy a record in a unique way - a National Insurance 
number
>|in an employee database, for instance. Everyone has an NI value, and 
no
>|one
>|can share it, right? Right? Reality always throws up exceptions. Say 
you
>|employ a foreign worker on a temporary visa, for example - no NI No.,
>|suddenly no primary key.
>|
>|Even in the best of worlds, you now find yourself _inventing_ 
imanginary-
>|real values for these records - which is only cool until someone 
forgets
>|that it's an imaginary value and fills out an NI return for the guy. 
In
>the
>|worst of worlds, your DB screws up and starts applying the same NI 
value
>to
>|all the overseas workers, or you end up hiring someone who 
_actually_has_
>|the NI no. you just applied to Mr Temp... And then the boss goes and 
open
>|a Paris office!
>|
>|Semantic keys are a fundamentally bad idea in database design and 
should
>|never be used. The database is for _storing_ data. Using that data to 
do
>|the work of the database itself, is like using the books to hold the
>|shelves up: looks like a bookshelf, but is actually as useful as a
>choloate
>|teapot. Always use an autonumber field as the primary key. You can
>|always hide it, if it is likely to confuse the users of the database.
>|
>|Daniel Walker
>|Wrox Press
>|
>|**************
>|
>|
>|
>|
>|
>
>
>
>
>
>
>




_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx

Phew!  So even in replication we're still using the genera AutoNumber
as the primary key, only the species of AutoNumber has changed from  a
4 byte Long Integer to a 16 byte GUID (ReplicationID).

----- Original Message -----
From: "Gerald, Rand" <RGerald@u...>
To: "Access" <access@p...>
Sent: Friday, September 27, 2002 12:58 PM
Subject: [access] Re: Use record number to create an ID number


If you have data created in two or more locations and then funnel that data
into a central repository, you will need to use a GUID (Globally Unique
Identifier).

Go to your autoNumber field and change the data type from LongInteger to
Replication ID.

This will "guarantee?" that all record identifiers are globally unique.
Unfortunately, you will lose sequentiallity.

Rand E Gerald
Database Specialist
Information Services / Operations
Bahá'í National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx

-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Friday, September 27, 2002 11:31 AM
To: Access
Subject: [access] Re: Use record number to create an ID number

Hi Rand,

You just shattered my whole primary key world view. I hadn't factored
replication into the equation. Bummer. How would you handle that? Is it
possible to create a GUID field in Access?

>From: "Gerald, Rand" <RGerald@u...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Use record number to create an ID number
>Date: Fri, 27 Sep 2002 09:43:35 -0500
>
>Hi Enzo,
>
>I'm a little nervous about "always".  There are some exceptions.  If you
>have multiple users creating new entries at multiple locations with
>replicated database you can have problems with key duplication.  In that
>case you will need to use a GUID as the key.
>
>One more point - if you do use autonumber keys and want to maintain the
>sequence without gaps - you will need to prohibit record deletions.   This
>can be of use when you wish to have an audit trail on records.
>
>Rand E Gerald
>Database Specialist
>Information Services / Operations
>Bahá'í National Office
>1233 Central St.
>Evanston IL 60201
>(xxx) xxx-xxxx
>
>-----Original Message-----
>From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
>Sent: Friday, September 27, 2002 4:40 AM
>To: Access
>Subject: [access] Re: Use record number to create an ID number
>
>
>         Thanks Leo for the comment!  Little by little it's coming to my
>mind
>autonumbers is always d primary key no matter what.
>
>Thanks,
>
>Enzo :)
>
>-----Original Message-----
>From: Leo Scott [mailto:leoscott@c...]
>Sent: Friday, September 27, 2002 4:33 PM
>To: Access
>Subject: [access] Re: Use record number to create an ID number
>
>
>The primary key should still be an autonumber but to enforce that field
>being unique just create an index on it with duplicates not allowed.  Of
>course then you will have to deal with the errors that causes if something
>violates the uniqueness.
>
>|-----Original Message-----
>|From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
>|Sent: Friday, September 27, 2002 12:39 AM
>|To: Access
>|Subject: [access] Re: Use record number to create an ID number
>|
>|
>|
>|Thanks Bob for re posting the post.  But I have a question in mind, what
>if
>|you have this field that you think that is also non repeating like
>|for example
>|a receipt no (01234) may it be manually or programmatically
>|entered.  Is this still
>|not good enough to be your primary key?  And also what if this field needs
>|to be in series and no jumping of numbers?
>|
>|Thanks,
>|
>|Enzo
>|
>|-----Original Message-----
>|From: Bob Bedell [mailto:bobbedell15@m...]
>|Sent: Friday, September 27, 2002 3:22 AM
>|To: Access
>|Subject: [access] Re: Use record number to create an ID number
>|
>|
>|Hi all,
>|
>|Since it appears that we're revisiting the "Should you always use
>|autonumbers as primary keys?" debate for a moment, I wanted to share
>|a comment posted by Daniel Walker of Wrox Press on this board in
>|February of 2001. This comment helped me see the light, and has spared me
>|untold suffering in the design process. I always use autonumbers as
>|primary keys and would never consider doing otherwise. Read on:
>|
>|**************
>|
>|What you describe as "natural" primary keys are actually known as semantic
>|keys in database work. A semantic key is a value from the real world which
>|appears to identfy a record in a unique way - a National Insurance number
>|in an employee database, for instance. Everyone has an NI value, and no
>|one
>|can share it, right? Right? Reality always throws up exceptions. Say you
>|employ a foreign worker on a temporary visa, for example - no NI No.,
>|suddenly no primary key.
>|
>|Even in the best of worlds, you now find yourself _inventing_ imanginary-
>|real values for these records - which is only cool until someone forgets
>|that it's an imaginary value and fills out an NI return for the guy. In
>the
>|worst of worlds, your DB screws up and starts applying the same NI value
>to
>|all the overseas workers, or you end up hiring someone who _actually_has_
>|the NI no. you just applied to Mr Temp... And then the boss goes and open
>|a Paris office!
>|
>|Semantic keys are a fundamentally bad idea in database design and should
>|never be used. The database is for _storing_ data. Using that data to do
>|the work of the database itself, is like using the books to hold the
>|shelves up: looks like a bookshelf, but is actually as useful as a
>choloate
>|teapot. Always use an autonumber field as the primary key. You can
>|always hide it, if it is likely to confuse the users of the database.
>|
>|Daniel Walker
>|Wrox Press
>|
>|**************
>|
>|
>|
>|
>|
>
>
>
>
>
>
>




_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx

one way to get work around this, if you can safely make certain 
assumptions
about your data, is to start each system at an arbitrarily high 
autonumber
seed, like HQ starts at 1000, FtWorth branch starts at 50,000, Utah 
branch
starts at 100,000...  depends a lot on the particulars of the system if 
you
can get away with something like that, it is not always appropriate.

-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Friday, September 27, 2002 12:17 PM
To: Access
Subject: [access] Re: Use record number to create an ID number


Phew!  So even in replication we're still using the genera AutoNumber
as the primary key, only the species of AutoNumber has changed from  a
4 byte Long Integer to a 16 byte GUID (ReplicationID).

----- Original Message -----
From: "Gerald, Rand" <RGerald@u...>
To: "Access" <access@p...>
Sent: Friday, September 27, 2002 12:58 PM
Subject: [access] Re: Use record number to create an ID number


If you have data created in two or more locations and then funnel that 
data
into a central repository, you will need to use a GUID (Globally Unique
Identifier).

Go to your autoNumber field and change the data type from LongInteger 
to
Replication ID.

This will "guarantee?" that all record identifiers are globally unique.
Unfortunately, you will lose sequentiallity.

Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx

-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Friday, September 27, 2002 11:31 AM
To: Access
Subject: [access] Re: Use record number to create an ID number

Hi Rand,

You just shattered my whole primary key world view. I hadn't factored
replication into the equation. Bummer. How would you handle that? Is it
possible to create a GUID field in Access?

>From: "Gerald, Rand" <RGerald@u...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Use record number to create an ID number
>Date: Fri, 27 Sep 2002 09:43:35 -0500
>
>Hi Enzo,
>
>I'm a little nervous about "always".  There are some exceptions.  If 
you
>have multiple users creating new entries at multiple locations with
>replicated database you can have problems with key duplication.  In 
that
>case you will need to use a GUID as the key.
>
>One more point - if you do use autonumber keys and want to maintain 
the
>sequence without gaps - you will need to prohibit record deletions.   
This
>can be of use when you wish to have an audit trail on records.
>
>Rand E Gerald
>Database Specialist
>Information Services / Operations
>Bah=E1'=ED National Office
>1233 Central St.
>Evanston IL 60201
>(xxx) xxx-xxxx
>
>-----Original Message-----
>From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
>Sent: Friday, September 27, 2002 4:40 AM
>To: Access
>Subject: [access] Re: Use record number to create an ID number
>
>
>         Thanks Leo for the comment!  Little by little it's coming to 
my
>mind
>autonumbers is always d primary key no matter what.
>
>Thanks,
>
>Enzo :)
>
>-----Original Message-----
>From: Leo Scott [mailto:leoscott@c...]
>Sent: Friday, September 27, 2002 4:33 PM
>To: Access
>Subject: [access] Re: Use record number to create an ID number
>
>
>The primary key should still be an autonumber but to enforce that 
field
>being unique just create an index on it with duplicates not allowed.  
Of
>course then you will have to deal with the errors that causes if 
something
>violates the uniqueness.
>
>|-----Original Message-----
>|From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
>|Sent: Friday, September 27, 2002 12:39 AM
>|To: Access
>|Subject: [access] Re: Use record number to create an ID number
>|
>|
>|
>|Thanks Bob for re posting the post.  But I have a question in mind, 
what
>if
>|you have this field that you think that is also non repeating like
>|for example
>|a receipt no (01234) may it be manually or programmatically
>|entered.  Is this still
>|not good enough to be your primary key?  And also what if this field 
needs
>|to be in series and no jumping of numbers?
>|
>|Thanks,
>|
>|Enzo
>|
>|-----Original Message-----
>|From: Bob Bedell [mailto:bobbedell15@m...]
>|Sent: Friday, September 27, 2002 3:22 AM
>|To: Access
>|Subject: [access] Re: Use record number to create an ID number
>|
>|
>|Hi all,
>|
>|Since it appears that we're revisiting the "Should you always use
>|autonumbers as primary keys?" debate for a moment, I wanted to share
>|a comment posted by Daniel Walker of Wrox Press on this board in
>|February of 2001. This comment helped me see the light, and has 
spared me
>|untold suffering in the design process. I always use autonumbers as
>|primary keys and would never consider doing otherwise. Read on:
>|
>|**************
>|
>|What you describe as "natural" primary keys are actually known as 
semantic
>|keys in database work. A semantic key is a value from the real world 
which
>|appears to identfy a record in a unique way - a National Insurance 
number
>|in an employee database, for instance. Everyone has an NI value, and 
no
>|one
>|can share it, right? Right? Reality always throws up exceptions. Say 
you
>|employ a foreign worker on a temporary visa, for example - no NI No.,
>|suddenly no primary key.
>|
>|Even in the best of worlds, you now find yourself _inventing_ 
imanginary-
>|real values for these records - which is only cool until someone 
forgets
>|that it's an imaginary value and fills out an NI return for the guy. 
In
>the
>|worst of worlds, your DB screws up and starts applying the same NI 
value
>to
>|all the overseas workers, or you end up hiring someone who 
_actually_has_
>|the NI no. you just applied to Mr Temp... And then the boss goes and 
open
>|a Paris office!
>|
>|Semantic keys are a fundamentally bad idea in database design and 
should
>|never be used. The database is for _storing_ data. Using that data to 
do
>|the work of the database itself, is like using the books to hold the
>|shelves up: looks like a bookshelf, but is actually as useful as a
>choloate
>|teapot. Always use an autonumber field as the primary key. You can
>|always hide it, if it is likely to confuse the users of the database.
>|
>|Daniel Walker
>|Wrox Press
>|
>|**************
>|
>|
>|
>|
>|
>
>
>
>
>
>
>




_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx

"One more point - if you do use autonumber keys and want to maintain the
sequence without gaps - you will need to prohibit record deletions.   This
can be of use when you wish to have an audit trail on records."

>>>>But if you have a rolled back transaction you won't be able to use the
    the id number the rolled back transaction has. :(

Thanks,

Enzo

-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: Friday, September 27, 2002 10:44 PM
To: Access
Subject: [access] Re: Use record number to create an ID number
Importance: High


Hi Enzo,

I'm a little nervous about "always".  There are some exceptions.  If you
have multiple users creating new entries at multiple locations with
replicated database you can have problems with key duplication.  In that
case you will need to use a GUID as the key.

One more point - if you do use autonumber keys and want to maintain the
sequence without gaps - you will need to prohibit record deletions.   This
can be of use when you wish to have an audit trail on records.

Rand E Gerald
Database Specialist
Information Services / Operations
Bahá'í National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx

-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 27, 2002 4:40 AM
To: Access
Subject: [access] Re: Use record number to create an ID number


        Thanks Leo for the comment!  Little by little it's coming to my mind
autonumbers is always d primary key no matter what.

Thanks,

Enzo :)

-----Original Message-----
From: Leo Scott [mailto:leoscott@c...]
Sent: Friday, September 27, 2002 4:33 PM
To: Access
Subject: [access] Re: Use record number to create an ID number


The primary key should still be an autonumber but to enforce that field
being unique just create an index on it with duplicates not allowed.  Of
course then you will have to deal with the errors that causes if something
violates the uniqueness.

|-----Original Message-----
|From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
|Sent: Friday, September 27, 2002 12:39 AM
|To: Access
|Subject: [access] Re: Use record number to create an ID number
|
|
|
|Thanks Bob for re posting the post.  But I have a question in mind, what if
|you have this field that you think that is also non repeating like
|for example
|a receipt no (01234) may it be manually or programmatically
|entered.  Is this still
|not good enough to be your primary key?  And also what if this field needs
|to be in series and no jumping of numbers?
|
|Thanks,
|
|Enzo
|
|-----Original Message-----
|From: Bob Bedell [mailto:bobbedell15@m...]
|Sent: Friday, September 27, 2002 3:22 AM
|To: Access
|Subject: [access] Re: Use record number to create an ID number
|
|
|Hi all,
|
|Since it appears that we're revisiting the "Should you always use
|autonumbers as primary keys?" debate for a moment, I wanted to share
|a comment posted by Daniel Walker of Wrox Press on this board in
|February of 2001. This comment helped me see the light, and has spared me
|untold suffering in the design process. I always use autonumbers as
|primary keys and would never consider doing otherwise. Read on:
|
|**************
|
|What you describe as "natural" primary keys are actually known as semantic
|keys in database work. A semantic key is a value from the real world which
|appears to identfy a record in a unique way - a National Insurance number
|in an employee database, for instance. Everyone has an NI value, and no
|one
|can share it, right? Right? Reality always throws up exceptions. Say you
|employ a foreign worker on a temporary visa, for example - no NI No.,
|suddenly no primary key.
|
|Even in the best of worlds, you now find yourself _inventing_ imanginary-
|real values for these records - which is only cool until someone forgets
|that it's an imaginary value and fills out an NI return for the guy. In the
|worst of worlds, your DB screws up and starts applying the same NI value to
|all the overseas workers, or you end up hiring someone who _actually_has_
|the NI no. you just applied to Mr Temp... And then the boss goes and open
|a Paris office!
|
|Semantic keys are a fundamentally bad idea in database design and should
|never be used. The database is for _storing_ data. Using that data to do
|the work of the database itself, is like using the books to hold the
|shelves up: looks like a bookshelf, but is actually as useful as a choloate
|teapot. Always use an autonumber field as the primary key. You can
|always hide it, if it is likely to confuse the users of the database.
|
|Daniel Walker
|Wrox Press
|
|**************
|
|
|
|
|

	In a replicated dbase, Yes.  Access creates GUID that is unique even after
the synchronization of the child databases with their master dbase.

Enzo

-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Saturday, September 28, 2002 12:31 AM
To: Access
Subject: [access] Re: Use record number to create an ID number


Hi Rand,

You just shattered my whole primary key world view. I hadn't factored
replication into the equation. Bummer. How would you handle that? Is it
possible to create a GUID field in Access?

>From: "Gerald, Rand" <RGerald@u...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Use record number to create an ID number
>Date: Fri, 27 Sep 2002 09:43:35 -0500
>
>Hi Enzo,
>
>I'm a little nervous about "always".  There are some exceptions.  If you
>have multiple users creating new entries at multiple locations with
>replicated database you can have problems with key duplication.  In that
>case you will need to use a GUID as the key.
>
>One more point - if you do use autonumber keys and want to maintain the
>sequence without gaps - you will need to prohibit record deletions.   This
>can be of use when you wish to have an audit trail on records.
>
>Rand E Gerald
>Database Specialist
>Information Services / Operations
>Bahá'í National Office
>1233 Central St.
>Evanston IL 60201
>(xxx) xxx-xxxx
>
>-----Original Message-----
>From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
>Sent: Friday, September 27, 2002 4:40 AM
>To: Access
>Subject: [access] Re: Use record number to create an ID number
>
>
>         Thanks Leo for the comment!  Little by little it's coming to my
>mind
>autonumbers is always d primary key no matter what.
>
>Thanks,
>
>Enzo :)
>
>-----Original Message-----
>From: Leo Scott [mailto:leoscott@c...]
>Sent: Friday, September 27, 2002 4:33 PM
>To: Access
>Subject: [access] Re: Use record number to create an ID number
>
>
>The primary key should still be an autonumber but to enforce that field
>being unique just create an index on it with duplicates not allowed.  Of
>course then you will have to deal with the errors that causes if something
>violates the uniqueness.
>
>|-----Original Message-----
>|From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
>|Sent: Friday, September 27, 2002 12:39 AM
>|To: Access
>|Subject: [access] Re: Use record number to create an ID number
>|
>|
>|
>|Thanks Bob for re posting the post.  But I have a question in mind, what
>if
>|you have this field that you think that is also non repeating like
>|for example
>|a receipt no (01234) may it be manually or programmatically
>|entered.  Is this still
>|not good enough to be your primary key?  And also what if this field needs
>|to be in series and no jumping of numbers?
>|
>|Thanks,
>|
>|Enzo
>|
>|-----Original Message-----
>|From: Bob Bedell [mailto:bobbedell15@m...]
>|Sent: Friday, September 27, 2002 3:22 AM
>|To: Access
>|Subject: [access] Re: Use record number to create an ID number
>|
>|
>|Hi all,
>|
>|Since it appears that we're revisiting the "Should you always use
>|autonumbers as primary keys?" debate for a moment, I wanted to share
>|a comment posted by Daniel Walker of Wrox Press on this board in
>|February of 2001. This comment helped me see the light, and has spared me
>|untold suffering in the design process. I always use autonumbers as
>|primary keys and would never consider doing otherwise. Read on:
>|
>|**************
>|
>|What you describe as "natural" primary keys are actually known as semantic
>|keys in database work. A semantic key is a value from the real world which
>|appears to identfy a record in a unique way - a National Insurance number
>|in an employee database, for instance. Everyone has an NI value, and no
>|one
>|can share it, right? Right? Reality always throws up exceptions. Say you
>|employ a foreign worker on a temporary visa, for example - no NI No.,
>|suddenly no primary key.
>|
>|Even in the best of worlds, you now find yourself _inventing_ imanginary-
>|real values for these records - which is only cool until someone forgets
>|that it's an imaginary value and fills out an NI return for the guy. In
>the
>|worst of worlds, your DB screws up and starts applying the same NI value
>to
>|all the overseas workers, or you end up hiring someone who _actually_has_
>|the NI no. you just applied to Mr Temp... And then the boss goes and open
>|a Paris office!
>|
>|Semantic keys are a fundamentally bad idea in database design and should
>|never be used. The database is for _storing_ data. Using that data to do
>|the work of the database itself, is like using the books to hold the
>|shelves up: looks like a bookshelf, but is actually as useful as a
>choloate
>|teapot. Always use an autonumber field as the primary key. You can
>|always hide it, if it is likely to confuse the users of the database.
>|
>|Daniel Walker
>|Wrox Press
>|
>|**************
>|
>|
>|
>|
>|
>
>
>
>
>
>
>




_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx

	Here's a link that would make you clarify you on things about "PRIMARY KEY"
this link has changed my views on primary keys and autonumbers.  I've seen this link
yesterday on ms_access yahoo groups, posted by Donn Edwards.

http://www.dbpd.com/vault/9805xtra.htm

	This is worth reading, this explains using surrogate keys for your database 
design and how it would create great flexibility in your database.  This article
has given me a broader view on database design.

Enzo

-----Original Message-----
From: Mikki.Maxwell@a... [mailto:Mikki.Maxwell@a...]
Sent: Friday, September 27, 2002 3:15 PM
To: Access
Subject: [access] Re: Use record number to create an ID number


Thanks for all of the input.  What I am looking at is the possibility of 
the backend being moved to a SQL Server which does not have autonumber.  
My effort is to prevent having to make changes later.  Any thoughts...  
Thanks again.  Mikki

	to programatically create "somehow" a unique identifier, you can create a seperate table
that will hold the seed value of your identifier.  For every transaction for example, to get
its respective identifier open this table and lock the record then get the seed value plus add 1
then give the result to the caller, then update and released the lock of the record.  You'll just
have to have an error handler to try to get to access the record if it is locked and still being 
used by a prior process.  This is just an idea I got from Dave.

Enzo

-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Friday, September 27, 2002 11:31 PM
To: Access
Subject: [access] Re: Use record number to create an ID number


Hi Enzo,

The best I can do in answering your question is share a bit of my own
experience to date.

The bulk of the database app development I have done has been,
of all places, in the public sector behavioral health industry,
especially since the advent of managed care. In the vast majority
of cases, clients moving through these systems are attached to
a plethora of "unique" identifiers: modified social security
numbers, Medicaid numbers, state, county and ferederal probation and
parole numbers, department of human services case numbers. The
dulication of these numbers across two or three or four clients happens
with alarming regularity. If one believes, for example, that social
security numbers are "unique", they havn't spent much time around the
Medicaid billing cycle. So I'd be very careful about a "field that
you think is also non-repeating."

I don't have a good answer to the second part of your question because
I've never needed to generate an identifier that had to be sequential
in the way you're describing. In the agencies I've done work for, even
internal client identifiers are appropriated from other systems,
such as state systems.  They're usually taken from some form of
documentation that accompanies the client and is manually input.
That has never struck me as the optimal arrangement. I've always felt
that internally generated client IDs are a good idea and there are
algorithms out there to produce them. I've simply never used one. I
would, however, enjoy hearing from anyone who has, particularly if
they feel like doing a little "unique identifier code generator"
swapping.

Best,

Bob

>From: "Lorenzo Amado Zaragoza" <enzaux@g...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Use record number to create an ID number
>Date: Fri, 27 Sep 2002 15:39:16 +0800
>
>
>Thanks Bob for re posting the post.  But I have a question in mind, what if
>you have this field that you think that is also non repeating like for 
>example
>a receipt no (01234) may it be manually or programmatically entered.  Is 
>this still
>not good enough to be your primary key?  And also what if this field needs
>to be in series and no jumping of numbers?
>
>Thanks,
>
>Enzo
>
>-----Original Message-----
>From: Bob Bedell [mailto:bobbedell15@m...]
>Sent: Friday, September 27, 2002 3:22 AM
>To: Access
>Subject: [access] Re: Use record number to create an ID number
>
>
>Hi all,
>
>Since it appears that we're revisiting the "Should you always use
>autonumbers as primary keys?" debate for a moment, I wanted to share
>a comment posted by Daniel Walker of Wrox Press on this board in
>February of 2001. This comment helped me see the light, and has spared me
>untold suffering in the design process. I always use autonumbers as
>primary keys and would never consider doing otherwise. Read on:
>
>**************
>
>What you describe as "natural" primary keys are actually known as semantic
>keys in database work. A semantic key is a value from the real world which
>appears to identfy a record in a unique way - a National Insurance number
>in an employee database, for instance. Everyone has an NI value, and no
>one
>can share it, right? Right? Reality always throws up exceptions. Say you
>employ a foreign worker on a temporary visa, for example - no NI No.,
>suddenly no primary key.
>
>Even in the best of worlds, you now find yourself _inventing_ imanginary-
>real values for these records - which is only cool until someone forgets
>that it's an imaginary value and fills out an NI return for the guy. In the
>worst of worlds, your DB screws up and starts applying the same NI value to
>all the overseas workers, or you end up hiring someone who _actually_has_
>the NI no. you just applied to Mr Temp... And then the boss goes and open
>a Paris office!
>
>Semantic keys are a fundamentally bad idea in database design and should
>never be used. The database is for _storing_ data. Using that data to do
>the work of the database itself, is like using the books to hold the
>shelves up: looks like a bookshelf, but is actually as useful as a choloate
>teapot. Always use an autonumber field as the primary key. You can
>always hide it, if it is likely to confuse the users of the database.
>
>Daniel Walker
>Wrox Press
>
>**************
>
>
>
>






_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com