Northwind as an example

#1 (**permalink**) 28-Jul-2006, 08:03 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9547-northwind-as-an-example.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
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

#2 (**permalink**) 28-Jul-2006, 08:03 AM

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"Tom Ellison" wrote in message
news:%23p9UMffdGHA.1204@TK2MSFTNGP02.phx.gbl...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
> 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
>
>

#3 (**permalink**) 28-Jul-2006, 08:03 AM

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

"Douglas J. Steele" wrote in message
news:uAOm8AgdGHA.4912@TK2MSFTNGP05.phx.gbl...
>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.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Tom Ellison" wrote in message
> news:%23p9UMffdGHA.1204@TK2MSFTNGP02.phx.gbl...
>> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
>>
>> I'm preparing a set of Northwind back-end variants, both with and without
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
>> 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
>>
>>
>
>

#4 (**permalink**) 28-Jul-2006, 08:03 AM

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

"Tom Ellison" wrote in message
news:%23p9UMffdGHA.1204@TK2MSFTNGP02.phx.gbl...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
> 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
>
>

#5 (**permalink**) 28-Jul-2006, 08:03 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
a bad idea!

Tom Ellison

"Fred Boer" wrote in message
news:uqOxsQgdGHA.4108@TK2MSFTNGP03.phx.gbl...
> 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
>
> "Tom Ellison" wrote in message
> news:%23p9UMffdGHA.1204@TK2MSFTNGP02.phx.gbl...
>> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
>>
>> 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
>>
>>
>
>

#6 (**permalink**) 28-Jul-2006, 08:03 AM

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
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.
--
Peter Hibbs

#7 (**permalink**) 28-Jul-2006, 08:03 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
Having columns over-long is very nasty on the screen and on reports. They
need to be limited.

Tom Ellison

"Peter Hibbs" wrote in message
news:15B5FFD8-7778-4854-A19E-DBFE387F025D@microsoft.com...
> 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.
> --
> Peter Hibbs
>

#8 (**permalink**) 28-Jul-2006, 08:03 AM

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
http://I.Am/DougSteele
(no private e-mails, please)

"Tom Ellison" wrote in message
news:e$WHX1gdGHA.5016@TK2MSFTNGP04.phx.gbl...
> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
>
> Tom Ellison
>
>
> "Peter Hibbs" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
> news:15B5FFD8-7778-4854-A19E-DBFE387F025D@microsoft.com...
>> 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.
>> --
>> Peter Hibbs
>>
>
>

#9 (**permalink**) 28-Jul-2006, 08:03 AM

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

"Douglas J. Steele" wrote in message
news:egtmjThdGHA.5116@TK2MSFTNGP02.phx.gbl...
> 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
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Tom Ellison" wrote in message
> news:e$WHX1gdGHA.5016@TK2MSFTNGP04.phx.gbl...
>> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
>>
>> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9547
>> 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
>>
>>
>> "Peter Hibbs" wrote in message
>> news:15B5FFD8-7778-4854-A19E-DBFE387F025D@microsoft.com...
>>> 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.
>>> --
>>> Peter Hibbs
>>>
>>
>>
>
>