Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Northwind as an example

Discussion in 'Information Technology' started by Tom Ellison, Jul 28, 2006.

  1. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Examples of Charitropakhyan Charitropakhyan Jan 24, 2016
    Need sample/example Ardas for Gurpurab Questions and Answers Nov 14, 2013
    Guru Nanak Dev ji: A Positive Example of Respectful Dialog! .. Preamble Sidh Gosht Aug 19, 2012
    How Can People of Faith be Good Examples for Peace? Interfaith Dialogues May 29, 2011
    Canada Knox Policeman Sets Example Breaking News Mar 22, 2011

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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" <tellison@jcdoyle.com> 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.
    >
    > 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
    >
    >
     
  4. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    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" <NOSPAM_djsteele@NOSPAM_canada.com> 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" <tellison@jcdoyle.com> 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.
    >>
    >> 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. Fred Boer

    Fred Boer
    Expand Collapse
    Guest

    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" <tellison@jcdoyle.com> 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.
    >
    > 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. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    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" <fredboer1@NOyahooSPAM.com> 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" <tellison@jcdoyle.com> 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.
    >>
    >> 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
    >>
    >>

    >
    >
     
  7. Peter Hibbs

    Peter Hibbs
    Expand Collapse
    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.
    --
    Peter Hibbs
     
  8. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    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


    "Peter Hibbs" <peter.hibbs@btinternet.com.NO_SPAM> 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
    >
     
  9. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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" <tellison@jcdoyle.com> 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.
    >
    > Tom Ellison
    >
    >
    > "Peter Hibbs" <peter.hibbs@btinternet.com.NO_SPAM> 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
    >>

    >
    >
     
  10. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    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" <NOSPAM_djsteele@NOSPAM_canada.com> 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" <tellison@jcdoyle.com> 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.
    >>
    >> Tom Ellison
    >>
    >>
    >> "Peter Hibbs" <peter.hibbs@btinternet.com.NO_SPAM> 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
    >>>

    >>
    >>

    >
    >
     

Share This Page