数据库设计问题 - 孤立的，无关的表 [英] Database design question - Isolated, unrelated tables
问题描述
我对数据库设计的最佳实践有疑问。在一个
关系数据库中，有时候通过外键创建与其他表无关的表
是明智/必要的
关系或者这总是表明某种潜在的
设计缺陷。需要重新评估问题的东西
域名？
我问的原因是因为在我们的应用程序中，用户可以执行x
高级操作数量（创建/更新项目，创建/
回答调查等等）。不同的用户可以执行不同的操作，每个操作可以操作一个或多个表。这个系统的
部分已经完成并正常运行。现在要求
在数据库中有一些审计日志记录（与应用程序生成的
基于文本的日志文件分开）。这个
审计日志记录 table将包含应用程序内部发生的高级事件（可能与某个特定的
操作有关，也可能与之无关）。此表在某种意义上与数据库中的每个其他表
相关，以及数据库本身不存在的数据
（异常，外部事件等） 。例如：它可能具有
条目，指定在用户创建项目y时，在时间A
用户填写调查B，在时间C LDAP服务器已关闭，在时间D a
未经授权的登录尝试发生等。
正如我所说，这些似乎暗示了一个独立的，浮动的表与
几个字段，用于存储有关系统上的内容的条目
，与数据库中的其他表没有任何直接关系。但是我对于创建这样一个孤立的表格感到不安。另一种选择
用于存储日志记录。另一个模式/数据库中的信息，但
使维护工作量加倍。不是真的很期待
维护/设计两种不同的模式。
我看了一下microsoft adventureworks数据库架构图
他们似乎有3个标准表 - AWBuildVersion，ErrorLog
和DatabaseLog（除非我读错了！）
任何建议，信息或资源非常感谢。
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
The reason I ask is because in our application, the user can perform x
number of high level operations (creating/updating projects, creating/
answering surveys etc. etc.). Different users can perform different
operations and each operation can manipulate one or more table. This
part of the system is done and working. Now there is a requirement to
have some sort of audit logging inside the database (separate from the
text based log file that the application generates anyway). This
"audit logging" table will contain high level events that occur inside
the application (which may or may not relate to a particular
operation). This table is in some sense related to every other table
in the database, as well as data that is not in the database itself
(exceptions, external events etc.). For example : it might have
entries that specify that at time x user created project y, at time A
user filled out survey B, at time C LDAP server was down, At time D an
unauthorized login attempt occurred etc.
As I said, these seems to suggest a stand alone, floating table with a
few fields that store entries regarding whats going on the system
without any direct relationship to other tables in the database. But I
just feel uneasy about creating such an isolated table. Another option
is to store the "logging" information in another schema/database, but
that doubles the maintainance work load. Not really looking forward to
maintaining/designing two different schemas.
I had a look at the microsoft adventureworks database schema diagram
and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
and DatabaseLog (unless i am reading it wrong!)
Any advice, Information or resources are much appreciated.
推荐答案
On 25 Jun，08：08，nyathan ... @ hotmail.com写道：
On 25 Jun, 08:08, nyathan...@hotmail.com wrote:
我对数据库设计的最佳实践有疑问。在一个
关系数据库中，有时候通过外键创建与其他表无关的表
是明智/必要的
关系或者这总是表明某种潜在的
设计缺陷。需要重新评估问题的东西
域名？
我问的原因是因为在我们的应用程序中，用户可以执行x
高级操作数量（创建/更新项目，创建/
回答调查等等）。不同的用户可以执行不同的操作，每个操作可以操作一个或多个表。这个系统的
部分已经完成并正常运行。现在要求
在数据库中有一些审计日志记录（与应用程序生成的
基于文本的日志文件分开）。这个
审计日志记录 table将包含应用程序内部发生的高级事件（可能与某个特定的
操作有关，也可能与之无关）。此表在某种意义上与数据库中的每个其他表
相关，以及数据库本身不存在的数据
（异常，外部事件等） 。例如：它可能具有
条目，指定在用户创建项目y时，在时间A
用户填写调查B，在时间C LDAP服务器已关闭，在时间D a
未经授权的登录尝试发生等。
正如我所说，这些似乎暗示了一个独立的，浮动的表与
几个字段，用于存储有关系统上的内容的条目
，与数据库中的其他表没有任何直接关系。但是我对于创建这样一个孤立的表格感到不安。另一种选择
用于存储日志记录。另一个模式/数据库中的信息，但
使维护工作量加倍。不是真的很期待
维护/设计两种不同的模式。
我看了一下microsoft adventureworks数据库架构图
他们似乎有3个标准表 - AWBuildVersion，ErrorLog
和DatabaseLog（除非我读错了！）
任何建议，信息或资源非常感谢。
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
The reason I ask is because in our application, the user can perform x
number of high level operations (creating/updating projects, creating/
answering surveys etc. etc.). Different users can perform different
operations and each operation can manipulate one or more table. This
part of the system is done and working. Now there is a requirement to
have some sort of audit logging inside the database (separate from the
text based log file that the application generates anyway). This
"audit logging" table will contain high level events that occur inside
the application (which may or may not relate to a particular
operation). This table is in some sense related to every other table
in the database, as well as data that is not in the database itself
(exceptions, external events etc.). For example : it might have
entries that specify that at time x user created project y, at time A
user filled out survey B, at time C LDAP server was down, At time D an
unauthorized login attempt occurred etc.
As I said, these seems to suggest a stand alone, floating table with a
few fields that store entries regarding whats going on the system
without any direct relationship to other tables in the database. But I
just feel uneasy about creating such an isolated table. Another option
is to store the "logging" information in another schema/database, but
that doubles the maintainance work load. Not really looking forward to
maintaining/designing two different schemas.
I had a look at the microsoft adventureworks database schema diagram
and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
and DatabaseLog (unless i am reading it wrong!)
Any advice, Information or resources are much appreciated.
嗨Nyathan，
一般来说，拥有独立的桌子是完全可以接受的
没有实现FK关系。
确实，在整个数据库中消失的时间都是以这种方式创建的。
作为OLTP的开销，所有索引数据操作都在后面/>
这些场景可能会让系统瘫痪。 （PK& FK支持
''隐藏''索引）。
关于你的具体任务，（这里你会意识到）我不是来自
SQL Server背景），检查rdbms中是否存在现有的审计
能力。
一些rdbms允许跟踪某个表上的所有活动或某个用户的所有
活动，一些允许两者，一些从一个
变为另一个，（大鼠） ）。
如果您需要创建自己的系统，那么请考虑通用。
带有日期时间戳，用户名，表格影响，动作的单个表格>
，以及100列varchar 100.
您要审核的每个表都需要触发器，（插入，
更新，删除） ，这会调用一个genric存储过程来填充您创建的表格。
如果您要为很多表格执行此操作或反复执行此操作br $> b $ b建议写一个小小的le noddy程序从systables获取源表列
信息然后生成触发器。
触发器将需要导致存储过程写出
数据的前后图像。
小心批量更新或删除，（最好禁用触发器
在你之前做它们。
同样归档/清除你的通用表变得有趣，
取决于活动水平。
我会不推荐以上忙碌的桌子。
希望有所帮助，Tim
Hi Nyathan,
In general terms it is quite acceptable to have a standalone table
with no FK relationships instansiated.
Indeed, in times gone by whole databases were created in this manner
as the overhead for OLTP with all the index data manipulation behind
the scences could bring a system to its knees. (PK & FK are backed by
''hidden'' indexes).
As regards your specific task, (and here you''ll realise I''m not from a
SQL Server background), check to see if there is an existing audit
ability in the rdbms.
Some rdbms allow for tracking all the activity on a table or all the
activity of a certain user, some allow both and some changed from one
to the other, (rats).
If you need to create your own system then think generic.
A single table with datetime stamp, username, table effected, action
taken, and the say 100 columns of varchar 100.
Each table you are seeking to audit will need triggers, ( insert,
update, delete), that calls a genric stored procedure that populates
the table you have created.
If your going to be doing this for a lot of tables or repeatedly I''d
advise writting a little noddy program to get the source table column
information from the systables and then generate the triggers.
The triggers will need to cause the stored procedure to write away
both the before and after image of the data.
BEWARE of bulk updates or deletes, ( best to disable the triggers
before you do them).
Also archiving / purging of your generic table becomes interesting,
depending on activity levels.
I would not recommend the above for busy tables.
Hope that helps, Tim
< ny ******** @ hotmail.comwrote in message
news：11 ******************** **@g37g2000prf.googlegr oups.com ...
<ny********@hotmail.comwrote in message
news:11**********************@g37g2000prf.googlegr oups.com...
我对最佳做法有疑问数据库设计。在一个
关系数据库中，有时候通过外键创建与其他表无关的表
是明智/必要的
关系或者这总是表明某种潜在的
设计缺陷。需要重新评估问题的东西
域名？
我问的原因是因为在我们的应用程序中，用户可以执行x
高级操作数量（创建/更新项目，创建/
回答调查等等）。不同的用户可以执行不同的操作，每个操作可以操作一个或多个表。这个系统的
部分已经完成并正常运行。现在要求
在数据库中有一些审计日志记录（与应用程序生成的
基于文本的日志文件分开）。这个
审计日志记录 table将包含应用程序内部发生的高级事件（可能与某个特定的
操作有关，也可能与之无关）。此表在某种意义上与数据库中的每个其他表
相关，以及数据库本身不存在的数据
（异常，外部事件等） 。例如：它可能具有
条目，指定在用户创建项目y时，在时间A
用户填写调查B，在时间C LDAP服务器已关闭，在时间D a
未经授权的登录尝试发生等。
正如我所说，这些似乎暗示了一个独立的，浮动的表与
几个字段，用于存储有关系统上的内容的条目
，与数据库中的其他表没有任何直接关系。但是我对于创建这样一个孤立的表格感到不安。另一种选择
用于存储日志记录。另一个模式/数据库中的信息，但
使维护工作量加倍。不是真的很期待
维护/设计两种不同的模式。
我看了一下microsoft adventureworks数据库架构图
他们似乎有3个标准表 - AWBuildVersion，ErrorLog
和DatabaseLog（除非我读错了！）
任何建议，信息或资源非常感谢。
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
The reason I ask is because in our application, the user can perform x
number of high level operations (creating/updating projects, creating/
answering surveys etc. etc.). Different users can perform different
operations and each operation can manipulate one or more table. This
part of the system is done and working. Now there is a requirement to
have some sort of audit logging inside the database (separate from the
text based log file that the application generates anyway). This
"audit logging" table will contain high level events that occur inside
the application (which may or may not relate to a particular
operation). This table is in some sense related to every other table
in the database, as well as data that is not in the database itself
(exceptions, external events etc.). For example : it might have
entries that specify that at time x user created project y, at time A
user filled out survey B, at time C LDAP server was down, At time D an
unauthorized login attempt occurred etc.
As I said, these seems to suggest a stand alone, floating table with a
few fields that store entries regarding whats going on the system
without any direct relationship to other tables in the database. But I
just feel uneasy about creating such an isolated table. Another option
is to store the "logging" information in another schema/database, but
that doubles the maintainance work load. Not really looking forward to
maintaining/designing two different schemas.
I had a look at the microsoft adventureworks database schema diagram
and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
and DatabaseLog (unless i am reading it wrong!)
Any advice, Information or resources are much appreciated.
一个孤立的表没有逻辑连接到系统的其余部分
的外键引用表明，在概念上，实际上是一个单独的架构。通常，话语的宇宙（数据所描述的主题
）由以某种方式与其他每个相关的项目组成。从
宇宙话语模型中得出的模式同样会相互关联。
你描述的情况可能就是这种情况，单独的模式。
审计日志记录表可能存储的数据不是它所说的关于
真实世界的数据。其余数据描述的主题，但对于
，它说的是关于申请处理的一系列事件的内容。
如果是这样，它可以是合法的设计。最后，问题归结为：b $ b：你打算如何使用记录表中捕获的数据？
如果它将被用于孤立的方式（没有与DBMS的其他表格中的数据相结合），那么你的设计可能是合法的。
An isolated table that is not logically connected to the rest of the system
by foreign key references suggests something that, in concept, is really a
separate schema. Ordinarily the Universe of Discourse (the subject matter
that the data describes) is composed of items that are all related to each
other in one way or another. The schema that is derived from a model of the
Universe of discourse will likewise be interrelated.
The situation you describe may be just such a situation, a separate schema.
An audit logging table may be storing data not for what it says about the
"real world" subject matter that the rest of the data describes, but for
what it says about the series of events that the application processed.
If so, it could be legitimate design. Ultimately, the question boils down
to this: how do you intend to use the data captured in the logging table?
If it''s going to be used in an isolated fashion (not combined with data in
other tables by the DBMS), then your design may well be legitimate.
2007年6月25日星期一00:08:45 -0700， ny ******** @ hotmail.com 写道：
On Mon, 25 Jun 2007 00:08:45 -0700, ny********@hotmail.com wrote:
>
我对数据库设计的最佳实践有疑问。在
关系数据库中，有时创建通过外键关系与其他表无关的表是明智/必要的，还是总是表明某种基础设计缺陷。需要重新评估问题的域名吗？
>Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
嗨nyathancha，
它可能会发生，但它绝对不常见。对我来说，再看看会是个原因，但不要立即解雇设计。
我曾经遇到过我需要的情况不相关的表格。
这与审计有关，但根本不像你提出的方法那样... b $ b提议 - 事实上，我并不喜欢我的想法你试图给b $ b做。有一个表来记录一切分享EAV设计的许多问题 - 你将创建一个非常通用的表，其中包含一些非常好的b $ b泛型列。他们可以掌握一切，使其无法限制或查询表格中的数据。如果这是
类型的审计故事，通常应该永远不会被使用，但仅保留
，以便百万分之一的机会完全无法预料的灾难，并且
浪费无数人工时间通过收集的数据手动筛选
是在这种情况下可以接受的价格，而不是这个设计可能需要考虑
。在所有其他情况下，我都会远离它，然后去设计更加受限制的设计。
我不得不使用不相关的表的情况是在一家公司，它有一个b $ b来保存一些表的变化的完整记录 - 所以对于每一个
表，一个历史表是用所有相同的列制作的，加上一个
日期时间（作为主键的一部分），谁进行了更改的用户ID，
等我们然后在主表中添加触发器以确保每个
这些表中的
修改被正确记录在相应的
历史表中。但我们没有定义任何外键，因为简单的
之后的原因是删除客户，客户仍需保留档案的更改历史记录;我们无法从历史表中删除
客户，并且客户的外键
表将阻止DELETE。
另一个带有无关表的设计的例子，我从来没有在
练习中使用，但可以想象得足够简单，将是一个单行表来
持有申请''的处理日期 （这样一个运行过了
午夜的批处理都可以被处理，好像所有都在同一天，并且
操作可以重做[或测试]在一个模拟的dy上 - 我已经在我的大型机PL / I编程日里用这样的系统工作了
但是他们使用平面文件而不是数据库< g> ）。
Hi nyathancha,
It can happen, but it''s definitely not common. For me, it would be a
reason to look again, but not to dismiss the design right away.
I have once encountered a situation where I needed unrelated tables.
This had to do with auditing, but not at all like the method you are
proposing - in fact, I don''t really like what I think you''re trying to
do. Having one table to log "everything" shares many of the problems of
the EAV design - you''ll be creating a very generic table with a few very
generic columns. They can hold everything, making it virtually
impossible to constrain or query the data in the table. If this is the
kind of audit tale that should normally never be used but is only kept
for the 1 in a million chance of a completely unforeseen disaster, and
wasting countless man hours to sift manually through the collected data
is an acceptable price to pay in that situation, than this design MIGHT
be considered. In all other cases, I''d steer away from it and go for a
more constrained design.
The situation where I had to use unrelated tables was at a firm that had
to keep a full record of changes for some tables - so for each of those
tables, a history table was made with all the same columns, plus a
datetime (as part of the primary key), userid of who made the change,
etc. We then added triggers to the main tables to ensure that each
modification in those tables was properly recorded in the corresponding
history table. But we did NOT define any foreign keys, for the simple
reason that after e.g. a deletion of a customer, the change history of
that customer still had to be kept on file; we couldn''t remove the
customer from the history table, and a foreign key to the customers
table would have prevented the DELETE.
Another example of a design with an unrelated table that I never used in
practice but can imagine easily enough, would be a single-row table to
hold an application''s "processing date" (so that a batch that runs past
midnight can all be processed as if all was on the same date, and that
actions can be "redone" [or tested] on a simulated dy - I have worked
with such systems back in my mainframe PL/I programming days, but they
used flat files rather than databases <g>).
>我看了一下microsoft adventureworks数据库架构图
它们似乎有3个standalong表 - AWBuildVersion，ErrorLog 和DatabaseLog（除非我读错了！）
>I had a look at the microsoft adventureworks database schema diagram
and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
and DatabaseLog (unless i am reading it wrong!)
有关优秀设计的例子，请不要看任何微软提供的
样本。尽管AdventureWorks比Northwind和
酒吧领先一步，但它仍然充满了不良做法。
-
Hugo Kornelis，SQL Server MVP
我的SQL Server博客： http://sqlblog.com/blogs/hugo_kornelis
这篇关于数据库设计问题 - 孤立的，无关的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
