SQL 你如何记录你的数据库结构？

How do you document your database structure?

提问by Anders Sandvig

Many database systems don't allow comments or descriptions of tables and fields, so how do you go about documenting the purpose of a table/field apart from the obvious of having good naming conventions?

许多数据库系统不允许对表和字段进行注释或描述，那么除了显然具有良好的命名约定之外，您如何记录表/字段的用途？

(Let's assume for now that "excellent" table and field names are not enough to document the full meaning of every table, field and relationship in the database.)

（让我们暂时假设“优秀”的表和字段名称不足以记录数据库中每个表、字段和关系的完整含义。）

I know many people use UML diagrams to visualize the database, but I have rarely—if ever—seen a UML diagram including field comments. However, I have good experience with using comments inside .sqlfiles. The downside to this approach is that it requires the .sqlfiles to be manually kept up-to-date as the database structure changes over time—but if you do, you can also have it under version control.

我知道很多人使用 UML 图来可视化数据库，但我很少（如果有的话）看到包含字段注释的 UML 图。但是，我在.sql文件中使用注释有很好的经验。这种方法的缺点是，.sql随着数据库结构随着时间的推移而变化，它需要手动使文件保持最新——但如果这样做，您也可以将其置于版本控制之下。

Some other techniques I have seen are separate document describing database structure and relationships and manually maintained comments inside ORM code or other database-mapping code.

我见过的其他一些技术是描述数据库结构和关系的单独文档，以及在 ORM 代码或其他数据库映射代码中手动维护的注释。

How have you solved this problem in the past? What methods exists and what are the various pros and cons associated with them? How you would you like this solved in "a perfect world"?

您过去是如何解决这个问题的？存在哪些方法以及与它们相关的各种利弊是什么？您希望如何在“完美世界”中解决这个问题？

Update

更新

As others have pointed out, most of the popular SQL engines do in fact allow comments, which is great. Oddly enough, people don't seem to be using these features much. At least not on the projects I have been involved with in the past.

正如其他人指出的那样，大多数流行的 SQL 引擎实际上都允许注释，这很好。奇怪的是，人们似乎并没有经常使用这些功能。至少在我过去参与的项目中不会。

采纳答案by MaxVT

MySQL allowscomments on tables and rows. PostgreSQL doesas well. From other answers, Oracle and MSSQL have comments too.

MySQL允许对表和行进行注释。PostgreSQL也是如此。从其他答案来看，Oracle 和 MSSQL 也有评论。

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.

对我来说，一个 UML 图的组合，用于快速复习字段名称、类型和约束，以及一个外部文档（TeX，但可以是任何格式）与数据库相关的所有内容的扩展描述 - 特殊值、字段注释、访问笔记，无论如何 - 效果最好。

回答by Joel Cuff

Late one but hopefully useful… Here is a process we used when developing relatively large database (around 100 tables and around 350 objects in total)

晚了，但希望有用……这是我们在开发相对较大的数据库时使用的一个过程（总共大约 100 个表和大约 350 个对象）

• Developers were required to use extended properties to add details to all objects.
• Admins rejected any DDL that didn't have extended properties
• Third party tool was used to automatically generate visual documentation via command line interface every day. We used ApexSQL Docand it worked just fine but I also successfully used SQL Doc from Red Gate in other company.

• 开发人员需要使用扩展属性为所有对象添加详细信息。
• 管理员拒绝了任何没有扩展属性的 DDL
• 使用第三方工具每天通过命令行界面自动生成可视化文档。我们使用了ApexSQL Doc，它工作得很好，但我也成功地使用了其他公司 Red Gate 的 SQL Doc。

This process ensured that we have all objects documented and document up to date.

此过程确保我们将所有对象记录在案并保持最新。

The difficult thing though was getting developers to write good comments consistently ;)

困难的是让开发人员始终如一地写出好的评论；)

回答by evilhomer

SQL Server has extended properties that can take care of this.

SQL Server 具有可以处理此问题的扩展属性。

This article describes how do set them up in SQL Sever http://www.developer.com/db/article.php/3677766

本文介绍如何在 SQL Sever 中设置它们 http://www.developer.com/db/article.php/3677766

MSDN Reference

MSDN 参考

It can be used in conjunction with RedGate SQL Docto create a nice Data dictionary.

它可以与RedGate SQL Doc结合使用来创建一个不错的数据字典。

回答by bernardn

I use comments attached to tables and column. SchemaSpyis a great tool for generating html documentation files out of your schema, including comments.

我使用附加到表格和列的注释。SchemaSpy是一个很好的工具，用于从您的架构中生成 html 文档文件，包括注释。

回答by ConcernedOfTunbridgeWells

At one point I wrote a basic SQL parser that would parse CREATE TABLE statements and strip out specially formatted comments. These were then post-processed into LaTeX source and rendered to PDF. This was inspired by Javadocand was used to create the documentation for This product. Subsequently a data dictionary feature was built into the warehouse manager and a modified version of the LaTeX generator was used to render the data dictionary from the warehouse manager.

有一次，我编写了一个基本的 SQL 解析器，它可以解析 CREATE TABLE 语句并去除特殊格式的注释。然后将它们后处理为 LaTeX 源代码并渲染为 PDF。这受到Javadoc 的启发，用于创建本产品的文档。随后，在仓库管理器中内置了一个数据字典功能，并使用 LaTeX 生成器的修改版本来呈现来自仓库管理器的数据字典。

