Scott Hanselman

NotNorthwind - Update #1 - All Your Northwind Are Belong To Us

June 26, '08 Comments [21] Posted in Musings | NotNorthwind | Source Code
Sponsored By

imageI posted a little rant against the Northwind Database a few weeks back, and suggested that we, the community, create a better demo database than Northwind.  I proposed the name NotNorthwind.

There were some varied and interesting responses.

From Scott Mitchell:

Yes, it is far from perfect and could use some updating with regards to the date/time values and the category pictures, but those warts aside, it does a good job at what it was designed to do.

From Brian Sullivan:

I feel the same way. I have to stifle a groan whenever I hear the word "Northwind" come out of presenter's mouth. I've even jokingly said that Microsoft ought to have an internal metric for new technologies called "TTN", "Time to Northwind." In mathematical terms:
Product Coolness = 1 / TTN

From Duncan Smart:

We don't a need a new one! Pubs FTW! :-)

Steven Smith says Whither Northwind?

Enter NorthWind, the HTTP standard of databases, understood by virtually all Microsoft developers without need for preamble.  It just works.  With the words, "I'm using Northwind for my database." I now have the complete understanding of 95% of the people in the room - we're all on the same page - and I can continue with the actual point of the presentation or demo, which is not, has not, and probably will never be, "why this database isn't Northwind."

And Jeff Atwood from Twitter ;)

@shanselman rejects the standard MSFT Northwind database. What next? "Hello World" isn't good enough for you? Snob!

There were also a lot of great comments and ongoing discussion the CodePlex site's Discussions tab and project comments. We brainstormed alternative domains like Medical, Insurance, Media, and many others. Others suggested that AdventureWorks was a fine replacement.

ASIDE: Some folks suggested just updating the dates in Northwind to the present, but as attractive as the idea of adding ELEVEN YEARS to these columns is, I just didn't feel like doing this (Thanks to the folks on Twitter for their ideas, though!).

use Northwind
update Employees set BirthDate = dateadd(yy, 11, BirthDate)
update Employees set HireDate = dateadd(yy, 11, HireDate)
update Orders set OrderDate = dateadd(yy, 11, OrderDate)
update Orders set ShippedDate = dateadd(yy, 11, ShippedDate)
update Orders set RequiredDate = dateadd(yy, 11, RequiredDate)

We had a SkypeCast call today at noon and it was agreed that Northwind does have some redeeming qualities. It's simple, it works, it's understood and there's a pile of demos written against it. We rethought the requirements.

As a small group, we've decided to extend Northwind. We'll still call it NotNorthwind (although Super Northwind 2000 and "Microsoft Visual Northwind Enabler SP2 RC0 Beta1 July Refresh Plus Pack" were also possibilities), though, but we want to add a bunch of features that should make it a more interesting database for demos/prototyping/experimenting.

It's worth pointing out that I (and we) are not interested in showcasing the shiniest, latest new thing in SQL2008 with this small effort. We're just trying to have a little fun, while updating a very old database with some interesting and fun features in the hopes that our demos/prototypes/experiments/whatever that use Northwind could possibly become more interesting.

  • Product Reviews and Ratings
    • This new area should introduce a number of interesting, but easily understood concepts that can be used to showcase everything from whatever cool AJAX toolkit you're demoing to how flexible your CodeGenerator or ORM is.
    • Perhaps extending reviews to "x people found this review helpful" as well. Also, wish-lists, etc.
  • Tagging
    • In parallel to, and juxtaposed against the existing Product/Category relationship, tagging, and social tagging, introduces some interesting DB issues as we'll want to tag anything not just products, resulting in a many-to-many heterogeneous situation.  It also makes for some potentially interesting User Interfaces.
  • Compatibility
    • We're going to pretend that the existing database can't be changed, but only extended.  This way folks with existing Northwind Demos will "just work" against NotNorthwind. This plug-ability should also encourage people with demos to extend their existing those of things like web frameworks but are just using the database as a place to find scenarios.
    • We'd like it to support (either via creative SQL or multiple versions) multiple databases like mysql, SQLite, Oracle, etc.
    • We'd like it to be an existing "legacy" database that ORM tools could each build on top of so we could compare apples to apples.
  • Sample Data
    • At some point it'd be cool to make the database REALLY HUGE to test the scale of both it, and the things folks build on top of it.
    • We'd like pluggable Sample Data, so we'll make the structure file separate from the sample data. Basically Database "Themes" as not everyone wants the standard Microsoft "scrubbed" names.
  • Alternate Domains
    • We also think that the Northwind world could be explored with more "Vertical" things like these. These could also be done without changing the core database.
      • Expense Tracking and Approval
      • Accounts Receivable and Payable and Payroll
      • Business Intelligence - Warehousing, Sales, Trends

