How to model this multiple inheritance relationship with a RDBMS?

Question

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.

Answer 1

Unless there is significant difference in the attributes that's being tracked among different types of cusomter, I'd just have one table called Account with some CustomerType field. you can express the 1:m relationship by detail tables having FK to AccountID.

Edit: Modern databases can add data integrity rules beyond FK referential integrity. For example with SQL Server you could add CHECK Constraints to enforce AccountType to be GovernmentCustomer for certain detail table's master. It could look something like this:

CREATE FUNCTION EnforceGovernmentCustomer(@AccountID int)
RETURNS bit
AS 
BEGIN
   DECLARE @retval bit
   SELECT @retval = 0
   SELECT @retval = 1
   FROM Account
   WHERE AccountID = @AccountID AND AccountType = 3

   RETURN @retval
END;
GO
ALTER TABLE GovernmentCustomerDetail
ADD CONSTRAINT chkGovernmentCustomer CHECK (dbo.EnforceGovernmentCustomer(AccountID) = 1);
GO

Answer 2

I think you should just have an Account and a Customer table, along with a CustomerRelationship table. The different types of customers can be differentiated with a type code of some sort, and the relationships can be upheld using a CustomerRelationship table.

Answer 3

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'

Answer 4

i agree with the others that a customerType field should suffice if the different types of customer are similar.

that said, perhaps just the corporate customers share a table, but the government customers are different enough that they need to be defined in their own table. in the event that this is warranted, one design to help you enforce the PK constraint would be to have a MasterAccount table that would reference all the customers by id (the PK constraint), and have a reference to their type on the next level of the hierarchy (corporate or government).

you still need to map the 1-* relationships, which you could do with yet another two tables - a Relationship table and an account-relationship mapping table.