How to Document a Database

How to document a database

In my experience, ER (or UML) diagrams aren't the most useful artifact - with a large number of tables, diagrams (especially reverse engineered ones) are often a big convoluted mess that nobody learns anything from.

For my money, some good human-readable documentation (perhaps supplemented with diagrams of smaller portions of the system) will give you the most mileage. This will include, for each table:

• Descriptions of what the table means and how it's functionally used (in the UI, etc.)
• Descriptions of what each attribute means, if it isn't obvious
• Explanations of the relationships (foreign keys) from this table to others, and vice-versa
• Explanations of additional constraints and / or triggers
• Additional explanation of major views & procs that touch the table, if they're not well documented already

With all of the above, don't document for the sake of documenting - documentation that restates the obvious just gets in people's way. Instead, focus on the stuff that confused you at first, and spend a few minutes writing really clear, concise explanations. That'll help you think it through, and it'll massively help other developers who run into these tables for the first time.

As others have mentioned, there are a wide variety of tools to help you manage this, like Enterprise Architect, Red Gate SQL Doc, and the built-in tools from various vendors. But while tool support is helpful (and even critical, in bigger databases), doing the hard work of understanding and explaining the conceptual model of the database is the real win. From that perspective, you can even do it in a text file (though doing it in Wiki form would allow several people to collaborate on adding to that documentation incrementally - so, every time someone figures out something, they can add it to the growing body of documentation instantly).

How do you document your database structure?

MySQL allows comments on tables and rows. PostgreSQL does as well. From other answers, Oracle and MSSQL have comments too.

For me, a combination of UML diagram for a quick refresher on field names, types, and constraints, and an external document (TeX, but could be any format) with extended description of everything database-related - special values, field comments, access notes, whatever - works best.

Database design for a document sharing system

Imagine the case when instead of a record per permission, having as many records as many permissions, you would have values like:

id1,id2,...,idn

Now, let's see a few problems:

Number of privileges

With the change you intend to accomplish, you would have to count the number of commas (and add 1) for each permissions, which would cause you a LOT of headache. Currently you can just count the number of records and you can group the results as you want.

Adding a new permission to a document

Suppose you have to add an id to the permissions list of a document. In thi case you will have to search for the permission record of the document. If not found, then you will need to insert a record and ensure that your id is put there. If found, then you will have to update the same record. Currently it's just an insert if the permission does not exist, often you already have the information and do not even need to query.

Removing a permission from a document

To achieve this with the suggested schema you will need to find the permission record of the document and check whether it contains at least a comma. If so, then you will need to split the values and construct a character sequence which does not contain the id you want to remove and update the record and to delete the record if there was no comma. Currently you just have to remove a record.

Finding the documents a user has permission to

With your suggested schema you will need to do some pretty slow string operations, like

like '%,<theid>,%'

and ensure that all the strings start and end with comma for this query, but without actually modifying the data, so you will need to concatenate the actual value with a comma prefix and a comma suffix, making your code underperformant and highly difficult to read and maintain. Currently you can easily query for such values.

1NF

In short, your suggestion would worsen your design. You will need to ask yourself whether you really have a problem with your current schema and if so, what it is. If you have serious problems with performance, you will need to make sure you correctly identify the problem with performance and if this is the problem of performancee, then improve the schema, possibly by adding indexes to some columns. Your suggestion would deviate from 1NF and it is a bad idea.

How to document database efficiently (tables, attributes with definition)?

We use extended properties to "tag" constraints, tables, indexes, viws, procs, the lot. (we also use SQL doc)

It's mentioned on the interweb quite a lot too

What is your preferred document format for documenting databases

It sounds like you have already decided on a document format, which is HTML based on MediaWiki markup.

Also you should generate Entity-Relationship Diagrams which are useful additions to database documentation (though ERD's don't tell the whole story either).

Do you mean document organization, i.e. what headings and content should be included in each page?

Here are some suggestions:

1. Table Structure

   • Column names, data types, constraints
   • Meaning and usage of each column
   • Extra logical constraints in triggers and application code
   • Indexes defined

2. Relationships to other tables

   • Tables dependent on this one
   • Tables this one depends on
   • Notes on special or implicit relationships, that have no enforcement through database constraints

3. Usage of table

   • Usage in stored procedures
   • Usage in application code
   • Usage in views
   • Who has read and/or write access; SQL privileges of each user or role

There are other questions at StackOverflow that are very close to this one.

• "How to document a database" is a very similar question to yours, since it's specifically about wiki documentation solutions.
• "What are the best ways to understand an unfamiliar database" may give you some good tips, as you are creating documentation that would help someone in that situation.
• "How do you document your database structure?" is related but not as closely, because it's about putting documentation into the metadata itself.

What is a good way to document what your tables and columns mean in a SQL Server database?

You can add extended properties

Viewing Extended Properties

e.g. displays all extended properties in the AdventureWorks2008R2 sample database

USE AdventureWorks2008R2;
GO
SELECT class, class_desc, major_id, minor_id, name, value
FROM sys.extended_properties;
GO

Fun with Extended Properties in SQL Server 2008

How to document a database project in visualstudio

A couple of ideas:

1. There is a project on codeproject.com called SQL XML Documentation that extracts XML comments from SQL scripts using Microsoft SandCastle. I've never used it though so I cannot vouch for it.
2. If you don't necessarily need the documentation in XML, you can take advantage of the Description property in Visual Studio's Property pane. The description will be stored as an extended property in SQL Server.
   

---

Related Topics