Re: Concatenate and Null Values -- Features

Thanks for the links. I don't have time to study them right now, but I have
bookmarked them.

To me multi-field keys seem messy (for lack of a better word), and natural
keys that contain elements subject to change can mean massive cascade
updates, which make me nervous. I am not convinced that physical ordering
on the disk provides a significant (or maybe even noticeable) performance
gain. Having said that, I have little experience with really large
databases, so all I can really say for sure is that surrogate keys work well
for me. The argument will rage on.

Tony Toews dislikes cascade deletes as well as cascade updates, and uses a
SQL statement to perform the delete action. I'm not entirely convinced, but
then I very rarely delete records, especially from the Parent table.
Because I am convinced that if I am indispensable I am not doing my job very
well, other people have some database management permissions. The
temptation can be to edit a record rather than creating a new one (purchase
orders, for instance, where the common practice has been to change the date
and a few details on a PO, then send it out again). I tell my associate
administrators that they are *never* to delete a record. Sometimes I do
some cleanup that involves selective deletions, but the stray record usually
isn't causing any real problems.

My observation about being embarassed by my early attempts wasn't intended
all that seriously. One poorly designed flat file of mine is still in place
because it is used infrequently, and because when it is used people tend to
print out a paper form that is circulated by hand. For various business
reasons that kind of redundancy is required. However, if somebody was
interested in seeing my work I would rather they not select that project at
random.

"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
news:EF56395A-3870-49D7-974F-A171A9CF0200@xxxxxxxxxxxxxxxx
Hi Bruce,

I've only been doing this for a few years (and not full-time at that),
and
am already embarassed by some of my early attempts.

That's a clear sign that you are learning. Nothing to be embarrassed
about.

To add some to what Crystal stated, there are people on both sides of the
fence regarding the use of natural versus surrogate (autonumber) keys. I,
too, am on the surrogate key side of the fence. Here are some articles
that
you might want to read:

The Case for the Surrogate Key
http://www.dbpd.com/vault/9805xtra.htm

Database Normalization Tips
By Luke Chung, President of FMS Inc.
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html
See the section sub-titled "Use Meaningless Field for the Key Field".

I have the newest version of Total Access Analyzer for Access 2002, a
product marketed by FMS:
http://www.fmsinc.com/products/analyzer/index.html.
The latest version includes the following bits of advice in a report for a
database that I just analyzed for another person. Of course, these are the
opinions of FMS, which folks on the natural key side of the fence could
likely argue with:

Table Primary Key is Multi-Field
For optimal database design and performance, the primary key of a table
should be a single field containing a meaningless value that does not
reflect
the data in the other fields of its record. In Access, the easiest is to
use
a field of AutoNumber field type (long integer) which Access automatically
fills with a unique value when a new record is created. In SQL Server, use
an
Identity field. With a single field primary key, any secondary indexes of
the
table point to one field rather than all the primary key fields (and
values)
in a multifield primary key.


Table Primary Key is Not AutoNumber
The primary key of a table should be a single field containing a
meaningless
value that does not reflect the data in the other fields of its record.
With
rare exceptions, the primary key should be numeric since it's smaller than
text fields and makes secondary indexes on the table more efficient.
Access
makes it simple to do this by designating a field with AutoNumber field
type
(long integer) and automatically
filling it with a unique value for new records. A numeric field is
acceptable if the value is always unique and is not edited or modified
once
created.


Table Primary Key is Not Numeric
With rare exceptions, primary key fields should be numeric since it's
smaller than text fields and makes secondary indexes on the table more
efficient. Exceptions usually occur for lookup tables where the list of
values is fairly stable and using an ID number to reference it causes more
confusion (e.g. a list of countries, states, gender, etc.). For optimal
database design and performance, the primary key of a
table should be a single field containing a meaningless value that does
not
reflect the data in the other fields of its record. In Access, the easiest
is
to use an AutoNumber field type which Access automatically fills with a
unique value when a new record is created. In SQL Server, an Identity
field
serves the same purpose.

Consider also what Access MVP Tony Toews has to say regarding Cascade
Updates:
http://www.granite.ab.ca/access/cascadeupdatedelete.htm

"If you are using an autonumber primary key in your tables then cascade
updates are a non issue because 1) you can't change an autonumber key and
2)
the user should never see the autonumber key. If you are using a natural
key,
for example in Northwinds this would be using CategoryName as a primary
key
in the Categories table, then I can see this being somewhat useful. I
don't
care for natural keys though. Access needs some added functionality to
support these better such as in the subform creation wizards."

