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.
Does anyone have a "best practices" suggestion or tip they use when documenting a database? any advice here would be appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
>> 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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
are there any tools to help set up scripts or is it all done from scratch? any links would be useful.
ASKER
can't see any free version of Toad
ASKER
found the freeware version here http://www.toadworld.com/Freeware/ToadforSQLServerFreeware/tabid/562/Default.aspx
<isn't Erwin a fairly expensive complex tool?
yes
<regarding Visio and db diagrams are they better than the diagrams already in MSSMS?
yes
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.
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.
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
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
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/
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
--