SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

24 Hours of PASS: Answer questions…

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
  • ER/Studio
  • 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 Smile

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

God Bless,


The Smiling DBA

Thomas LeBlanc is a Business Intelligence Consultant/Data Warehouse Architect in Baton Rouge, Louisiana. He uses his 25+ years in IT to help develop OLTP systems with normalized databases for high-performing T-SQL and end-to-end dimensional data marts using SSIS, SSAS, PPS, and Excel. His SQL Server certifications include MCSA 12, MCITP 08 BI and DBA, MCITP 2005 DBA, and MCDBA 2000. As a PASS volunteer, he is current chair of the Excel BI virtual chapter, past chair of the Data Architecture virtual chapter, and past virtual chapter mentor. He has helped the Baton Rouge SQL Server User Group with SQLSaturdays and speaks at local IT meetings.


Leave a comment on the original post [thesmilingdba.blogspot.com, opens in a new window]

Loading comments...