That's about it. I'll update the Issue Trackers and we'll try another SkypeCast same time next Thursday around noon. I'll announce the SkypeCast URL on Twitter. (AYNABTU)

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. I am a failed stand-up comic, a cornrower, and a book author.

facebook twitter subscribe
About   Newsletter
Sponsored By
Hosting By
Dedicated Windows Server Hosting by ORCS Web
Friday, June 27, 2008 12:16:49 AM UTC
An Aside on the Aside, you could write up a simple script to go through systables and syscolums to auto update ALL datetime columns in the database, wouldn't be all that terrible, might be a worthy stopgap if the dates really bother you THAT much.
Friday, June 27, 2008 12:37:06 AM UTC
So if it's not Northwind, why don't you just call it Southwind?
Friday, June 27, 2008 12:38:51 AM UTC
Josh - LOL.

TheProkrammer - Ya, I thought about that, but I'm only about 70% good at SQL and didn't have the idioms on the tip of my tongue.
Scott Hanselman
Friday, June 27, 2008 1:38:20 AM UTC
@SHanselman, here ya go brother:


begin tran

declare @tablename varchar(500)
declare @columnname varchar(500)

declare update_datetime cursor for

select so.name, sc.name from sys.columns sc
inner join sys.types st on sc.user_type_id = st.user_type_id and st.name like '%datetime%'
inner join sys.objects so on sc.object_id = so.object_id and so.type = 'U'

open update_datetime

fetch next from update_datetime into @tablename, @columnname

while @@fetch_status = 0
begin