"Doing a lot of cascade updates can be more susceptible to performance and
corruption problems as cascade updates could need to lock a lot of pages.
A
table high up on the relationships "food chain", for example, a customer
table, could require many thousands of invoices to have the customer
foreign
key to be changed if cascade updates were allowed."



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"strive4peace" wrote:

Hi Bruce,

IMHO, every table should have an autonumber primary key (even if you do
not need it for a relationship) and it is not a good idea to use
meaningful data for relationships.

On the field with 07-01, 07-02, etc, you could set a Unique index so
duplicates would not be allowed. If you HAVE the date this comes from,
there is really no reason to store it because it can always be
calculated.

"embarassed by some of my early attempts"

I know how you feel! Every day, I learn more about Access -- each
application is better than the last ...

I learned Access by teaching it; I had been programming with DOS-based
databases for about 10 years when I was approached to teach Access
running under Windows 3.0 <g> ... they just asked if I could do it, not
if I had ever used it ;) So I crammed like crazy and no one ever knew
that the teacher was as new as the students! I researched every
question that anybody had, and was usually making handouts for the
afternoon during my lunch break. Teaching is a great way to learn.

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



BruceM wrote:
I've only been doing this for a few years (and not full-time at that),
and
am already embarassed by some of my early attempts. I am aware of
Michael
Hernandez' Database Design for Mere Mortals, which I will be acquiring
as
soon as I get organized enough to order it. The light has come on for
me,
but I'm still squinting a lot. I'm also trying to decide how much
improvising I can do. For instance, I have a situation in which a
number
increments 07-01, 07-02, etc., then starts over with 08-01 etc. next
year.
It is a unique number, so I could make it the PK, but I would be
storing
part of the date, which is already in another field. I could either
add a
PK field, combine two fields into a PK (although I'm not sure what the
other
field would be), or just go ahead and store 07-01, etc. In the first
case I
am creating a new field in the parent table (autonumber, maybe); in the
second I am creating a new field in the child table, since a two-field
PK
needs a two-field FK (as I understand it); in the third case I have one
PK
and one FK, but arguably some redundancy in the date. So I ask myself
if
storing part of the date like that is less redundant than adding a
field
just so I can avoid storing part of the date. I've about decided to
store
07-01, but my point is that I am thinking about this in a way that
would not
have been possible a year ago.

"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
news:D088C525-31BB-4DB7-AF4A-167A33ED48B5@xxxxxxxxxxxxxxxx
Hi Bruce,

For what it's worth, I floundered like a fish out of water for a few
years
with Access. I had been attempting to work with Access since version 2
was
released. The light finally came on for me one evening back in '96 (or
perhaps '97) when I was at a meeting of the Pacific NW Access
Developer's
Group. The speaker was Michael Hernandez, that author of the paper I
cited
in
my previous post. Several of the examples of problems he showed were
the
exact types of problems I had been encountering. I made an effort to
start
studying database design after that, even though the subject can be a
bit
dry. The effort really payed off.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"BruceM" wrote:

Hoopster, this is a lot of stuff being tossed your way, but it is
really
good stuff. The people who have added to this thread since you
posted
the
table structure are very skilled and knowledgeable (excepting this
posting;
I can claim only that I am making decent progress for a relative
beginner,
but I know good advice when I see it). I urge you not to be daunted
by
all
of the new information. Once you start to get the hang of relational
databases you will have a powerful tool at your disposal.





.

Relevant Pages

  • Re: AutoNumber Question
    ... primary key, so the posted comments were applicable. ... database, as it would be if it was part of relationships. ... even then, a random number, such as Aceess' AutoNumber should be used ... so a VIN has meaning separate ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Multi-Field Primary Key
    ... Surrogate numeric keys do solve a lot of technical ... problems for database and database application developers and many seasoned ... I recognise *three* uses for autonumber. ... "Although a primary key isn't required, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: AutoNumber Question
    ... table, Autonumber is not the only alternative, and certainly not the best ... "If it were a Database containing vehicle information, ... primary key, so the posted comments were applicable. ... A VIN as a PK could get a bit murky for an insurance company or the DMV ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Combining several databases into one master
    ... Autonumber primary key in the CONTACTS table of the dbs used by the sales ... rep. in the main database, import the records into the CONTACTS table - ... using a unique index made up of a LongInteger field (to hold the Autonumber ... value from the sales reps' tables) and a field that identifies each sales ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Concatenate and Null Values -- Features
    ... fence regarding the use of natural versus surrogate (autonumber) keys. ... For optimal database design and performance, the primary key of a table ... Consider also what Access MVP Tony Toews has to say regarding Cascade Updates: ...
    (microsoft.public.access.reports)