I will try my best to answer some of the questions after the Standard Operating Procedure webcast for 24 Hours of PASS.
First one is what design tools are out there. Here is a list I found from Louis Davidson’s book Pro SQL Server 2008 Relational Database Design and Implementation:
- All Fusion ERwin Data Modeler
- Toad data Modeler
- Visible Analyst DB Engineer
- Visio Enterprise Edition
I would not suggest using the one built into SQL Server, because if you change the design then and save it, the tables will be updated automatically with the changes.
The second one had to do with having views in a data modeler tool and showing the relationship between other tables or views: I do not have a good answer to this, and have asked others with no available tool.
3. What ER diagram tool are you using for the presentation? Which do you recommend?
I used Visio for my presentation because it is what I have a license for, but the companies I have worked for recently use ERwin.
4. Where can we download the example document I used?
I cannot let you have it because it is from a company I worked for, but I did relink the Parts 1 thru 6 of a generalization of the SOP.
5. One comment was for me to read up more on normalization –
This is true, I gave a brief (very brief) explanation of 1st, 2nd and 3rd normal forms. I should not have done that and just referenced a website. Or suggested to go to the PASS Summit and watch my session 3rd Normal Form: That’s crazy talk!!!
6. Singular or Plural on table name?
Singular, like Louis says, pick a standard and be consistent.
7. The CHAR(1) for IsActive is much better for using the column directly in Reports (for example - If the value is Y or N it is much easier to simply write the sql to display than to logically determine the meaning of the Boolean.
That is a good point, reporting is very important. Some report writers might even convert Boolean to a text equivalent.
8. Please repeat the contact/blog/web site information. Thank you! --> Here it is
9. Will the recording be available - answered yes.
Keep checking back on 24HOP site, or you can watch it from the PASS Data Architect Virtual Chapter
10. will there be an example of the SOP available? –> see Number 4 above
11. Have you found an ERI application which allows joins to be defined on a view. Visio and Erwin do not allow this. –> see Number 2 above
12. I recall sometime ago while trying to use Visio 2010 to import SQL 2008 database for database diagrams it wasn't supported. Any ideas on future support for Visio and database diagrams?
Look on the database menu choice to reverse engineer a database, then it connects thru ODBC (DSN)
13. Preferred data type for ID?
It depends on the amount of rows in the table, but you can go with tinyint, smallint, int or biint