Are Orphaned Tables OK

[ED007]

I have a table set up this way.

*LOTS*
LOT - Text Field and Primary key
GRADE - Text Field
....
....
....

Our LOT identifiers are unique and are a concatination of the day of the
year, 1-365), two digit year, single letter signifiying lot order A, B, C
used for production, two digit code to signify operator, single digit to
signify shift, and single character to signify machine.

Originaly I have the table set up as
*LOTS*
LOT - Text Field (only day, year part, and order) and Primary key
OPER - Text Field
SHIFT - Text Field
MACHINE - Text Field
GRADE - Text Field
....
....
....


I also had tables for OPER, SHIFT and Machine

*OPER ID*
OPER - Text Field and primary Key
First Name - Text
....
....

*SHIFT*
SHIFT - Text Field and primary Key
Start time - date/time
end time - date/time
....
....

*MACHINE*
MACH - Text Field primary key
MACHINE DETAILS - Memo
....
....


These tables were linked to limit the data that could be entered on the main
table to real operators, shifts and machines.
This worked great UNTIL I realized that the LOT could be repeated if the
same operator produced material on more that one machine at the same time.
This is very rare but does happen.

Therfore I concated all the fileds together into the new LOT. To validate
the LOT data entry some code is used to pull the LOT apart and compare each
part with the corisponding table of allowable entries.

When I run reports or querries that use the information in these orphaned
tables I also must write code to pull apart the LOT and get the correct part.

My question is was their a better way to solve my original issue? I thought
about setting a new primary key in the original table that was an auto
number. HOWEVER, the users af the data base were already grumbling about
having to enter each part ofhte LOT seperatly. So combining it made some
sense make them happy. But I fear I may have set myself up for bigger issues
down the road.

Any other ideas as to what i should have done?

 

[Roger Carlson]

Your original idea was correct. You should have an autonumber primary key
for that table. In addition, you should not store the concatenated value
for LOT. Instead, you should recreate it on the fly in queries, reports,
and forms as needed. Then create a Unique Index on the Date, OPER, SHIFT,
and MACHINE. (You ARE capturing the Date, aren't you?)

I don't know what to tell you about the grumbling. If the operators are
themselves entering data, perhaps you could have a login screen which would
accept OPER, SHIFT, and MACHINE and set them as default values.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Pat Hartman\(MVP\)]

Regardless of the complaints, storing the lot as a mushed field violates the
first three normal forms and is not recommended. A string of cascading
combos can be used to minimize typing or if you wish, populate the RowSource
of the combo with a concatenated field that mushes all the fields together.
This works because you have a table that defines valid lots. This combo
will make the users more comfortable. They think they are working with
their familiar lot number but the real data is properly stored.

"This worked great UNTIL I realized that the LOT could be repeated if the
same operator produced material on more that one machine at the same time.
This is very rare but does happen."
Earlier you said that machine is part of the lot identifier so how could
this cause a duplicate?

I would use an autonumber as the primary key because pk's with this many
pieces tend to be awkward to work with. The user never needs to see the
autonumber. You will only use it for joining to other tables. You can then
define a unique index that includes all the fields that are required to
uniquely identify a lot. Access/Jet supports up to 10 fields in a
multi-field primary key or index.

 

[Tom Ellison]

Dear Roger:

Any particular reason why an autonumber primary key is so important. There
is a unique combination of Date, OPER, SHIFT and MACHINE. Let that be the
PK.

I have built multiple databases without autonumber type identities. They
are almost always unnecessary. In a database the scale which is possible
for Access Jet, this has never been a problem.

Tom Elliosn

 

[ED007]

Unfortunatly the operators are not entering the information. So a log on
screen would not be ideal. I am not capturing the date perse but I do have
it in the first 5 digits ofhte lot. I use the DateSerial function in
querries and or reports were I want to report the production date for a lot.

What I am thinking now is that I should have added the autonumber primary
key. And to give the daa entry people a break had an unbound field in the
data entry from to accept the "full" lot id and code in the on update or on
exit section to yank the text apart and put the appropriate part in the
relavent field. This would have preserved the table relationships, and made
writting subsequent reports and querries easier. However, I have everything
working now so I will leave it alone. Just trying to learn from my mistakes
for the next time.

 

[Roger Carlson]

In this case, it may well be unnecessary.

The only time it would be necessary (to my mind) is if the table were
someday linked with to other table at some time. My experience is that no
matter what people say at the time, things change. To be stuck with a
multi-field primary key and have to join on multiple fields is, I believe,
well worth the time to set up an autonumber primary key ahead of time. If
it's never used, it's not really hurting anything. If it is needed in the
future, you'll already have it.

Natural vs Surrogate keys is a religious issue in some circles, and I won't
get into that here. I'll just say that nearly EVERY table I create (aside
from "linking" tables) has an autonumber primary key. I think most of the
MVPs would agree. If you don't want to, I'm fine with that.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

I would have them enter them into separate controls and store the values in
separate field and put them together when you need them. Ripping it apart
is far harder.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[ED007]

