Northwind as an example

[Tom Ellison]

Dear friends:

The Northwind database is a useful example of a database. It seems
sometimes to be an example of how NOT to do things, but that wouldn't be the
impression a beginning programmer might get. Perhaps you won't agree with
this, but that's the purpose of this post.

In this database, there are autonumber values as identity keys to Employees
and Orders that perform, effectively, as the Employee Number and the Order
Number. I would not consider this to be a preferred practice.

In a practical sense, this can cause problems.

Consider a business where there are several locations. At each location,
there are sales being made. Allow that some of these locations do not have
computers connected to the network. They must take orders and submit them
(by fax or mail perhaps). They would very probably have pre-numbered order
pads, with order numbers. These could be printed by the "main office" and
sent out. Thus, each sub office has an assigned sequence of order numbers.

Or, any salesman in the field would be taking orders on his order pad.

Barring some error in printing, these hand written orders would have an
Order Number assigned from the time they are hand written. As written,
Northwind doesn't permit such practice.

I'm preparing a set of Northwind back-end variants, both with and without
autonumber primary keys. I am strongly considering having an entered
OrderNumber and EmployeeNumber column in them. I'd like to do a
sufficiently good job that these variants could become examples much like
the existing Northwind has been, but demonstrating different approaches that
can be used. My hope is to have these be an excellent display of good
practices, so when I see something in the existing Northwind I do not like,
I'd like to bring it here for comments. Any alternatives that seem proper
may become part of one of these variants, so I'd appreciate your input.

Thanks, everyone.

Tom Ellison

 

[Douglas J. Steele]

I don't want to come across as argumentative, Tom, but the example you give
is one of the least grievous sins in Northwind. For the specific complaint
you have, the appropriate thing would be to use replication, which forces
the Autonumbers to be Random (thus significantly reducing the possibility of
collision).

A far worse sin is their use of Lookup fields.

 

[Tom Ellison]

Dear Doug:

While I'm not unaware of the nasty Lookups, they aren't a target of what I'm
trying to do. By the way, Lookups are a natural result of using surrogate
identities which you wish to hide.

No argument with anything you say. In a recent post (mine on Northwind
yesterday) Albert was discussing how the sequentially assigned autonumber
keys are useful when the PK is on the autonumber. You've just shot a
considerable hole in that one!

Thanks very much. I don't find anything argumentative in what you say.
Actually, I find what you say here to be supportive of what I'm concluding
from my studies. I'm not going to draw any final conclusions until the full
testing rig is built and some empirical, replicable results can be provided.

Tom Ellison

 

[Tom Ellison]

Dear Fred:

At this point, I've split the tables, indexes, and relationships into a
separate back end. I'm not really dealing with the front end. But it's not
a bad idea!

Tom Ellison

 

[Fred Boer]

Dear Tom:

While you are at it, could you make yours a bit more attractive as well?
Some of those Northwind form backgrounds are ugly enough to curdle milk!

Cheers!
Fred Boer

 

[Guest]

Hi Tom

I notice in Northwind that there are several tables linked using AutoNumber
fields. I have found from bitter experience that this is NOT a good idea when
trying to import data from an external source into such tables. The presence
of linked tables using AutoNumber fields makes it very difficult to import
data easily. I always use another type of field to link tables.

Also all Text fields have their Allow Zero Length property set to No (the
default). I always set this property to Yes. When importing data into a
table using a query or the TransferText command, if this property is set to
No and the incoming text field is NULL the imported record is just ignored
with no warning or indication that it has failed.

Also I would set the Field Size of all text fields to 255 as this can cause
obscure problems for beginners when users say they cannot enter enough
characters into a field. Any limits are better done on the form.

Good Luck.

 

[Tom Ellison]

Dear Peter:

Some very thoughtful comments. Thanks.

I will be building two version, with and without the autonumber columns, and
comparing their performance. Not for the same reasons you mention.

I have already paid some attention to setting the column properties
properly, especially the Allow Zero Length. I'll keep on that.

Having a long text size is not at all desirable if the column is to be
indexed. The indexes would then be HUGE and slow. I also get concerned
that data may be entered or modified using the table datasheets or imports.
Having columns over-long is very nasty on the screen and on reports. They
need to be limited.

Tom Ellison

 

[Douglas J. Steele]

Allow Zero Length isn't always desirable: I believe it can prevent having
Null values in the field.

Long text size doesn't always have a negative impact on indexing. Yes, if
they choose to have many long strings in the field, it will cause the index
to be very large, but just a field that allows 255 characters vs. a field
that only allows 50 characters should make no difference to the index if all
of the fields fit within the 50 characters.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Dear Peter:

Some very thoughtful comments. Thanks.

I will be building two version, with and without the autonumber columns,
and comparing their performance. Not for the same reasons you mention.

I have already paid some attention to setting the column properties
properly, especially the Allow Zero Length. I'll keep on that.

Having a long text size is not at all desirable if the column is to be
indexed. The indexes would then be HUGE and slow. I also get concerned
that data may be entered or modified using the table datasheets or
imports. Having columns over-long is very nasty on the screen and on
reports. They need to be limited.

Tom Ellison

 

[Tom Ellison]

Dear Douglas:

While it is true that the data in the table is stored as variable length
strings in separate segments, it was not may impression that this was the
case for indexes. Can you definitely confirm this for indexes?

Having built an ISAM database engine myself, this does not seem likely to
me. The index entries in each node of a btree are usually of fixed length
with the actual values stored (and sorted) directly in the nodes. Having
these be linked to variable length strings in other segments, as the are for
the data, would be quite a challenge in building an index.

I'll make a point of testing this to see. It would be quite unexpected, but
it's probably possible. Not a good thing for index performance though, I'd
think.

Tom Ellison

Allow Zero Length isn't always desirable: I believe it can prevent having
Null values in the field.

Long text size doesn't always have a negative impact on indexing. Yes, if
they choose to have many long strings in the field, it will cause the
index to be very large, but just a field that allows 255 characters vs. a
field that only allows 50 characters should make no difference to the
index if all of the fields fit within the 50 characters.