混合单/多逻辑数据库建模的最佳方法 [英] Best way to model hybrid single/multiple logical database

问题描述

我最近多次遇到过这个问题，而且我从来没有想过

一个满意的答案来解决这个架构问题的最佳方法。你有一个

的大部分架构，其中所有表中的记录子集是

通常被认为是一个单独的逻辑系统，但有时可能会被处理掉。

是全球系统的一部分，逻辑数据库中的

记录中不仅有1-mm ...树。


这是一个例子。最初设计为仅处理单个公司的数据的系统现在正处理多个公司的数据。因此，要实现的最简单的概念是简单地将CompanyID添加到所有

表中，并将其包含在表之间的所有关系中。

不知何故，这不是很令人满意。


这个问题的一个明显问题是，在行数最多的表中，行数为b $ b添加一列是一个很大的成本。这些都是subdetail

记录，因此人们会认为只依靠父母

记录的CompanyID，但等待 - 详细信息表引用查找表

也是公司特定的。如果表格中省略了CompanyID，那么
无法让关系强制所有相关记录属于同一公司的
。可以依靠应用程序来做到这一点，但是当手动表维护执行时，这个

会留下一些完整性问题的危险。


有没有人想出更好的策略来处理这类设计？

I''ve run across this issue several times of late, and I''ve never come up with
a satisfactory answer to the best way to handle this schema issue. You have a
large section of schema in which a subset of records across all tables is
often considered a separate logical system, but sometimes may be treaded ar
part of the global system, and there is not simply a 1-m-m... tree among the
records in a logical database.

Here''s an example. A system that was originally designed to handle only data
for a single company is now handling data for multiple companies. So, the
simplest concept to implement would be to simply add CompanyID to all the
tables, and include that in all the relationships between the tables.
Somehow, that''s not very satisfying.

One obvious problem with this is that among the tables with the largest number
of rows, adding a column is a big cost in size. These are mostly subdetail
records, so one would think it would work to simply rely on the parent
record''s CompanyID, but wait - The detail table references lookup tables that
are also company-specific. If the CompanyID is omitted from the table, there
is no way to have the relationship enforce that all the related records belong
to the same company. One can rely on the application to do that, but this
leaves some danger of integrity problems when manual table maintenance is
performed.

Has anyone come up with a better strategy for handling these kinds of designs?

推荐答案

" Steve Jorgensen" <无**** @ nospam.nospam>在消息中写道

新闻：1h ******************************** @ 4ax.com ...

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:1h********************************@4ax.com...

这是一个例子。最初设计为仅处理单个公司的
数据的系统现在正在处理多个公司的数据。


你怎么知道？什么是一个特定的数据，它允许你认为 - 啊，这是一个关于Acme Widget公司的事实。如果你已经告诉

，也许这就是你的标识符，如果你不能那么它可能不会是b / b
做它看起来正在做的事情。 br />

也许一些实际的表和样本数据会有帮助吗？我们不能真的

告诉它是什么类型的应用程序。

因此，实现的最简单的概念是简单地将CompanyID添加到所有
表格，并包括表格之间的所有关系。
不知何故，这不是很令人满意。


大概也有一些交汇处？可能是你的唯一途径。

有没有人想出更好的策略来处理这些

Here''s an example. A system that was originally designed to handle only data for a single company is now handling data for multiple companies.
How can you tell? What is it about a particular datum which allows you to
think - ''ah, this is a fact about the Acme Widget Company''. If you can tell
already, perhaps that''s your identifier, if you can''t then it might not be
doing what it appears to be doing.

Maybe some of the actual tables and sample data would help? We can''t really
tell what sort of application it is.
So, the
simplest concept to implement would be to simply add CompanyID to all the
tables, and include that in all the relationships between the tables.
Somehow, that''s not very satisfying.
And presumably some junction tables too? Might be your only way.
Has anyone come up with a better strategy for handling these kinds of

的设计？


呃，重新开始？在我看来，一个应用程序旨在处理一个

组织的数据，然后开始处理> 1组织的数据 - 好吧，

