Northwind Template

[MichaelB]

I'm very new to this, but can I just rename the Northwind sample db to my
company name and delete all data in the db, then just revise each table and
form to suit my needs? I know, that's what a template is for, but I was just
concerned that there are elements of this "sample" template that don't work
and that I may need advanced user/developer knowledge to understand and
manipulate. The db seems pretty much suited to any retail business like the
one I am starting.
Thanks for any suggestions/help.

 

[Ken Sheridan]

No reason why not, although Northwind isn't really a template as such, but a
sample application designed to illustrate the basic principles of relational
database design using Access. One thing to be aware of, however, is that the
Customers table in Northwind is not fully normalized and contains redundancy.
While it can be used as it is, this does leave the door open to the
possibility of inconsistent data. In fact I use it as an example in my
standard summary of normalization, which contains these paragraphs:

"To see how a database is made up of related tables take a look at the
sample Northwind database. Its not completely normalized in fact
(deliberately so for the sake of simplicity) but illustrates the main
principles of how tables representing entity types relate to each other. An
example of its lack of proper normalization can be found in its Customers
table. You'll see that this has City, Region and Country columns so we are
told numerous times that São Paulo is in SP region (as is Resende) and that
SP region is in Brazil. Not only does this require repetitive data entry,
but more importantly it opens up the risk of inconsistent data, e.g. it would
be perfectly possible to put São Paulo in California in one row and
California in Ireland! Proper normalization as I described above would
prevent this as the fact that São Paulo is in SP region would be stored only
once in the database as would the fact that SP region is in Brazil and
California is in the USA.

An example of what at first sight might seem to be redundancy, but in fact
is not, can also be found in Northwind. The Products table and the
OrderDetails table both have UnitPrice columns. It might be thought that the
unit price of a product could always be looked up from the Products table, so
its unnecessary in Order Details. However, the unit price of a product will
change over time, but each order needs to retain the price in force at the
time the order was created. Consequently a UnitPrice column is needed in
both tables; that in products holds the current price and is used to get the
value for that in Order Details (code in the ProductID control's AfterUpdate
event procedure in the Order Details Subform does this), which then remains
static when the current price (in products) changes. In each case UnitPrice
is functionally dependent on the key of the table, so there is no redundancy"

To normalize this table it would have to be decomposed so that a foreign key
CityID column references a Cities table with a unique numeric CityID primary
key, which in turn references a Regions table, which in turn references a
Countries table. However if you are happy to live with the design as is it
will do the job provided you are careful not to enter inconsistent data where
the design allows this.

Another peculiarity of Northwind which really doesn't make much sense is the
use of a structured CustomerID. It serves no real purpose and an autonumber
would a lot simpler, allowing you to enter the customer name for a new
customer without having to bother about working out the 5-character code for
the CustomerID, which would be given a unique numeric value automatically, as
is the case with all other referenced tables in Northwind. Again its for you
to decide whether you can live with this oddity.

Ken Sheridan
Stafford, England

 

[MichaelB]

Thanks Ken.

I understand what you say, but since I am new, I guess I don't fully
understand everything that you wrote or how to fix each item throughout the
entire database, but I will give it a try. I guess my next question is,
should I just use this db as a guide and create my own from scratch, although
it's probably a toss-up as to which is the more difficult task.

My other issue is that I want to create my own CustomerID instead of
autonumber. I have created a format for the way I want the CustomerID to be
and have posted this question in another discussion group. I want to create
a CustomerID based on the following criteria:
1st 2 characters of the last name
1st 2 characters of the first name
2 digit month
2 digit year
5 digit sequential number
example: Ken Sheridan
SH + KE + 10 + 08 + 00001 = shke100800001

Since it is essentially a sequential number since the last 5 digits will
just increase by 1 every time a new record is created, I thought that this
might be something that could be done, although I am not suggesting that it
is easy.

Thanks again for your advice. Michael

 

[Ken Sheridan]

Michael:

On the one hand there's no point reinventing the wheel, though on the other
hand you'd learn more about database design as you go along if you start from
scratch. It really depends how far you want to modify Northwind. You might
find that you'll modify something quite small and then spend a long time
pinning down what you need to do to something else to accommodate your
original modification.

I would not create a column for your structured CustomerID. It would
introduce redundancy. As you'll have Firstname and LastName columns already
you can get the first four characters from those. I'd suggest you include a
DateStamp column in the table of date/time data type and set its DefaultValue
property to Date(). Then for the sequential number use a column of long
integer number data type, CustNumber say, and increment it in the Customers
form's BeforeInsert event procedure with:

Me.CustNumber = Nz(DMax("CustNumber","Customers"),0) + 1

Don't be tempted to use an autonumber for CustNumber; an autonumber is
designed to guarantee uniqueness not sequence and can't be relied on for the
latter.

So you only have to enter the customer names, the rest is done automatically
and you don't need to show the DateStamp or CustNumber fields anywhere in the
application. In forms, reports etc you can show the structured CustomerID in
an unbound control with a ControlSource of:

= UCase(Left(LastName,2) & Left(FirstName,2)) & Format(DateStamp,"mmyy") &
Format(CustNumber,"00000")

As the CustNumber values will be unique make this the Customers table's
primary key and use an identical column in related tables, e.g. Orders, as a
foreign key, indexing them non-uniquely in those tables.

One final word of warning. Don't under any circumstances use the 'Lookup
Wizard' for the data type of foreign key columns like CustNumber. Just use a
number data type and set its Field Size property to Long Integer (which is
the default for numbers as it happens). You can 'look up' the text values in
combo boxes on forms; there is no point doing it in a table design where it
causes a number of problems. See the following for the gory details:


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


Ken Sheridan
Stafford, England