Funny, I was wondering how other people are doing this as well..

While developing my first big database project, I found that Microsoft SQL Server Management Studio 10.0.1600.22 supports database diagrams which you can export to a word document or other documentation software where you can add as much documentation detail as you want. Just expand the database you connected to on SQL Management Studio and right click on "database diagrams" in the object explorer and select "New Database Diagram" to generate an interactive diagram which will show all the relationships between the different tables. You can even specify which tables you want to include in the diagrams, so that the image does not get unweildly if you are just trying to document it piece by piece. Export the image to any other editing software and comment as much as you want.

I also recommend plenty of /comments/ in the script which generates your database.

Generally it is a lot of work writing down what it is all for, but a good idea for the long term, such as when you or some other poor soul comes back to update your creation a couple of years later! :)