SQL 如何记录数据库

How to document a database

提问by Paul Tomblin

(Note: I realize this is close to How do you document your database structure?, but I don't think it's identical.)

（注意：我意识到这与How do you document your database structure?很接近，但我不认为它是相同的。）

I've started work at a place with a database with literally hundreds of tables and views, all with cryptic names with very few vowels, and no documentation. They also don't allow gratuitous changes to the database schema, nor can I touch any database except the test one on my own machine (which gets blown away and recreated regularly), so I can't add comments that would help anybody.

我已经开始在一个拥有数百个表和视图的数据库的地方工作，所有表和视图的名称都很神秘，元音很少，而且没有文档。他们也不允许对数据库架构进行无偿更改，除了我自己机器上的测试数据库（它被吹走并定期重新创建）之外，我也不能触及任何数据库，因此我无法添加对任何人都有帮助的评论。

I tried using "Toad" to create an ER diagram, but after leaving it running for 48 hours straight it still hadn't produced anything visible and I needed my computer back. I was talking to some other recent hires and we all suggested that whenever we've puzzled out what a particular table or what some of its columns means, we should update it in the developers wiki.

我尝试使用“Toad”来创建 ER 图，但在让它连续运行 48 小时后，它仍然没有产生任何可见的东西，我需要我的电脑回来。我正在与其他一些最近的员工交谈，我们都建议，每当我们对特定表格或其某些列的含义感到困惑时，我们应该在开发人员 wiki 中对其进行更新。

So what's a good way to do this? Just list tables/views and their columns and fill them in as we go? The basic tools I've got to hand are Toad, Oracle's "SQL Developer", MS Office, and Visio.

那么有什么好的方法可以做到这一点呢？只是列出表/视图及其列并在我们进行时填写它们？我手头的基本工具是 Toad、Oracle 的“SQL Developer”、MS Office 和 Visio。

回答by Ian Varley

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.

根据我的经验，ER（或 UML）图并不是最有用的工件 - 有大量表格，图（尤其是逆向工程图）通常是一个大而复杂的混乱，没有人从中学到任何东西。

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

• 表格含义及其功能使用方式的说明（在 UI 等中）
• 描述每个属性的含义（如果不明显）
• 从此表到其他表的关系（外键）的解释，反之亦然
• 附加约束和/或触发器的说明
• 对接触表格的主要视图和过程的附加解释，如果它们还没有被很好地记录下来

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 massivelyhelp 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 understandingand explainingthe 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).

正如其他人所提到的，有多种工具可以帮助您进行管理，例如Enterprise Architect、Red Gate SQL Doc以及来自不同供应商的内置工具。但是，虽然工具支持很有帮助（在更大的数据库中甚至是至关重要的），但努力理解和解释数据库的概念模型才是真正的胜利。从这个角度来看，您甚至可以在文本文件中进行操作（尽管以 Wiki 形式进行操作将允许多个人协作逐步添加到该文档中 - 因此，每次有人弄清楚某事时，他们都可以将其添加到不断增长的正文中文件立即）。

回答by Steven Huwig

One thing to consider is the COMMENT facility built into the DBMS. If you put comments on all of the tables and all of the columns in the DBMS itself, then your documentation will be inside the database system.

需要考虑的一件事是 DBMS 中内置的 COMMENT 工具。如果您对 DBMS 本身中的所有表和所有列进行评论，那么您的文档将位于数据库系统中。

Using the COMMENT facility does not make any changes to the schema itself, it only adds data to the USER_TAB_COMMENTS catalog table.

使用 COMMENT 工具不会对模式本身进行任何更改，它只会将数据添加到 USER_TAB_COMMENTS 目录表中。

回答by Kieveli

We use Enterprise Architectfor our DB definitions. We include stored procedures, triggers, and all table definitions defined in UML. The three brilliant features of the program are:

我们将Enterprise Architect用于我们的数据库定义。我们包括存储过程、触发器和 UML 中定义的所有表定义。该计划的三大亮点是：

1. Import UML Diagrams from an ODBC Connection.
2. Generate SQL Scripts (DDL) for the entire DB at once
3. Generate Custom Templated Documentation of your DB.

1. 从 ODBC 连接导入 UML 图。
2. 一次性为整个数据库生成 SQL 脚本 (DDL)
3. 生成数据库的自定义模板化文档。

You can edit your class / table definitions within the UML tool, and generate a fully descriptive with pictures included document. The autogenerated document can be in multiple formats including MSWord. We have just less than 100 tables in our schema, and it's quite managable.

您可以在 UML 工具中编辑您的类/表定义，并生成包含图片的完整描述文档。自动生成的文档可以是多种格式，包括 MSWord。我们的架构中只有不到 100 个表，而且非常易于管理。