exec('
update '+ @tablename + '
set '+@columnname+' = dateadd(yy, 11, '+@columnname+')
where '+@columnname+' < getdate()')

exec('print ''Updated '+@tablename+'.'+@columnname+'''')
fetch next from update_datetime into @tablename, @columnname
end
close update_datetime
deallocate update_datetime
commit tran
Friday, June 27, 2008 2:05:14 AM UTC
The Northgale project is interesting NorthGale. Here is a quote from that page,

"Northgale has the same tables and indexes as Northwind, but I have exploded the data so that instead of 830 orders, there are 344035 of them. To install Northgale, run Northgale.sql. The data in Northgale is taken from Northwind, so this database must be in place. To install Northgale, you need 4.6 GB of disk space on SQL 2005 and 2.6 GB on SQL 2000. Once installed, the database takes up 520 MB."
Friday, June 27, 2008 2:46:01 AM UTC
I'd like to see the community get rid of "Hello World" and "Foo" and "Bar". If you want to talk about being sick of something. I'm sick of those 3 things. And while I'm at it, lets quit saying "stack" and "story" behind everything as well :)
Anthony Yott
Friday, June 27, 2008 3:35:00 AM UTC
As corny as it sounds, I think developer/presenter acceptance of the extended Northwind database would increase substantially if it was simply called Northwind+. "NotNorthwind" would just create FUD. Just my 2 cents.
jhigh
Friday, June 27, 2008 8:39:32 AM UTC
Must agree with the "NotNorthwind" business. It IS Northwind after all, is it not? You extend the schema for new features, so it's basically Northwind Rel.2, Northwind+, Northwind2008, Northnorthwestwind or whatever other versioning scheme you may come up with.
Friday, June 27, 2008 9:37:59 AM UTC
Anthony Yott - If the purpose of Hello World is to demonstrate a programming language then I find http://99-bottles-of-beer.net/ to be a good replacement. Typically it shows loops, conditional branching and a few irritating edge cases (like when there are 1 or 0 bottles) that seem to be annoyingly common in programming and prevent it from being a neat, tidy little solution.
Friday, June 27, 2008 9:56:23 AM UTC
INADD (I'm not a data dude) but it might be possible to make some significant design changes to NorthWind while maintaining backwards compatibility by using some of the techniques in "Refactoring Databases" by Scott W. Ambler and Pramod J. Sadalage.
Friday, June 27, 2008 10:06:53 AM UTC
It also seems to me that the incorporation of Universal Data Model would be a good fit as well. I've found these models to give good direction towards building flexible databases and systems. http://www.univdata.com/
Friday, June 27, 2008 12:28:26 PM UTC
I totally agree that Northwind need to be extended. It's so not popular anymore that it doesn't even come installed on Microsoft SQL 2005. What I would like to see is a SQL file to run to extend Northwind in a predictable way.

For exemple:
- A script to add product review support
- A script to add tagging support
- A script to [PLEASE FILL]

It wouldn't be "NotNorthwind" but it would be "Northwind Extensions 1.0 RC0 Beta 1" :)

What do you think Scott?
Friday, June 27, 2008 12:59:54 PM UTC
I will be interesting setting an example using Entity framework. We have a lot little examples, but lack more global samples applying best practices.
Friday, June 27, 2008 1:35:10 PM UTC
It sounds silly at first glance, but I think the points on naming make sense. "NotNorthwind" has an obviously negative tone to it and will probably discourage people from using it. As others have said, it IS Northwind. I would agree with a name that reflects that and also allows the DB to be extended in the future. Without getting into a formal major.minor[.build[.revision]] versioning scheme, "Northwind2008" is a pretty good choice so far, IMHO.
Friday, June 27, 2008 2:01:44 PM UTC
I agree with the negative connotation of NotNorthwind, especially given that it IS Northwind. NorthwindPlus or something in that vein might be more appropriate. But I am also aware of the number of projects that never get off the ground because they can't get past the minutiae :)

@[ICR], the primary purpose of "Hello World" is not to demonstrate all the features, or even the most popular features, of a given language. Its primary purpose is to allow a new developer to accomplish something as quickly as possible in a new language while demonstrating its basic structure and syntax. The fact that it has no logic or even mildly complex code structures is the whole point.
Friday, June 27, 2008 2:24:12 PM UTC
How about Northwind++ ?
scantwell
Friday, June 27, 2008 2:40:41 PM UTC
Shouldn't it just be called GNN?

:-)
Kevin Kuebler
Friday, June 27, 2008 2:50:32 PM UTC
Scott,

As a long-time and widespread Northwind user, I'd prefer Southwind (the maker of aircraft cabin heaters that have kept me warm on many cold nights at 9,500 ft.).

I use Northwind rather than AdventureWorks because AW is overly normalized and the added joins confuse readers by diverting their attention to database structure and not the issue at hand.

I'd also suggest some additions:

1. Add an item number (tinyint) and shipped (bit) field for backorders below.
2. Invoices from shipped order with backorders. An sproc to issue an invoice with and without backordered items would be nice.
3. A relation table for a one:many relationship between sales orders and invoices to support the above.
4. Inventory transactions table to provide on-demand calculated data for related field in Products table
5. Change Products table to many:many relationship with suppliers and add multiple suppliers for some products
6. A Purchase orders table with line items as in #1 with received field working with inventory table.
7. Sproc to automatically add or remove timestamp field for each table
8. Sproc to automatically add or remove usual audit fields for each table
9. Sproc to automatically add or remove Sync framework fields for each table
10. Sproc to substitute surrogate for natural PKs or vice-versa
11. Product reviews (by customers) would be great

Deletions: Remove CustomerDemographics, Territories, Region

Thanks for considering the above,

--rj


Friday, June 27, 2008 4:08:50 PM UTC
Don't forget we need a starburst on the northwind logo that says "Web 2.0 compatable!"
Tuesday, July 01, 2008 7:28:47 AM UTC
Northwind once won me some loot at an MSDN event.

The presenter was playing around with Office Accounting and was showing how to flip between different datafiles. One was the default demo database, and the other was some stuff imported from Northwind. He went back and forth a couple of times, and went through the rest of the demo. Near the end there was an error that had to do with which database was currently connected. The presenter had anticipated this and offered the standard shirt to whoever knew what happened. I was the only one who recognized a name on the screen as having come from Northwind instead of the default database.

As annoying as it is, Northwind still has a warm fuzzy place in my heart.
kettch
Tuesday, July 01, 2008 7:58:44 AM UTC
Sounds nice, if you can use NotNorthwind in places where you can use Northwind (ie backwards compatible) that would be great.

How about membership, roles and profiles (users and passwords). Maybe just the standard ASP.NET membership (or something better :p ).

Also, I don't know if this is the case today with Northwind, but some tests for ORM patterns (like mapping one entity to two tables, or mapping two entities to one table).

Either way, it's a nice idea for a project, good luck with it!
Mike
Comments are closed.

Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.