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.
LVL 1
ShawnAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
..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
--
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?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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.
ShawnAuthor Commented:
can't see any free version of Toad
ShawnAuthor Commented:
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:

<isn't Erwin a fairly expensive complex tool?
yes
<regarding Visio and db diagrams are they better than the diagrams already in MSSMS?
yes
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.  
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
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/
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.