?
Solved

documenting tables and relationships

Posted on 2011-10-03
11
Medium Priority
?
224 Views
Last Modified: 2012-06-27
I am working on a rather complex database right now and realize I have forgotten why I even created ceartain columns...I can usually figure it out going back to my notes or by setting up diagrams but this may not be the ideal way to do things.

Does anyone have a "best practices" suggestion or tip they use when documenting a database? any advice here would be appreciated.
0
Comment
Question by:Shawn
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 25

Accepted Solution

by:
jogos earned 1000 total points
ID: 36908360
Use description to comment. And as you realised document on creation, later it'll be forgotten.
Only the 'description' can add that little extra value on the names off the db-objects and when combining that with an easy overview of the whole off the existing key's, indexes, defaults... that's a lot.

http://devio.wordpress.com/2009/01/07/automatically-generate-mediawiki-documentation-of-ms-sql-database/
http://www.mssqltips.com/sqlservertip/1250/sql-server-documentation-tools/

As you also point out 'find it in my notes' ... should be find it in the documentation off the demanded functionality/executed changes.

And off course version control on your db-changes is the way to allign them with the changes in your application. http://msdn.microsoft.com/en-us/library/aa833229(v=vs.80).aspx
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 36908721
..before doing phisacal design -- > make logical with clearly named tables- columns relations: you can use Erwin tool that can help you in uch way of db design:

also you can use Visio to generate db diagram and put your comments there
--
and good to have specs
---

<forgotten why I even created ceartain columns>
check what data is there (if you have any) - > it can refresh your memory
--
0
 
LVL 1

Author Comment

by:Shawn
ID: 36911131
jogos:
 >> Use description to comment
completely forgot about that one. thank you
>> and when combining that with an easy overview of the whole off the existing key's, indexes, defaults
easy overview...do you mean using diagrams in MSSMS?

I am not really looking for a 3 party app right now unless it is free and the setup is quick and easy

EugeneZ:
isn't Erwin a fairly expensive complex tool?
regarding Visio and db diagrams are they better than the diagrams already in MSSMS?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 13

Assisted Solution

by:AielloJ
AielloJ earned 1000 total points
ID: 36911466
Shawnaraxi:

A good tool, with a free version available, is TOAD. (www.quest.com)  There is a version for most databases includign SQL-Server, mySQl, and Oracle.  As far as documentation, I strongly suggest using scripts to create all tables and relationships as opposed to graphical tools.  The script filenames are numbered in sequence, basic operation performed (CREATE, UPDATE, DELETE), and descriptions of their function.  The scripts are kept and archived. ie:

0001 - UPDATE - Root Password.sql
0002 - CREATE - Help Desk Database.sql
0003 - GRANT - All To Root.sql
0004 - CREATE - Help Desk Admin User.sql
0005 - CREATE - Help Desk User.sql

At a later date if the data model is changed, scripts are created that make the necessary modifications.  the advantage of this is you can develop and test in a test instance, get your scripts correct, then apply them to the production database without worry.  A good practice is to backup your development/test database and run your scripts in order until they are correct.  If you run into an error, restore the backup, correct the script, then run them again.

I've attached an example script.

AielloJ
0005---CREATE---Help-Desk-User1.sql
0
 
LVL 1

Author Comment

by:Shawn
ID: 36911572
AielloJ: I really like the idea of scripts but have only created them from existing tables in the past. Sometimes I"ll do this to create a similar table but will not save the script after the table is created.

are there any tools to help set up scripts or is it all done from scratch? any links would be useful.
0
 
LVL 1

Author Comment

by:Shawn
ID: 36911653
can't see any free version of Toad
0
 
LVL 1

Author Comment

by:Shawn
ID: 36911714
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 36914001

<isn't Erwin a fairly expensive complex tool?
yes
<regarding Visio and db diagrams are they better than the diagrams already in MSSMS?
yes
0
 
LVL 25

Expert Comment

by:jogos
ID: 36915689
The first link I gave points to a freeware dbscript for documentation. Toad is wider because also usable as another 'management studio'

You say you like the script idea. What about to put them in version control? How do you version control your code and documents describing the changes. Combine your db-version-scripts (both create from scratch and 'upgrade-script) in that same version control system and you won't ever forget when and why something is changed and what it's impact was.  
0
 
LVL 13

Expert Comment

by:AielloJ
ID: 36918660
Shawnaraxi:

I usually create the scripts in Notepad, then copy and paste the 'code' section into the tool I'm using with the database.  I've used the straight command windows interface as well as programs like TOAD with this cut and paste method.

Developing in a text editor, like Notepad, enables you to easily create, debug, and test, your scripts.  There's nothing more frustrating than having a minor error in a script and having to type it all over again.  If there's an error, you correct the text file, hit the 'Save' button, and copy and paste again.  When finished you have a text file with a debugged, and commented script.

All scripts are sequentially numbered.  For example, if your original project had 53 scripts, then your last file was named something like:

  0053 - INSERT - xxxxx.sql

If you develop another version of the project and need to make data model changes, then that script file starts with:

  0054 - xxxxx.  

'Jogo's comment about putting them into a version control system is also an excellent idea.  Some tools can create table creation scripts from existing data models.

AielloJ
0
 
LVL 25

Expert Comment

by:jogos
ID: 36920383
If you work in Visual Studio and have TFS as version control look at http://channel9.msdn.com/shows/toolshed/Toolshed-Tooltip-9-VSTS-DB-Pro-from-Episode-2/
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question