I've never been more impressed with any other tool in my 10+ years as a developer. EA supports Oracle, MySQL, SQL Server (multiple versions), PostGreSQL, Interbase, DB2, and Access in one fell swoop. Any time I've had problems, their forums have answered my problems promptly. Highly recommended!!

在我 10 多年的开发人员生涯中，我从未对任何其他工具印象更深刻。EA一举支持Oracle、MySQL、SQL Server（多版本）、PostGreSQL、Interbase、DB2、Access。每当我遇到问题时，他们的论坛都会及时回答我的问题。强烈推荐！！

When DB changes come in, we make then in EA, generate the SQL, and check it into our version control (svn). We use Hudsonfor building, and it auto-builds the database from scripts when it sees you've modified the checked-in sql.

当数据库更改进来时，我们在 EA 中进行，生成 SQL，并将其签入我们的版本控制 (svn)。我们使用Hudson进行构建，当它看到您修改了签入的 sql 时，它会根据脚本自动构建数据库。

(Mostly stolen from another answer of mine)

（主要是从我的另一个答案中窃取的）

回答by Bad Pitt

In our team we came to useful approach to documenting legacy large Oracle and SQL Server databases. We use Dataedofor documenting database schema elements (data dictionary) and creating ERD diagrams. Dataedo comes with documentation repository so all your team can work on documenting and reading recent documentation online. And you don't need to interfere with database (Oracle comments or SQL Server MS_Description).

在我们的团队中，我们采用了有用的方法来记录遗留的大型 Oracle 和 SQL Server 数据库。我们使用Dataedo来记录数据库架构元素（数据字典）并创建 ERD 图。Dataedo 带有文档存储库，因此您的所有团队都可以在线记录和阅读最近的文档。并且您不需要干扰数据库（Oracle 注释或 SQL Server MS_Description）。

First you import schema (all tables, views, stored procedures and functions – with triggers, foreign keys etc.). Then you define logical domains/modules and group all objects (drag & drop) into them to be able to analyze and work on smaller chunks of database. For each module you create an ERD diagram and write top level description. Then, as you discover meaning of tables and views write a short description for each. Do the same for each column. Dataedo enables you to add meaningful title for each object and column – it's useful if object names are vague or invalid. Pro version enables you to describe foreign keys, unique keys/constraints and triggers – which is useful but not essential to understand a database.

首先导入模式（所有表、视图、存储过程和函数——带有触发器、外键等）。然后定义逻辑域/模块并将所有对象（拖放）分组到它们中，以便能够分析和处理较小的数据库块。您为每个模块创建一个 ERD 图并编写顶级描述。然后，当您发现表和视图的含义时，为每个写一个简短的描述。对每一列执行相同的操作。Dataedo 使您能够为每个对象和列添加有意义的标题——如果对象名称模糊或无效，它会很有用。专业版使您能够描述外键、唯一键/约束和触发器——这对理解数据库很有用但不是必需的。

You can access documentation through UI or you can export it to PDF or interactive HTML (the latter is available only in Pro version).

您可以通过 UI 访问文档，也可以将其导出为 PDF 或交互式 HTML（后者仅在 Pro 版本中可用）。

Described here is a continuous process rather than one time job. If your database changes (eg. new columns, views) you should sync your documentation on regular basis (couple clicks with Dataedo).

这里描述的是一个连续的过程而不是一次性的工作。如果您的数据库发生变化（例如新列、视图），您应该定期同步您的文档（使用 Dataedo 进行几次点击）。

See sample documentation: http://dataedo.com/download/Dataedo%20repository.pdf

请参阅示例文档：http: //dataedo.com/download/Dataedo%20repository.pdf

Some guidelines on documentation process:

关于文档过程的一些指南：

Diagrams:

图表：

• Keep your diagrams small and readable – just include important tables, relations and columns – only the one that have any meaning to understand big picture – primary/business keys, important attributes and relations,
• Use different color for key tables in a diagram,
• You can have more than one diagram per module,
• You can add diagram to description of most important tables/with most relations.

• 保持你的图表小而易读——只包括重要的表、关系和列——只有那些对理解大局有意义的——主/业务键、重要的属性和关系，
• 对图表中的关键表使用不同的颜色，
• 每个模块可以有多个图表，
• 您可以将图表添加到最重要的表/大多数关系的描述中。

Descriptions:

说明：

• Don't document the obvious – don't write description “Document date” for document.date column. If there's nothing meaningful to add just leave it blank,
• If objects stored in tables have types or statuses it's good to list them in general description of a table,
• Define format that is expected, eg. “mm/dd/yy” for a date that is stored in text field,
• List all known/important values an it's meaning, e.g. for status column could be something like this: “Document status: A – Active, C – Cancelled, D – Deleted”,
• If there's any API to a table – a view that should be used to read data and function/procedures to insert/update data – list it in the description of table,
• Describe where does rows/columns' values come from (procedure, form, interface etc.) ,
• Use “[deprecated]” mark (or similar) for columns that should not be used (title column is useful for this, explain which field should be used instead in description field).