Earlier you said that machine is part of the lot identifier so how could

this cause a duplicate?

In the original manefestation LOT was only the first 6 characters of the
mashed up LOT number. 3 digits for the day of the year, 2 digits for the
year and 1 to indicate if it was batch A, B, C etc for that day on that
machine. If I had left the table this way it is possible that on day 001 of
year 06, batch A is produced (LOT ID is 00106A) and on the same day the
second machine is also used (LOT ID is also 00106A) In this case the two LOT
numbers are not distinct even if differnt machines were used to produce them.
This is a result of our buisness practice however when I mash the fields
together these two lots become 00106A...A on the first machine and 00106A...B
on the second machine. This is why I needed to mash the field together in
retropect however adding an autonumber filed as the primary key. TO prevent
entering duplicate date in all 4 fields, shich would be an error I would have
needed to write some verification code. BUT at least it would have only been
done once. Instead the way I solved the problem created DLookup statments
every time I wanted use the OPERATOR/SHIFT/MACHINE data tables. Talk about a
pain.

 

[Tom Ellison]

Dear Roger:

OK.

But having a multi-field primary key is not "stuck". On the contrary, you
are "stuck" with an additional column of data AND an index on it. What it
hurts is insert performance and disk space. This is a trivial difference
for a small number or rows in the table.

Almost none of the tables I create have autonumber columns. I used to
program that way, but after extensive testing, I do not find them useful.
I've heard of and tested the differences extensively. For a database of no
more the 2 gigabytes, they serve no real purpose.

The statement, "You should have an autonumber primary key for that table" is
the problem. Why?

I know most of the MVPs from previous summits except for the "new crop" and
I'm looking forward to meeting them, too. Till then.

Tom Ellison

 

[ED007]

You have confused me. I thought that a primary key could only be ONE field
in a table. However a little testing in Access showed me that I could set up
four fields (lot, oper, shift, machine) and define them all as the primary
key (joined primary key??). Three of them share a relationship with the
tables that describe operator shift and machine and therefore the values are
limited. AND access will not let me duplicate a lot operator shift machine
so the whole lot number is not repeated. So buisness practice is enforced.

But then I run into trouble. How do I use this joined primary key as the
forien key in another table. Do I need to set up all four fileds in the
forgien table as well? Or is their a way to use a field that is a
concatination of the four fields in the main table?

 

[Roger Carlson]

That's why an autonumber field is preferable. If this table has to
participate in a relationship with another table, with a multi-field primary
key, you have to put ALL the fields as a foriegn key in the related table.
Considering that the fields are all text and an autonumber field is a long
integer, the advantages of an autonumber primary key in terms of performance
and ease-of-use are overwhelming.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[ED007]

Thanks for taking the time to explain this to me. I only started this
database thing about 3 weeks ago and have learned a lot. But I still have a
lot to learn. I will know better next time.

 

[Larry Daugherty]

Hi Tom,

As Roger observed, people tend to embrace their own points of view and
the surrogate key, natural key issue tends to be joined with religious
fervor. I discovered that back in the days of Access 1.x .Someone had
a question about the issue so I innocently proposed the Autonumber
point of view. There was a s..tstorm that went on for days. The
sides were about even at that time.

I happen to come down on the other side of the issue from you but that
isn't really important. We're all free to do as we please. What is
important is that we are able to create and deliver "bullet proof"
applications to our clients.

It is also important that when you render judgements in support of
your point of view you should take care that the rationale is valid.

What follows is a few of my observations:

The greatest single value the Autonumber surrogate key is that is has
no meaning whatever in the context of any application in which it
appears.

Every professional Access developer has been deceived by well intended
lies. We've got scars. Unfortunately when the lie is revealed the
developer has to re-do some work and the client (who lied) rarely
takes responsibility and often places blame on the developer. The lie
is "this will NEVER CHANGE". By the time I'd been developing with
Access for a year or so I went to Autonumber primary keys with a will.
I've never had to go back to deal with a changed Autonumber. So,
IMHO, the development process is more predictable, more efficient and
less risky using the surrogate key vice the natural key. Those are
very compelling reasons.

I go even further and insist that the Autonumber/surrogate key never
be revealed to the end users. If they see the number it will often
suck them in. They want to play with it. They want to assign meaning
to it. Many micromanaging anal types want to dictate that you treat
it according to their tastes. It's a waste of time and energy. I
know that MS proudly displays Autonumbers in their sample apps. IMHO
they're wrong to do so.

The "additional column of data AND an index on it" doesn't ring true
to me. An index is an index whether on the natural key or the
surrogate key. Parity.

I believe that the disk space issue usually falls in favor of the
Autonumber point of view. It may favor the natural key point of view
if the ratio of the One-to-Many is very low. The "extra column"
comparison at the One side of a relation is true. On the Many side,
the Foreign Key is a long Integer; Each and every natural key will
take many times more disk space than a Long Integer. Now throw in
another Long Integer for the Autonumber primary key of the Many side
table and you'll still require less disk space. That natural key as
the Foreign Key uses more turf to do the same thing. When you
consider that for each record on the One side there may be thousands
of records on the Many side the difference can be considerable. A
very low ratio favors the natural key. A very high ratio favors the
surrogate side. The ratio where parity falls will be different for
each application and relation and the storage required for the natural
key.

