thank you Access newsgroup posters

Forgive me Father Access for I have sinned!



Dev Ashish ( ) has a web site dedicated to the knowledge
transfer of Microsoft Access users. He has a page I read a long time ago
that I thought I understood, I laughed and said how cute.



The Ten Commandments of Access

http://www.mvps.org/access/tencommandments.htm



I wanted to write an application for the Collections Industry, no not those
weirdoes that collect Beanie Babies, the unfortunate businesses that sell
something to you and me and expect to be paid for it. Well they don't get
paid, and when they do the check always bounces, the check is sent in the
mail, or the Ex-husbands girlfriend took the money. Always excuses.



Ok, well 1 year later and I am back to square one. Why did I return to
square one, well thank you for asking let me tell you why.



When I chose Access it was to be a quick prototype to make sure I understood
the needs, BUT NO, the dang company started using the Access tool as if it
were released, telling me that's it exactly what we needed. Um, but could it
please do this too. Now stuck with access I continue.



A year later and here I am looking at my fields, tables and relationships
and guess what; they aren't normalized, Oh I thought they were! And they
were, according to the draft I used to explain the business rules, but
somewhere along the way getting ready to release version three of this app,
the needs listed, didn't match the needs they were now asking for.



Oh and by the way did you read Commandment number two, Do not use lookup
fields in a table. I assure you I now understand what I thought I knew about
normalized tables.



Don't just read these Commandments; memorize them, until they flow off your
brain like water off a table of broken fields.



That brings me to commandment number 3. Ok well I did use a naming
convention, ok phew.



Commandment number 4 doesn't bite you in the ass until you have now realized
that you broke Commandment numbers 1 and 2 and now need to re-do the
database you started a year ago. I'm sure you're better than me, but I wrote
comments sporadically and I never commented the code I really needed to
comment, oh sure that would have been hard, its always easy to go back
through your code and say YES I messed up. After all, I knew when I started
this mess, I mean application, that it would only take 4 to 6 weeks to
finish. Oh how wrong I was.



And if you don't know what Commandment number 7 means DON"T USE THEM. Ease
of use does not make a good program, better to re-invent the wheel than
re-write your whole application, I can assure you. Its almost like the
programmers at Microsoft say wow, here's a cool feature, lets give them
this, get them lost and we will finish it in a future version so it works
right.



I mean I even got them to sign off on my design document. They thought they
knew, I thought they knew, and it turns out only the Access Gods know for
sure.



I don't know where I would be with out the faithful masochists, oh yeah and
a few sadists, who watch and post to these newsgroups, but I thank them from
the bottom of my heart, you will surely inherit the kingdom.



-Dan Hurwyn

"Dan" wrote in message

Forgive me Father Access for I have sinned!

As have we all, my son.

The Ten Commandments of Access

http://www.mvps.org/access/tencommandments.htm

Let us take heart, and learn from those who have passed this way before.

G

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dan, that is a wonderful..and heart felt warming post.

Really, the issue here is that some of this stuff takes time..and that is
what
we call experience.

I do however want to point out one little issue he

And if you don't know what Commandment number 7 means DON"T USE THEM. Ease
of use does not make a good program, better to re-invent the wheel than
re-write your whole application, I can assure you. Its almost like the
programmers at Microsoft say wow, here's a cool feature, lets give them
this, get them lost and we will finish it in a future version so it works
right.

No, that is not quite what #7 says. It says that if you use a autonumber, do
not EXPOSE it to your users. The problem with autonumbers is

* Only when you give them meaning *

You could be developing using Oracle, mysql, sql-server, Sybase, or JET in
ms-access. All, I repeat ALL OF these systems offer automnumbers.

As a developer you will eat, breathe, and live by using autonumbers.
Autonumbers is STANDARD FAIR in the database industry, and virtually ALL
database SYSTEMS provide a autonumber mechanism. In fact, the only system I
used that did not provide a built-in autonumbers was Raining Data (pick)
systems, and that was a complete different system then a relational database
anyway.
(it is known as a multi-value system..and is not based on the relational
data model).
Further, when I first started using the "pick" multi-value system, the first
thing I did was write my own autonumber system!

Hence, autonumbers are part of database systems as to what screwdrivers are
to auto mechanics..and they are most useful.

So, take note to heart that above statement does not say don't use
autonumbers. The
above says that you MUST NOT allow your USERS TO give meaning to those
numbers, and that is a big difference.

A mechanic will for sure use a screwdriver...but a good mechanic will not
use a screwdriver to pry off battery cables, as that can damage the battery
post. So, what that rule is really saying don't use a tool for the wrong
thing.

So, you will freely use autonumbers throughout your application, but you
will NEVER allow users (or you!) to give meaning to those numbers.

I mean, if I have a customer file, and a another
file of invoices, then we as a developer will want to have a one to
many relationship for this design. In most cases I would use a
autonumber to accomplish this design.

However, that field to build the relationship will never be seen by my end
users.
This means that the application will function correctly even if the user
does not yet entered a invoice number, or yet been given a customer number.
So,. we might not have yet issued a customer number. However, customer
numbers, and
invoice numbers are for the "end user". These number have little, or nothing
to do with the
fact that you are building a one to many relationship.

What this means is that any "number" that your USERS see should NEVER be a
autonumber.

So, you might write some custom code to generate a customer number, or even
perhaps have the accounting department issues a customer number. So, perhaps
users are trained to make up a customer number that is part of the customer
name and perhaps today's date + the year, or some other silly scheme that
a company uses (and, having written software for 20 years...I seen some
real strange methods that companies use to come up with a customer number).