这是一个根本性的变化不是吗？


Mike

designs?

Er, start again? Seems to me an app that is designed to handle data for one
organisation that then starts to handle data for >1 organisation - well,
that''s a fundamental change isn''t it?

Mike

2004年1月3日星期六07:45:49 -0000，" Mike MacSween

< mi ****************** @ btinternet.com>写道：

On Sat, 3 Jan 2004 07:45:49 -0000, "Mike MacSween"
<mi******************@btinternet.com> wrote:

" Steve Jorgensen" <无**** @ nospam.nospam>在消息中写道
新闻：1h ******************************** @ 4ax.com ..。

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:1h********************************@4ax.com.. .

这是一个例子。最初设计用于处理单个公司的

Here''s an example. A system that was originally designed to handle only

数据

的系统现在正在处理多个公司的数据。

for a single company is now handling data for multiple companies.

你怎么知道？什么是允许你思考的特定数据 - '啊，这是关于Acme Widget公司的事实''。如果你已经告诉
，或许这是你的标识符，如果你不能那么它可能不会做它看起来正在做的事情。

也许一些实际的表格和样本数据会有所帮助吗？我们不能真的告诉它是什么类型的应用程序。

How can you tell? What is it about a particular datum which allows you to
think - ''ah, this is a fact about the Acme Widget Company''. If you can tell
already, perhaps that''s your identifier, if you can''t then it might not be
doing what it appears to be doing.

Maybe some of the actual tables and sample data would help? We can''t really
tell what sort of application it is.

因此，实现的最简单的概念是简单地将CompanyID添加到所有
表，并包括在表之间的所有关系中。
不知何故，这不是很令人满意。

So, the
simplest concept to implement would be to simply add CompanyID to all the
tables, and include that in all the relationships between the tables.
Somehow, that''s not very satisfying.

大概也有一些联结表？可能是你唯一的方法。

And presumably some junction tables too? Might be your only way.

有没有人想出更好的策略来处理这些

Has anyone come up with a better strategy for handling these kinds of

设计？

呃， 重新开始？在我看来，一个应用程序旨在处理一个组织的数据，然后开始处理> 1组织的数据 - 好吧，
这是一个根本性的改变不是吗？ />
Mike

designs?

Er, start again? Seems to me an app that is designed to handle data for one
organisation that then starts to handle data for >1 organisation - well,
that''s a fundamental change isn''t it?

Mike

无论是设计更改还是初始设计

要求，都会出现同样的问题。在设计方面描述问题更容易

更改。在任何一种情况下，都有一大部分架构，其中所有

数据通常由某个拥有实体（如公司）进行分区。

The same issue comes up whether it is a design change or an initial design
requirement. It''s just easier to describe the issue in terms of a design
change. In either case, there is a large section of schema in which all the
data is normally partitioned by some owning entity such as a Company.

Steve Jorgensen <无**** @ nospam.nospam>在留言中写道

news：q0 ******************************** @ 4ax.com ......

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:q0********************************@4ax.com...

无论是设计变更还是初始设计要求，都会出现同样的问题。


当然，那么设计是什么？

在设计方面更容易描述问题
改变。


然后描述它。

在任何一种情况下，都有一大部分模式，其中所有的数据通常由一些拥有的人分割诸如公司之类的实体。

The same issue comes up whether it is a design change or an initial design
requirement.
Sure, what''s the design then?
It''s just easier to describe the issue in terms of a design
change.
Describe it then.
In either case, there is a large section of schema in which all the
data is normally partitioned by some owning entity such as a Company.

通过''大部分架构''你的意思是某些表要做

with一家公司？或者某些表中的某些行与公司有关？


也许你可以告诉我们更多。像一些表格一样，它们包含哪些数据，以及它们之间的关系。


干杯，Mike MacSween

By ''a large section of schema'' do you mean that certain tables are to do
with a Company? Or that some rows from some tables are to do with a Company?

Maybe you could tell us some more. Like some of the tables, what data they
contain, and the relationships between them.

Cheers, Mike MacSween

这篇关于混合单/多逻辑数据库建模的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！