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  

New Database - Primary Key



 
 
Thread Tools Display Modes
  #11  
Old June 9th, 2009, 06:22 AM posted to microsoft.public.access.tablesdbdesign
Armen Stein
external usenet poster
 
Posts: 507
Default New Database - Primary Key

On Mon, 8 Jun 2009 16:00:05 +0100, Bernard Peek
wrote:

Hi Bernard,

If you add an
autonumber field you would call it a surrogate key.


Yes, an AutoNumber can act as a surrogate key with automatically
generated values.

But Access refers to
an autonumber field as a primary key, which I consider to be a serious
flaw in its design.


Not really. Access supports a table having a primary key, which is a
field (or combination of fields) that uniquely identifies each record.

And Access supports an AutoNumber datatype, which is a Long Integer
assigned unique values automatically. However, you do not *have* to
make it the primary key, although that's normally how it's used. You
can have one field (or fields) as a primary key, and another non-PK
AutoNumber field. So they are *not* the same thing, and Access
doesn't refer to them as such. Access will offer to create a PK using
an AutoNumber if you try to save a table without a PK, but that's just
it trying to help. It doesn't claim that they're the same thing.

So Access does not have the "serious flaw" you're referring to.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

  #12  
Old June 9th, 2009, 07:30 AM posted to microsoft.public.access.tablesdbdesign
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default New Database - Primary Key

Bernard Peek wrote:

Access is a very nice tool for some jobs but it has limitations. It's
not suitable for applications where lots of people need frequent access
to the data. For those types of applications SQL Server is more
appropriate.


And when the app reaches the point where SQL Server becomes viable you
only need to move the data across and do some tweaking in Access and
SQL Server. And keep on going using all the time and effort you've
invested in Access.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/
  #13  
Old June 9th, 2009, 01:11 PM posted to microsoft.public.access.tablesdbdesign
Bernard Peek[_3_]
external usenet poster
 
Posts: 42
Default New Database - Primary Key

In message , Armen Stein
writes
On Mon, 8 Jun 2009 16:00:05 +0100, Bernard Peek
wrote:

Hi Bernard,

If you add an
autonumber field you would call it a surrogate key.


Yes, an AutoNumber can act as a surrogate key with automatically
generated values.

But Access refers to
an autonumber field as a primary key, which I consider to be a serious
flaw in its design.


Not really. Access supports a table having a primary key, which is a
field (or combination of fields) that uniquely identifies each record.


It's not that simple unfortunately. The primary key is *always* made up
of attributes in the underlying entity and this does not include the
autonumber field. Access should identify the data field(s) as the
primary key and the autonumber field as a surrogate key. Both primary
and surrogate keys uniquely identify a record and either can be used to
implement joins. By merging the two ideas Access makes it more difficult
than it should be to follow good practise. But that's probably an
unrealistic expectation for a tool that's designed for people with no
formal training in database design.



--
Bernard Peek
  #14  
Old June 9th, 2009, 01:15 PM posted to microsoft.public.access.tablesdbdesign
Bernard Peek[_3_]
external usenet poster
 
Posts: 42
Default New Database - Primary Key

In message , "Tony Toews
[MVP]" writes
Bernard Peek wrote:

Access is a very nice tool for some jobs but it has limitations. It's
not suitable for applications where lots of people need frequent access
to the data. For those types of applications SQL Server is more
appropriate.


And when the app reaches the point where SQL Server becomes viable you
only need to move the data across and do some tweaking in Access and
SQL Server. And keep on going using all the time and effort you've
invested in Access.


Indeed. Even when I have built a production database in SQL Server I am
likely to use Access as a tool for database maintenance.



--
Bernard Peek
  #15  
Old June 9th, 2009, 03:26 PM posted to microsoft.public.access.tablesdbdesign
Trini Gal
external usenet poster
 
Posts: 20
Default New Database - Primary Key

Thank you all for all of your information. I really learned a lot from your
post and they cleared up a lot of questions I had about the Primary Key.

Thanks Again.

"Bernard Peek" wrote:

In message , "Tony Toews
[MVP]" writes
Bernard Peek wrote:

Access is a very nice tool for some jobs but it has limitations. It's
not suitable for applications where lots of people need frequent access
to the data. For those types of applications SQL Server is more
appropriate.


And when the app reaches the point where SQL Server becomes viable you
only need to move the data across and do some tweaking in Access and
SQL Server. And keep on going using all the time and effort you've
invested in Access.


Indeed. Even when I have built a production database in SQL Server I am
likely to use Access as a tool for database maintenance.



--
Bernard Peek

  #16  
Old June 9th, 2009, 05:08 PM posted to microsoft.public.access.tablesdbdesign
Armen Stein
external usenet poster
 
Posts: 507
Default New Database - Primary Key

On Tue, 9 Jun 2009 13:11:28 +0100, Bernard Peek
wrote:

It's not that simple unfortunately. The primary key is *always* made up
of attributes in the underlying entity and this does not include the
autonumber field. Access should identify the data field(s) as the
primary key and the autonumber field as a surrogate key. Both primary
and surrogate keys uniquely identify a record and either can be used to
implement joins.


Hi Bernard,