On another project I used Visio - the version that comes with Visual Studio Enterprise Architect will forward engineer a database. The SQL so generated had the table and column comments rendered in comment strings that were fairly straightforward to parse. The tool I wrote generated MIF files that were be included into a spec document built with FrameMaker.

在另一个项目中，我使用了 Visio - Visual Studio Enterprise Architect 附带的版本将正向工程数据库。如此生成的 SQL 将表和列注释呈现在注释字符串中，解析起来相当简单。我编写的工具生成了 MIF 文件，这些文件包含在使用 FrameMaker 构建的规范文档中。

If you have a repository tool such as Powerdesigneryou can maintain data models in it and get repository reports out that include the documentation you have entered. If you need deeper integration of your data dictionary with functional specifications (Quite useful for data warehouse systems where the ETL is complex and involves significant computation of derived values) you can still extract the metadata and write a utility to generate something that will integrate the data dictionary into a specification document. This also allows cross-referencing between data dictionary items and other specification documents and generation of indexes that cover the data dictionary definitions and related documentation such as a specification of how something is calculated with examples.

如果您有诸如Powerdesigner 之类的存储库工具，您可以在其中维护数据模型并获取包含您输入的文档的存储库报告。如果您需要将数据字典与功能规范进行更深入的集成（对于 ETL 复杂且涉及派生值的大量计算的数据仓库系统非常有用），您仍然可以提取元数据并编写一个实用程序来生成将集成数据的内容字典转换为规范文档。这还允许在数据字典项和其他规范文档之间进行交叉引用，并生成涵盖数据字典定义和相关文档的索引，例如如何使用示例计算某些内容的规范。

回答by Katy

We've written a word document that lists the tables, the fields and what everything does. This is backed up by a diagram that shows how everything links/relates to each other. It's a pretty simple document really, just a load of tables with Field Name > Data Type > Purpose

我们编写了一个 Word 文档，其中列出了表格、字段和所有功能。这是由一张图表支持的，该图表显示了所有内容如何相互链接/关联。这真的是一个非常简单的文档，只是一堆带有字段名称 > 数据类型 > 目的的表

回答by Milan Babu?kov

I'm using Firebirdwhich has description field for all system objects (tables, columns, views, procedures and parameters, triggers, etc.) It's nice because you can easily share it with others (docs go with database, not separately) and you never lose it.

我正在使用Firebird，它具有所有系统对象（表、列、视图、过程和参数、触发器等）的描述字段。这很好，因为您可以轻松地与他人共享它（文档与数据库一起使用，而不是单独使用）和您永远不会失去它。

Most admin. tools for Firebird allow you to edit these descriptions and there are some specialized tools (like IBDesc, for example) that create nice HTML or PDF reports you can print (for some or all tables) easily.

大多数管理员。Firebird 工具允许您编辑这些描述，并且有一些专门的工具（例如 IBDesc）可以创建漂亮的 HTML 或 PDF 报告，您可以轻松打印（对于某些或所有表格）。

回答by bortzmeyer

I comment my databases as I comment my programs. By writing good (I hope) comments in the source code (the SQL file containing the DDL instructions).

我在评论我的程序时评论我的数据库。通过在源代码（包含 DDL 指令的 SQL 文件）中编写好的（我希望）注释。

Using SQL COMMENT is another possibility. The good thing with them is that they are always with your objects, are backed up with them, etc. The bad thing is that they are more limited (for instance in length).

使用 SQL COMMENT 是另一种可能性。它们的好处是它们总是和你的对象在一起，被它们备份等等。坏的是它们更有限（例如长度）。

回答by warren

It's a really simplistic approach, but I use a pair of wiki pages: one with the mysqldump of the database, and one written in a slightly more English-like format.

这是一种非常简单的方法，但我使用了一对 wiki 页面：一个带有数据库的 mysqldump，另一个以稍微更像英语的格式编写。

For the projects I've worked on, that's been sufficient (through the dozens of tables level). I don't know how well it might scale to larger projects (say in the hundreds of tables), but it's been good so far.

对于我从事过的项目，这已经足够了（通过数十个表级别）。我不知道它可以扩展到更大的项目（比如数百个表），但到目前为止它一直很好。

回答by hhh

I've recently turned to writing markdown documentation, which includes linking to individual table .sqlfiles (where tables and fields are hopefully intuitively named with plenty of comments).

我最近转向编写降价文档，其中包括链接到单个表.sql文件（其中表和字段希望直观地命名并带有大量注释）。

I keep the individual table schema's in version control, using the following command:

我使用以下命令将单个表模式保持在版本控制中：

mysqldump --no-data --tab=./tables dbname

mysqldump --no-data --tab=./tables dbname

The schema for a single table allows you to see comments, indexes, unique keys etc. so is fairly self explanatory (well that is the idea at least).

单个表的架构允许您查看注释、索引、唯一键等，因此是不言自明的（至少是这个想法）。

The master markdown documentation has hyperlinks such as user table peppered throughout, so the reader can easily go to the different tables.

主降价文档有超链接，如贯穿整个的用户表，因此读者可以轻松转到不同的表。