That being said, I rarely consider the disk space issues regarding
Access. Our time and our costs and our delivering on schedule are
much more important to our clients. Storage and clock speeds keep
growing and getting cheaper. Only once in a while do I get concerned
with speed. Then I get really concerned until things get resolved.

I confess that I've never dug into the innards of Jet to the level of
knowing all of the nitty gritty details of how it gets things done.

I catch some of your posts. Like many of the MVPs, you are much more
patient with people than I am.

Regards,

 

[Tom Ellison]

Dear Roger:

An autonumber column is not perferable. And, if you do use one, please,
please, do not make it the primary key. Make it a separate, unique key, but
not the primary key.

The primary key is used to put the physical rows of the table in order when
you do a compact and repair. Now, which has more advantage: to put the
rows of the table in the order of a unique natural key, or to put the rows
in the order of the autonumber?

I propose there is NO advantage in putting the rows of the table in
autonumber order. This order is almost never wanted.

When you perform a report or form, you will usually be looking at the rows
of the table in the natural key order. If the rows in the table sre in this
physical order, you will experience increased performance by making the
primary key the natural key. You could still have an autonumber column in
the table and make a unique key of it. If you must, build relationships on
this. There's no disadvantage to having the autonumber column be a unique
index but not the primary key, but there are definite performance advantages
in having the natural key be the primary index.

Please, think about it. Autonumber surrogate keys are not appropriate as a
primary key. If you're going to use this technique, do it right. You're
going to need a unique natural key almost every time anyway, and performance
will benefit from this being the primary key.

Tom Ellison

 

[Roger Carlson]

As I said, I'm not interested in debating this here.

I understand that Access stores the primary key as a clustered index. I
also understand that relationships do not have to be created on a Primary
Key, a Unique Index will do. I have, in fact, created tables exactly as you
describe. However, it is not my preferred method and it is not "wrong".

I have no problem with you creating natural primary keys, if that is your
preference, but it IS a preference. Please do not confuse your preference
with gospel.

 

[Tom Ellison]

Dear Roger:

It was your statement:

"You should have an autonumber primary key for that table."

This is your preferred method, but you present it as a rule that "should" be
performed at all times. It is not I who have confused his preference with
"gospel." And that, from the beginning, was my objection.

The point is, you're permitted only one primary key or clustered index. You
should not waste it on a surrogate key, which is virtually never the order
you would want to show the rows in the table. That is something that is
simply true almost all the time. Your statement that the autonumber
"should" be the primary key is simply wrong in most cases.

Tom Ellison

 

[Roger Carlson]

I'm sorry, but your assertion that it is *almost always* preferable ("simply
true almost all the time") to store records in the order of their natural
key is simply wrong. As with most things in database design, how you use
the data determines the correct method. That's why SQL Server gives you a
choice on which index to create as clustered.

It is true that if you only want to retrieve records from a single table,
the yes, storing them in natural key order is more efficient. However, if
your table is related to another table, and you typically Join these table
to retrieve records, then natural key order is *not* more efficient. In
this case, it is more efficient to store the records in the order of the
surrogate key, that is, the field on which the Join will be made.

Most of my databases are heavily normalized and most tables participate in
relationships. Joins are much more processor intensive than retieval of
records from single tables, so I choose, in most cases, to make the
autonumber field the primary key and create a unique index on the natural
key. In these cases, it is the most efficient design.

As I said, however, I *do* at times create compound primary keys. That is
in the case of a intersection or "linking" table that is used to resolve a
Many-to-Many relationship into two One-to-Many relationships. In this case,
it *does* make sense to store the values in the natural key order, because
these will be the fields on which the Joins will be created. If this
intersection table will participate in a join of its own, I will add an
autonumber field. Sometimes I make it the Primary Key, sometimes I simply
make it a unique index. It all depends on which Join I see as more
important, that is, which will be used more.

By the way, you misinterpreted my statement. As you correctly quoted, I
said:

"You should have an autonumber primary key for that table."

Notice I said "that" table. I did not:

 

[Tom Ellison]

Dear Roger:

Your responses have led me to begin an intensive study and demonstration
project to prove whether what you say is true. I seriously doubt it is.

I've given a lot of consideration to the concept of whether a
clustered/primary index on a surrogate key would be efficient, and why that
could be. On the one hand, it would keep the rows of the dependent table
with the same key together. A clustered/PK index on the natural key does
this also, and more. Having your data in an organized physical sequence is
obviously advantageous. Consider that the natural key order is considerably
more organized. For example, for orders, the surrogate key order would keep
all the detail of each order together. The natural key would (properly
designed) keep all the detail of all the orders for a given customer
together, and in date order, ready for the screen or a report.

Well, I've been looking for a topic to author. This will be it. I'm
expecting it to take a month or more, but it's already started.

Tom Ellison