如何使用RDBMS建模这种多重继承关系？

问题描述

我正在看这个我想出来的数据模型，感觉不舒服。我改变了实体名称，这样（希望）更有意义。无论如何，您将如何模拟以下内容？

我有3个实体。 GovernmentCustomer，PrivateCustomer，PublicCustomer。私人和公共客户都是企业客户。公司和政府客户是账户。所有帐户共享相同的密钥空间（因此，如果PrivateCustomer的PK为1，则Public或GovernmentCustomer的PK值不可能为1）。公司客户有一些1：M的关系，政府客户不要。 PublicCustomers有一些私人客户没有的1：M关系。

继承：

 帐户
 CorporateCustomer 
 PrivateCustomer 
 PublicCustomer 
政府客户
  

现在我的模型有5个表。 帐户表是此层次结构的根，每个子表的PK是其父对象的PK的FK。所以所有的表都有相同的PK。

那么，你怎么模拟这个？我希望一些事情在这里没有错误：）。

编辑：

另外：
- 我希望数据库可以照顾ref，而不是应用程序。
- 企业客户不可能存在而不是私人或公共客户。它的摘要。

解决方案

单向可以是：

  ACCOUNTS  - > ACCOUNT_CUSTOMERS<  -  CUSTOMERS 
  

让CUSTOMERS拥有一个CUSTOMER_TYPE列，类型为Corporate（C），Private （P），公共（Z），政府（G）。由于所有公共和关键客户也是公司，如果您需要获得所有公司客户，您可以执行以下操作：

  SELECT * 
 FROM ACCOUNTS 
，ACCOUNT_CUSTOMERS 
，CUSTOMERS 
 WHERE ACCOUNTS.ID = ACCOUNT_CUSTOMERS.ACCT_ID 
 AND CUSTOMERS.ID = ACCOUNT_CUSTOMERS.CUST_ID 
 AND CUSTOMERS.CUSTOMER_TYPE in （'C'，'P'，'Z'）
  

我使用ORACLE语法，但我认为你得到这个想法。

 为了回应你的编辑：
  

听起来你只有两种类型的CUSTOMERS。企业和政府。这更容易。我会在名为PUBLIC_IND的CUSTOMERS上使用一个布尔指标，当false是私有的，或者类似于ENTITY_TYPE的类型，可以是Private（P），Public（Z）或None（N）。然后，如果您想获得所有公共公司客户的用户：

  SELECT * 
从ACCOUNTS 
，ACCOUNT_CUSTOMERS 
，CUSTOMERS 
 WHERE ACCOUNTS.ID = ACCOUNT_CUSTOMERS.ACCT_ID 
 AND CUSTOMERS.ID = ACCOUNT_CUSTOMERS.CUST_ID 
和CUSTOMERS.CUSTOMER_TYPE（'C'）
和客户.ENTITY_TYPE ='Z'
  

I'm looking at this data model I've come up with and not feeling comfortable. I've changed the entity names so it (hopefully) makes more sense. In any event, how would you model the following?

I have 3 entities. GovernmentCustomer, PrivateCustomer, PublicCustomer. Private and Public Customer are both CorporateCustomers. Corporate and Government Customers are Accounts. All Accounts share the same key space (So if PrivateCustomer has a PK of 1 it shouldn't be possible for Public or GovernmentCustomer to have a PK of 1). CorporateCustomers have some 1:M relationships that GovernmentCustomer's don't. PublicCustomers have some 1:M relationships that PrivateCustomers don't.

The Inheritance:

Account
  CorporateCustomer
    PrivateCustomer
    PublicCustomer
  GovernmentCustomer

Right now my model has 5 tables. The "Account" table being the root of this hierarchy with each child table's PK being a FK to its parent's PK. So all the tables have the same PK.

So yeah, how would you model this? I hope something hasn't gone wildly wrong here :).

EDIT:

Also:- I'd like the DB to be taking care of ref integrity not the app.- Its not possible for a CorporateCustomer to exist without being either a Private Or Public customer. Its abstract.

解决方案

One Way could be:

ACCOUNTS -> ACCOUNT_CUSTOMERS <- CUSTOMERS

Make CUSTOMERS have a CUSTOMER_TYPE column that is of type Corporate(C), Private(P), Public(Z), Government(G). Since all public and pivate customers are also Corporate if you needed to get all corporate customers you could do something like:

SELECT *
  FROM ACCOUNTS
     , ACCOUNT_CUSTOMERS
     , CUSTOMERS
 WHERE ACCOUNTS.ID = ACCOUNT_CUSTOMERS.ACCT_ID
   AND CUSTOMERS.ID = ACCOUNT_CUSTOMERS.CUST_ID
   AND CUSTOMERS.CUSTOMER_TYPE in ('C','P','Z')

I used ORACLE syntax, but I think you get the idea.

In response to your edit:

It sounds like you only have two types of CUSTOMERS. Corporate and Government. This is even easier then. I would use a boolean indicator on CUSTOMERS called PUBLIC_IND that when false is private, or another type like ENTITY_TYPE that could be Private(P), Public(Z), or None(N). Then if you wanted to get all public Corporate customers user:

SELECT *
      FROM ACCOUNTS
         , ACCOUNT_CUSTOMERS
         , CUSTOMERS
     WHERE ACCOUNTS.ID = ACCOUNT_CUSTOMERS.ACCT_ID
       AND CUSTOMERS.ID = ACCOUNT_CUSTOMERS.CUST_ID
       AND CUSTOMERS.CUSTOMER_TYPE in ('C')
       AND CUSTOMERS.ENTITY_TYPE = 'Z'

这篇关于如何使用RDBMS建模这种多重继承关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！