However, that company can do whatever it
wants..but that process HAS ABSOLUTE NOTHING TO do with my relational
database design. That exposed customer number should not be used for the
relationship.

Thus, the users should be free to come up with any kind of customer number,
or
even leave it out until the customer actually becomes a customer. It is
very possible that we are building a sales database and a customer service
system. So, that system should still work just fine with the many names that
we enter for the sales process, or even lists that we enter for mass email
stuff. As these names become customers, then a customer number can
be entered. It is rather silly to not allow this system to function just
because
we have not issued a customer number yet.

Word lets you edit documents..and it is certainly magic to the end users.
Those end users NEVER worry about memory segment numbers, and how word loads
into memory (but, all kinds of numbers are being issued to make this process
happen). So, the same should be of your applications that you write. Those
numbers are simply part of the machinery to make the software work. End
users care nothing about some pointer, variable name you choose, or some
internal autonumber that maintains the relationships. They just don't
care..and never even have to know about the autonumber.

Can you imagine how silly it is to
tell a customer that the program will break, or not function because you
don't type in a customer number, or that you can't use the software until a
customer number is issued?

Perhaps the business rules do state that you MUST have a customer number
before you enter the person into the application. Once again, that
requirement
is a business rule that the company wants. This fact has little, or nothing
in regards of you develpuming a one to many relationship. If the company
decides that no new company records will be added without a customer
number, then so bit it. However, what does a company decision about some
customer number have ANYTHING to do with the fact that you have built a one
to many relationship? That is like saying that employees should not drive
diesel cars, but only drive gas cars because of the way customer numbers are
issued? Software is a machine that company purchases like car. Issuing
customer numbers has nothing to do with diesel, or gas cars...or the fact
that some developer made a one to many relationship in some software that
the
company uses..

So, it is just fine and reasonable to use a autonumber for the
relationship between the customer file and the invoices file. In fact, I
would
recommend that you use a autonumber. Like word, a car you purchase
or your application, it should just work..and care little about the fact of
some
number having been issued. Thus, your application will not break, or
not function if you need to enter invoice information..but not yet have a
invoice number.

As a developer, you build an application that works, and the
building of the application should not care about some number that the
accounting
department issues to a customer.

The same goes for something like a social insurance number. You can put that
field on a form, but since you OFTEN don't have a social insurance until
some time later on (for example, the employee comes in, gets approved for
work....but it might be some time before you get a sin number, ...you mean
now the whole application will break....or not work because you don't have
a sin number?

Once again, I will quote that lesson:

Thou shalt not use Autonumber if the field is meant to
have meaning for thy users.

The key word here is "meaning".

An autonumber is some mechanistic to generate a number. To you and me, all
we care about is that we have a relation from customers to customers invoice
table. Do we really care, or have to know what number is used? Why would
anyone care what number, or even "how" the customer file is related to the
invoice file. The ONLY thing we care about here is that fact that there is
a relationship..and it works. To start assign meaning, or worrying about
some number to make this relationship is not required at all.

There is not more importance to some internal number that word uses to load
a document into some memory segment number, and there is no importance to
some number generated to make a deaconship between two tables. In the case
of word, all we care is that the document loads into memory, and in the case
of ms-access all we care is that a relation exists..and it works...nothing
more...nothing less...

Having said the above, there is much differing opinions among developers
as to if autonumbers (artificial keys) and natural keys (a key made up
from actual data in the record) should be used. I will say that both
approahces have their place in the IT and the database industry.
I am not looking for a debate on natural keys vs generated keys,
but I only wanted to point out that all vendors, and all database
systems provide a autnoumber feature, and it is standard fair
with most database developers to use autonumbers. However
if you do use them..don't let our end users seem them!!

And, there is most certainly pros and cons of using natural keys vs
artificial
(autonumber) keys. This is not that debate, as that can be long one...

Remember, all software uses all
kinds of mechanisms to make software work. Pointers, internal index values
etc. This is all fine and par for the course.

The problem only starts when end
users start using those mechanics numbers for THEIR use!


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Albert D. Kallal wrote:
users should be free to come up with any kind of customer number,
or
even leave it out until the customer actually becomes a customer. It
is
very possible that we are building a sales database and a customer
service
system. So, that system should still work just fine with the many
names that
we enter for the sales process

I appreciate you don't want to enter the natural keys vs artificial
keys debate.

The problem I have with this treatise is that your use of an autonumber
(COUNTER) may be hiding the fact that something is missing (or an
identifier remains unidentified) in the data model.

To pick up on the 'not yet been given a customer number' example:

CREATE TABLE Customers (
ID COUNTER NOT NULL,
customer_number CHAR(10) NULL,
last_name VARCHAR(35) NOT NULL,
first_name VARCHAR(35) NOT NULL
);

Given that customer_number may be null and first_name + last_name (e.g.
'Jean Dupont') is insufficient to provide a unique identifier, then
this table lacks a natural key. You seem to be saying that the
autonumber ID may legitimately used to provide an artificial key. The
problem is that if you don't expose it (I don't know what you mean by
'users' in this context) then the table still lacks an identifier. The
table can have multiple 'Jean Dupont's with null customer_number's and
I still can't tell one from another. What's the use in having a
'one-to-many' relationship if I can't reliably get the 'one' out of the
database?

Jamie.

--