It seems your definition of "primary key" is narrower than mine. In
Access you can choose what your primary key is. It can be a natural
data field (or combination of them). Or it can be an AutoNumber. Or
both, although there's no real reason to do that.

The writeup at http://en.wikipedia.org/wiki/Surrogate_key is pretty
informative. We consistently use surrogate keys as primary keys, as
we feel the advantages outweigh the disadvantages.

By merging the two ideas Access makes it more difficult
than it should be to follow good practise. But that's probably an
unrealistic expectation for a tool that's designed for people with no
formal training in database design.


Well, it is designed for an easier initial learning curve. But we've
been developing custom database applications using Access, while
following good database design practices, for over 15 years. I have
no problem with Access's implementation of AutoNumbers and primary
keys.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

  #17  
Old June 9th, 2009, 06:10 PM posted to microsoft.public.access.tablesdbdesign
Bernard Peek[_3_]
external usenet poster
 
Posts: 42
Default New Database - Primary Key

In message , Armen Stein
writes
On Tue, 9 Jun 2009 13:11:28 +0100, Bernard Peek
wrote:

It's not that simple unfortunately. The primary key is *always* made up
of attributes in the underlying entity and this does not include the
autonumber field. Access should identify the data field(s) as the
primary key and the autonumber field as a surrogate key. Both primary
and surrogate keys uniquely identify a record and either can be used to
implement joins.


Hi Bernard,

It seems your definition of "primary key" is narrower than mine. In
Access you can choose what your primary key is. It can be a natural
data field (or combination of them). Or it can be an AutoNumber. Or
both, although there's no real reason to do that.


I've spent quite a lot of time in the comp.databases.theory newsgroup
where terminology tends to be used very carefully. In fact some of the
people there want to abolish the term and just use "key" so that it's
clear that a table may have more than one key (even though that's
generally seen as bad practise.)


The writeup at http://en.wikipedia.org/wiki/Surrogate_key is pretty
informative. We consistently use surrogate keys as primary keys, as
we feel the advantages outweigh the disadvantages.


I often use surrogate keys too, but only after I've established that
there isn't a real primary key that I can use. There were once very good
arguments for using surrogate keys even if a table had a real primary
key, but I believe that they have mostly gone away.



By merging the two ideas Access makes it more difficult
than it should be to follow good practise. But that's probably an
unrealistic expectation for a tool that's designed for people with no
formal training in database design.


Well, it is designed for an easier initial learning curve. But we've
been developing custom database applications using Access, while
following good database design practices, for over 15 years. I have
no problem with Access's implementation of AutoNumbers and primary
keys.


For the benefit of those that have tuned in late to this particular
debate before, many practitioners consider that using a surrogate key
when a real primary key is available is bad practise. The argument is
that you still need to maintain a unique index on the real data and
keeping a second one on the autonumber just adds processing overhead.
There's a countervailing argument that using a small field like a
long-integer for creating joins is more efficient. Personally I doubt
that either effect is particularly strong when considering the type of
databases that are commonly built using Access.

My personal view is that adding a surrogate key adds unnecessary
complexity and another point of failure, so I discourage it. And as I'm
the boss it's my opinion that counts.



--
Bernard Peek
  #18  
Old June 9th, 2009, 10:23 PM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default New Database - Primary Key

Bernard Peek wrote in
:

The primary key is *always* made up
of attributes in the underlying entity and this does not include
the autonumber field.


You're just restating the old natural key vs. surrogate question as
though there is a right answer to the question. There isn't. You may
*prefer* natural keys, but that's only a preference.

No defender of natural keys has ever been able to answer the
question of how you define a natural key for a table storing data
about people without populating the fields with useless default
values (to avoid the Null problem, i.e., PKs can't have Nulls in any
of the fields).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #19  
Old June 9th, 2009, 11:08 PM posted to microsoft.public.access.tablesdbdesign
Bernard Peek[_3_]
external usenet poster
 
Posts: 42
Default New Database - Primary Key

In message 00, David
W. Fenton writes
Bernard Peek wrote in
:

The primary key is *always* made up
of attributes in the underlying entity and this does not include
the autonumber field.


You're just restating the old natural key vs. surrogate question as
though there is a right answer to the question. There isn't. You may
*prefer* natural keys, but that's only a preference.

No defender of natural keys has ever been able to answer the
question of how you define a natural key for a table storing data
about people without populating the fields with useless default
values (to avoid the Null problem, i.e., PKs can't have Nulls in any
of the fields).


In the theory newsgroup I postulated a primary key consisting of the
latitude, longitude, altitude and time of birth. I didn't claim that it
was very practical.

There are lots of situations where there isn't any alternative to
surrogate keys, in fact the majority of tables I have come across don't
have a good natural key. But in any system that I manage the DBA will
have to justify the decision to use surrogates on a case-by-case basis.



--
Bernard Peek
  #20  
Old June 11th, 2009, 03:07 AM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default New Database - Primary Key

Bernard Peek wrote in
:

I've spent quite a lot of time in the comp.databases.theory
newsgroup


They are idiots in that group. None of them really work with actual
client data, so far as I can tell. Celko is one of the worst
blowhards -- a smart man, but doesn't really understand the real
world.

Theory is a starting place for designing a real-world app. It is not
the end point.

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




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


All times are GMT +1. The time now is 04:05 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.