Database Modeling / ERwin

In ERwin I found I needed to use the physical view if I wanted to create a join table against the same table for purposes of creating a hierarchy without adding a nullable self-referencing parentId column to the target table. Looks like there are a lot of resources on SQL Hierarchies (http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database) So I'll have to study a bit -- not to mention, I'll need to find out whether an ORM like NHibernate will handle it.

Two open source options for data modeling I found are mogwai ER Designer NG and SQL Power Architect.

 Forward engineering to SQL Server 2008 works well in ERwin, except for some quirks: It adds extraneous collation metadata; it forces you to uncheck a lot of options e.g. uncheck Schema Create, uncheck all Trigger checkboxes, and those changes aren't saved between sessions. Looks like you can save an option set though, extra work...

To get crows feet notation, choose Information Engineering instead of IDEF1x in Model properties. I learned that and other stuff from http://www.isqa.unomaha.edu/wolcott/tutorials/erwin/erwin.html