数据库设计：第三个表，与父表和子表相关

发布于 2024-10-06 03:31:49 字数 616 浏览 0 评论 0原文

我正在为一家银行设计一个 SQL Server 2005 数据库，用于保存与客户（父表）及其投资组合（子表）相关的记录，每个客户都有多个投资组合。到目前为止的表格：

Client (Client_Number PK ...)

Portfolio (Portfolio_ID PK, Client_Number FK ...)

我需要包含一个表格来保存相关第三方（例如基金经理、管理员、发起人等）的记录。第三方是不确定的，并且可能会发生变化，关系类型也是如此。这些关系显然是多对多的，所以我正在考虑如下附加表：

Third_Party (Third_Party_ID PK, Third_Party_Name ...)
Relationship (Relationship_ID PK, Third_Party_ID FK, Client_Number FK ...)

这可以很好地工作，但是，第三方可以与投资组合（子表）以及客户（父表）相关。

例如，客户 1 拥有投资组合 1 和投资组合 2。客户 1 和投资组合 1 链接到发起人 1，但投资组合 2 链接到不同的发起人。

对于上述情况的表格设计最佳实践有什么想法吗？

提前致谢。

原文

I'm designing a SQL Server 2005 database for a bank to hold records relating to clients (parent table) and their portfolios (child table), with each client having multiple portfolios. Tables so far:

Client (Client_Number PK ...)

Portfolio (Portfolio_ID PK, Client_Number FK ...)

I need to include a table to hold records for related third parties (e.g. Fund Manager, Administrator, Promoter etc.). The third parties are undetermined and liable to change, as are the relationship types. The relationships are obviously many-to-many, so I was thinking additional tables as follows:

Third_Party (Third_Party_ID PK, Third_Party_Name ...)
Relationship (Relationship_ID PK, Third_Party_ID FK, Client_Number FK ...)

This would work fine, but, the third parties can be related to Portfolios (child table) as well as Clients (parent table).

For example, Client 1 has Portfolio 1 and Portfolio 2. Client 1 and Portfolio 1 are linked to Promoter 1 but Portfolio 2 is linked to a different Promoter.

Any idea as to best practice for table design for the above situation?

Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风轻花落早 2024-10-13 03:31:49

我会选择两个附加表：

Client_ThirdParty
Portfolio_ThirdParty

它将充当两个现有表和“第三方”之间的链接实体。我会避开“关系”表，它看起来像是伪装的元数据。

编辑

例如，客户 1 拥有投资组合 1
和投资组合 2。客户 1 和
投资组合 1 与推广者 1 关联
但投资组合 2 链接到
不同的启动子。
您的意思是另外三张桌子吗？
第三方、客户关系和
投资组合_关系？我问这个
客户和投资组合
关系是一个单一的“池”
第三方。

好的，如果需要考虑更多关系，那么您可能需要更多的复杂性（但由于我不知道您的数据，我可能会错过一些东西！）：

客户可以有许多投资组合
客户可以有许多第三方
投资组合可以有 1 个客户？
投资组合可以有许多第三方
第三方可以有许多客户
第三方可以制作许多投资组合

如果这是正确的，那么：

Client  
Portfolio (contains ClientId to refer to its client)
ThirdParty

Client_ThirdParty  <-- link entity that handles the Client/ThirdParty M-to-M
Portfolio_ThirdParty  <-- link entity that handles the Portfolio/ThirdParty M-to-M

I would go for two additional tables:

Client_ThirdParty
Portfolio_ThirdParty

Which would act as link entities between the two existing tables and 'ThirdParty'. I would steer clear of the 'Relationship' table which looks like metadata in disguise.

EDIT

For example, Client 1 has Portfolio 1
and Portfolio 2. Client 1 and
Portfolio 1 are linked to Promoter 1
but Portfolio 2 is linked to a
different Promoter.
Do you mean three additional tables?
ThirdParty, Client_Relationship and
Portfolio_Relationship? I ask this as
both Client and Portfolio
relationships are to a single 'pool'
of Third Parties.

OK, if there are further relationships to consider then you may need more complexity (but as I don't know your data I may miss something!):

Clients can have many Portfolios
Clients can have many ThirdPartys
Portfolios can have 1 Client?
Portfolios can have many ThirdPartys
ThirdPartys can have many Clients
ThirdPartys can mave many Portfolios

If this is correct then:

Client  
Portfolio (contains ClientId to refer to its client)
ThirdParty

Client_ThirdParty  <-- link entity that handles the Client/ThirdParty M-to-M
Portfolio_ThirdParty  <-- link entity that handles the Portfolio/ThirdParty M-to-M

回复收藏 0 原文

~没有更多了~