• 不要记录明显的 - 不要为 document.date 列写描述“文档日期”。如果没有什么有意义的添加就留空，
• 如果存储在表中的对象具有类型或状态，最好在表的一般描述中列出它们，
• 定义预期的格式，例如。“mm/dd/yy”表示存储在文本字段中的日期，
• 列出所有已知/重要值及其含义，例如状态栏可能是这样的：“文档状态：A – 活动，C – 取消，D – 已删除”，
• 如果表有任何 API - 应该用于读取数据的视图和插入/更新数据的函数/过程 - 在表的描述中列出它，
• 描述行/列的值从何而来（程序、表单、界面等），
• 对不应该使用的列使用“[deprecated]”标记（或类似的）（标题列对此很有用，解释应该在描述字段中使用哪个字段）。

回答by Bob

Here is a good post on how to approach the database documentation: http://www.simple-talk.com/sql/database-administration/database-documentation---lands-of-trolls-why-and-how/

这是关于如何处理数据库文档的好帖子：http: //www.simple-talk.com/sql/database-administration/database-documentation---lands-of-trolls-why-and-how/

回答by Bill Karwin

A wiki solution supports hyperlinks and collaborative editing, but a wiki is only as good as the people who keep it organized and up to date. You need someone to take ownership of the document project, regardless of what tool you use. That person may involve other knowledgeable people to fill in the details, but one person should be responsible for organizing the information.

wiki 解决方案支持超链接和协作编辑，但 wiki 的好坏取决于保持组织和更新的人。无论您使用什么工具，您都需要有人拥有文档项目的所有权。该人可能需要其他有知识的人来填写详细信息，但应由一人负责组织信息。

If you can't use a tool to generate an ERD by reverse engineering, you'll have to design one by hand using TOAD or VISIO.

如果您无法使用工具通过逆向工程生成 ERD，则必须使用 TOAD 或 VISIO 手动设计一个。

Any ERD with hundreds of objects is probably useless as a guide for developers, because it'll be unreadable with so many boxes and lines. In a database with so many objects, it's likely that there are "sub-systems" of a few dozen tables and views each. So you should make custom diagrams of these sub-systems, instead of expecting a tool to do it for you.

任何包含数百个对象的 ERD 作为开发人员的指南都可能毫无用处，因为如果有这么多的框和行，它就会变得不可读。在具有如此多对象的数据库中，很可能存在每个包含几十个表和视图的“子系统”。因此，您应该制作这些子系统的自定义图表，而不是期望有工具为您完成。

You can also design a pseudo-ERD, where groups of tables are represented by a single object in one diagram, and that group is expanded in another diagram.

您还可以设计一个伪 ERD，其中表组由一个图中的单个对象表示，而该组在另一个图中展开。

A single ERD or set of ERD's are not sufficient to document a system of this complexity, any more than a class diagram would be adequate to document an OO system. You'll have to write a document, using the ERD's as illustrations. You need text descriptions of the meaning and use of each table, each column, and the relationships between tables (especially where such relationships are implicit instead of represented by referential integrity constraints).

单个 ERD 或一组 ERD 不足以记录这种复杂性的系统，就像类图足以记录 OO 系统一样。您必须编写一份文档，使用 ERD 作为插图。您需要每个表、每个列的含义和用途以及表之间的关系的文本描述（尤其是在这种关系是隐式的而不是由参照完整性约束表示的情况下）。

All of this is a lot of work, but it will be worth it. If there's a clear and up-to-date place where the schema is documented, the whole team will benefit from it.

所有这一切都需要大量工作，但这将是值得的。如果有一个清晰且最新的地方来记录模式，整个团队都会从中受益。

回答by dkretz

This answer extends Kieveli's above, which I upvoted. If your version of EA supports Object Role Modeling (conceptual design, vs. logical design = ERD), reverse engineer to that and then fill out the model with the expressive richness it gives you.

这个答案扩展了上面 Kieveli 的答案，我赞成。如果您的 EA 版本支持对象角色建模（概念设计，与逻辑设计 = ERD），请对其进行逆向工程，然后用它为您提供的丰富表现力填充模型。

The cheap and lighter-weight option is to download Visiomodeler for free from MS, and do the same with that.

便宜且重量更轻的选择是从 MS 免费下载 Visiomodeler，然后执行相同的操作。

The ORM (call it ORMDB) is the only tool I've ever found that supports and encourages database design conversations with non-IS stakeholders about BL objects and relationships.

