• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

documenting tables and relationships

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
Shawn
Asked:
Shawn
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
jogosCommented:
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
 
Eugene ZCommented:
..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
 
ShawnAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
AielloJCommented:
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
 
ShawnAuthor Commented:
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
 
ShawnAuthor Commented:
can't see any free version of Toad
0
 
ShawnAuthor Commented:
0
 
Eugene ZCommented:

<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
 
jogosCommented:
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
 
AielloJCommented:
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
 
jogosCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now