Guest Editorial: Improving Northwind

  • Phil Factor


    Points: 20064

    Comments posted to this topic are about the item Guest Editorial: Improving Northwind

    Best wishes,
    Phil Factor
    Simple Talk

  • Helen Trim-836693

    Old Hand

    Points: 396

    I used to be a software trainer specialising in Access from beginner to VBA, so I have a great fondness for Northwind - Nancy Davolio, Alfreds Futterkiste, Laughing Lumberjack Lager.....

    I especially liked the version that shipped with Access 95, where the photo of Nancy had been replaced but they had left her date of birth the same, so it appeared that the attractive young blonde lady was in her 50s.

    As you say, it is an excellent way to learn about databases and part of it's strength is it's simplicity. I vote for leaving it as it is.

  • archie flockhart


    Points: 2339

    A generation of learners, since 1997

    Longer than that, surely. I think it was available in Access 2.0 ( around 1993 ?), maybe earlier.

    I wonder where Nancy Davolio is now ?

  • Open Minded


    Points: 1842

    you should have seen the visual foxpro version of it. it was quite a complex OOP example, which i could take off a few pieces at a time to use.

    i am quite proud to have my db lineage start from DBaseIII+ and VFP. later on, i didn't have much problem about taking care of indexes, relational keys and the need for normalization of tables to reduce disk usage.

    even at this time when there are affordable, 400GB servers, a well-normalized database still provides better speed. before anyone debates on normalization, i agree to all your points provided performance is better.

  • Ray Hastie

    Default port

    Points: 1463

    Let's have fun and try to improve it.

    Count me in.

  • blandry

    SSCarpal Tunnel

    Points: 4821

    Geospatial data? Taxonomic data? Temporal data? Geez Phil, isn't the Northwind database supposed to be a learning database used to bring people interested in becoming SQL Gurus up through the ranks? When you ask the question; "Can't we as a community, do better?" - are you asking this only for the Obi Wan Kenobis of SQL Server? Or does the "community" also include the people who just getting wet behind the ears with SQL? Does it include the people just tracking your average mundane business data? After all, thats the greater part of the SQL population - not everyone NEEDS a geospatial record of the widgets their business keeps track of.

    In the late 90's I worked for a small company where almost everyone knew a little SQL - enough to run a query and get the answers they needed. Seems over the last 10 years as SQL has become more and more complex, with new often confusing features to newbies, SQL expertise is harder to define and we seem to be approaching a place where only the "few Wizards" will be "in the know". Is this really a good thing?

    Maybe what we need are two databases - one with good 'basics' use, like Northwind, and then another with the kind of data that Phil, you are talking about. As an old-timer, at this game for decades, I am of course impressed with SQL these days - but I am also aware that fewer and fewer people are interested in it because of its complexity. Again I ask, is this a good thing?

    Well, to be topical... The financial industry just collapsed on the back of complexity and it's confusion that even the people selling the instruments could not decipher. In fact today, we don't even know how big a mess we're in because it will take years to figure out these complex financial inventions that only a very few people even understand. Is THAT a good thing? Nope - and maybe we should all take that as a warning.

    KISS - Keep it simple, stupid - its not all that bad an idea, and before anyone starts producing some new demo data, I sure would like them to consider the BIG picture for a change - not just the "boy thats cool" picture. After all, though some companies are doing very cool stuff, most are just trying to keep track of things and do business. We should not lose site of that. Its not a dishonorable thing.

    There's no such thing as dumb questions, only poorly thought-out answers...
  • Steve S.-542474


    Points: 425

    I think that there is plenty of room in NorthWind for both simple and complex tables. A well designed database should include simple tables as well as complex relationships, data types, etc. When you are learning basic table structure, you don't need to know about stored procedures - but they can still be in the database, waiting patiently for you to get around to them. Viva complexity! - as long as we keep the simple stuff. I would love to have samples available for some of the medium to heavy-duty concepts that SS supports!

  • mhaskins

    Ten Centuries

    Points: 1145

    Why not still keep it simple with multiple training databases. So, keep Northwind as a general database. Then you can have one simple database that shows how to use analysis services - and it would be built with just SSAS in mind. Then have another one for spatial data, etc. They could still be small, simple and concise - and used for specific training purposes. Why does it have to be one huge database that covers everything?


    I have come to the conclusion that the top man has one principle responsibility: to provide an atmosphere in which creative mavericks can do useful work.
    -- David M. Ogilvy

  • Someguy


    Points: 2207

    Improving Northwind is a good idea. However, let's remember that databases are used in a lot of diverse ways. So, let's not make the mistake of thinking that there can be one database that best demonstrates all of the recent developments in db technology and theory. For example, we may need a database for "Voyager Transportation" to show the power of geospatial data.

    Having said that, I recall a number of debates on this forum with topics like "why bother with CLR?" (stated in more polite terms) and "why bother with XML in a database" (I think the editorial writer for that one was a little more blatent in his dislike for XML). If Microsoft is going to put the resources into adding features like these and more to SQL Server, perhaps it would behoove them to come up with some examples of how these technologies can shine in a proper setting?

    If it's possible to put multiple developments into one database, then great. Let's just be careful as Database "experts" to avoid the mistakes that we always criticize others for doing, such as creating a swiss army knife database that contains a lot of technology, but which doesn't really make any sense in the real world.

    “Politicians are like diapers. They both need changing regularly and for the same reason.”

  • GSquared

    SSC Guru

    Points: 260824

    I like the idea of a database that gradually increases in complexity as you progress through various training exercises in it.

    Start out with a table of customers, a table of salespeople, a table of products, and a table of orders, and perhaps a many-to-many join between orders and products. Very simple structure, just enough to get an idea of what a relation (table) is, what normal forms mean, and how simple foreign keys work. Something a real business could use as a starting place for replacing a file cabinet of paper order forms. Start out with contact data in the customers table, to illustrate the weaknesses of that violation of normal form, but still make the database human-understandable.

    Then, with a script that encompasses the necessary DDL and insert tasks, add in inventory and a running totals problem, maybe add in employee hierarchies, and perhaps a more complex way to track customer contact data, such as a table of addresses with customer ID and address type, separation of address and phone and e-mail, and so on. Lesson covers running totals, adjacency hierarchies, and higher normal forms.

    Another script with necessary DDL and DML, and you morph the database to include billing data including encrypted credit card numbers, more employee data including encrypted SSNs (or whatever), more complex database security access including logins and groups. Lesson is on security and encryption.

    And so on, one script per set of lessons, with the database getting more and more complex each time. It would be easy enough to build that way, and would progressively increase SQL skill and knowledge. Would be slightly more complex to make sure that no script was dependent on any other script having already been run, unless it is a more advanced lesson specifically building on the prior lesson. E.g.: Security 2 could be dependent on Security 1, but independent of Normal Forms 1.

    Does that sound like a good compromise between starting out simple and still being able to get the most complex and advanced subjects in there?

    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • MarkK-959639

    SSC Journeyman

    Points: 77

    One must learn to walk before they can run. Leave the Northwind database for what it is; a learning tool for beginners.

    There is also room for a more advanced learning aid. Feel free to take NorthWind, rename it (how about SouthSeas), and make it whatever you want. The problem will be that once you get beyond the basics, to material needed to be covered will have increased exponentially.


  • Steve S.-542474


    Points: 425

    The more that I think about it, the more like GSquared's idea of scripting additions to the database. Add what you need when you need it. Some of the scripts would themselves be good examples for folks new to that aspect of db management.

    I like it a lot!

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719720

    Scripting additions isn't a bad idea. We could use separate schemas as well for new features so that older scripts/demos still work with the original Northwind structure. The scripting idea works as long as it's an extension to the schema, and it's not altering tables.

    However one thing I worry about is size. Having too large a db for demos becomes a touch of an issue for some people. It's less portable, or more of a hassle. What about Filestream? Just getting that working to get AW setup is a pain.

    I tend to think that we ought to have separate sample dbs. Put a NW_Filestream db out there for people that want some samples. Have views that link back to NW, give scripts to enable security to the new db. Have a separate one for XML, Spatial, etc.

  • GSquared

    SSC Guru

    Points: 260824

    Separate databases also works. The scripting idea is based on the idea that any changes the person made to their own copy of the database would be carried over to the next lesson. If, for example, they want to create some new products that more closely resemble something they work with on a day-to-day basis, in the Products table, then the script for Filestream 1 will leave those products in there and they can add Filestream functionality to those (maybe PDFs describing the products) as part of the lesson.

    Either one works.

    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • David Reed-223505


    Points: 2456

    Let me see if I get this right, Phil... You want to create a committee to "replace" a database designed by a committee?


    Did you come to the Official AdventureWorks Committee™ meeting @ the MVP summit? I don't remember seeing you, so if you were lurking there it was in an uncharacteristically quiet fashion. Heh.


    BTW, the Northwind Community Edition idea has been tried. Scott Hanselman started a CodePlex project to do exactly what you described last year. The committee stalled after everyone voted unanimously that "We <3 Northwind!" and promptly referred the agenda to the Subcommittee on What to Do Next. There was also a referendum sentenced to the Subcommittee on Tastes Great / Less Filling about whether CLR, XML or other "non-relational" features should ever be found in a Righteous Database™ like Northwind.


    If you'd like to resurrect it, I'm game to help y'all try:[/url]

    If you just want to download the Northwind script, it's in Code Gallery:[/url]

    Yes, pubs (the red-headed stepchild) is there, too.


Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply