Re: Concatenate and Null Values -- Features

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: Assistance with my tables and relationships pls, should be fairly simple
    ... and we need to choose among differing opinions. ... Values in primary key columns must never change. ... But tables can have no candidate key besides the autonumber. ... He has seen far more errors in database design caused by *not* ...
    (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: Concatenate and Null Values -- Features
    ... Tony Toews dislikes cascade deletes as well as cascade updates, ... fence regarding the use of natural versus surrogate (autonumber) keys. ... Database Normalization Tips ... For optimal database design and performance, the primary key of a table ...
    (microsoft.public.access.reports)
  • Re: Autonumber Fields
    ... this special meaning is clustered index. ... believe that a clustered index is a requirement for a primary key. ... questions don't understand the purpose of an autonumber primary key. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Does each related table have to have their own primary key ass
    ... I agree with Rick Brandt to some extent, and I can't think of any real problems that you might encounter by adding Autonumber fields that you never use, so you perhaps should go ahead and stick them in there and nothing will go wrong, although they will make your Tables slightly larger and require you to name them and keep track of them. ... one such reason for ensuring that all the records are easily distinguishable (by having a unique primary key field in each record) might be that the Table is part of a self-join. ... The reason I ask is that when I was 1st setting up my database I had the 1st table set with a primary key I assigned (policy #) and I set the next table with an autonumber, but I was getting error messages when I was trying to do reports and forms extrapulating data from both tables. ...
    (microsoft.public.access.gettingstarted)