ORM（称为 ORMDB）是我所发现的唯一支持和鼓励与非 IS 利益相关者就 BL 对象和关系进行数据库设计对话的工具。

Reality check - on the way to generating your DDL, it passes through a full-stop ERD phase where you can satisfy your questions about whether it does anything screwy. It doesn't. It will probably show you weaknesses in the ERD you designed yourself.

现实检查——在生成你的 DDL 的过程中，它经历了一个完整的 ERD 阶段，在那里你可以满足你关于它是否有任何奇怪的问题的问题。它没有。它可能会向您展示您自己设计的 ERD 中的弱点。

ORMDB is a classic case of the principle that the more conceptual the tool, the smaller the market. Girls just want to have fun, and programmers just want to code.

ORMDB 是该原则的经典案例，即工具越概念化，市场越小。女孩只想玩得开心，而程序员只想编码。

回答by dkretz

Since you have the luxury of working with fellow developers that are in the same boat, I would suggest asking them what they feel would convey the needed information, most easily. My company has over 100 tables, and my boss gave me an ERD for a specific set tables that all connect. So also, you might want to try breaking 1 massive ERD into a bunch of smaller, manageable, ERDs.

既然你有幸与同舟共济的开发人员一起工作，我建议问问他们，他们认为最容易传达所需信息的方式是什么。我的公司有 100 多张桌子，我的老板给了我一张所有连接的特定桌子的 ERD。因此，您可能还想尝试将 1 个庞大的 ERD 分解为一堆较小的、可管理的 ERD。

回答by uncaught_exception

If describing your databases to your end users is your primary goal Ooluk Data Dictionary Managercan prove useful. It is a web-based multi-user software that allows you to attach descriptions to tables and columns and allows full text searches on those descriptions. It also allows you to logically group tables using labels and browse tables using those labels. Tables as well as columns can be tagged to find similar data items across your database/databases.

如果向最终用户描述您的数据库是您的主要目标，那么 Ooluk 数据字典管理器可以证明是有用的。它是一种基于 Web 的多用户软件，允许您将描述附加到表和列，并允许对这些描述进行全文搜索。它还允许您使用标签对表进行逻辑分组，并使用这些标签浏览表。可以标记表和列以在您的数据库/数据库中查找相似的数据项。

The software allows you to import metadata information such as table name, column name, column data type, foreign keys into its internal repository using an API. Support for JDBC data sources comes built-in and can be extended further as the API source is distributed under ASL 2.0. It is coded to read the COMMENTS/REMARKS from many RDBMSs.You can always manually override the imported information. The information you can store about tables and columns can be extended using custom fields.

该软件允许您使用 API 将元数据信息（例如表名、列名、列数据类型、外键）导入其内部存储库。对 JDBC 数据源的支持是内置的，并且可以进一步扩展，因为 API 源是在 ASL 2.0 下分发的。它被编码为从许多 RDBMS 中读取 COMMENTS/REMARKS。您始终可以手动覆盖导入的信息。您可以存储的有关表和列的信息可以使用自定义字段进行扩展。

The Data Dictionary Manager uses the "data object" and "attribute" terminology instead of table and column because it isn't designed specifically for relational databases.

数据字典管理器使用“数据对象”和“属性”术语而不是表和列，因为它不是专门为关系数据库设计的。

Notes

笔记

• If describing technical aspects of your database such as triggers, indexes, statistics is important this software isn't the best option. It is however possible to combine a technical solution with this software using hyperlink custom fields.
• The software doesn't produce an ERD

• 如果描述数据库的技术方面（例如触发器、索引、统计数据）很重要，则该软件不是最佳选择。但是，可以使用超链接自定义字段将技术解决方案与该软件相结合。
• 该软件不产生 ERD

Disclosure: I work at the company that develops this product.

披露：我在开发该产品的公司工作。

回答by James Piggot

Well, a picture tells a thousand words so I would recommend creating ER diagrams where you can view the relationship between tables at a glance, something that is hard to do with a text-only description.

好吧，一张图片讲述了一千个单词，所以我建议创建 ER 图表，您可以在其中一目了然地查看表格之间的关系，这是纯文本描述难以做到的。

You don't have to do the whole database in one diagram, break it up into sections. We use Visual Paradigm at work but EA is a good alternative as is ERWIN, and no doubt there are lots of others that are just as good.

您不必在一张图中完成整个数据库，而是将其分解为多个部分。我们在工作中使用 Visual Paradigm，但 EA 和 ERWIN 一样是一个不错的选择，毫无疑问，还有很多其他的也一样好。

If you have the patience, then using html to document the tables and columns makes your documentation easier to access.

如果您有耐心，那么使用 html 来记录表格和列会使您的文档更容易访问。