NotNorthwind - Update #1 - All Your Northwind Are Belong To Us
I 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. He is a failed stand-up comic, a cornrower, and a book author.
About Newsletter
TheProkrammer
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.
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
"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."
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?
@[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.
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
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.
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!
Comments are closed.