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

I know that this question is very old, but since it doesn't have an accepted answer yet, I have a couple of ideas.

One possibility is to use ORDBMS features - in other words, use table inheritance. In PostgreSQL you might model it like this:

(See the documentation about PostgresSQL inheritance http://www.postgresql.org/docs/9.3/static/ddl-inherit.html)

CREATE TABLE account
(
   account_id INT,
   PRIMARY KEY(account_id)
);

CREATE TABLE corporate_customer
(
   company_name VARCHAR(32),
   country_code CHAR(2),
   PRIMARY KEY(company_name)
) INHERITS(account);

CREATE TABLE private_corp_customer
(
   private_comp_id INT,
   company_owner VARCHAR(32),
   PRIMARY KEY(private_comp_int)
) INHERITS(corporate_customer);

CREATE TABLE public_corp_customer
(
   stock_ticker VARCHAR(6),
   PRIMARY KEY(stock_ticker)
) INHERITS(corporate_customer);

CREATE TABLE government_customer
(
   dept_nbr INT,
   country CHAR(2),
   PRIMARY KEY(dept_nbr)
) INHERITS(account);

Different DBMS vendors will implement this in different ways. In PostgresSQL, there are some important caveats described here:

http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-3-table.html

In particular, note the part about primary and foreign keys not being inherited.

If you don't like the limitations of your DBMS or are using a DBMS that does not have object-relational features, then another option is to use an alternative suggested in the article above and use secondary keys. That would be modeled like this:

CREATE TABLE account
(
   account_id INT,
   account_type INT NOT NULL,
   PRIMARY KEY(account_id),
   UNIQUE (account_id, account_type)
);

CREATE TABLE corporate_customer
(
   account_id INT,
   account_type INT NOT NULL CHECK(account_type IN (1,2)),
   company_name VARCHAR(32),
   country_code CHAR(2),
   PRIMARY KEY(account_id, account_type),
   FOREIGN KEY(account_id, account_type) REFERENCES account(account_id, account_type),
   UNIQUE(account_id, account_type, company_name)
);

CREATE TABLE private_corp_customer
(
   account_id INT,
   account_type INT NOT NULL CHECK(account_type = 1),
   company_name VARCHAR(32),
   company_owner VARCHAR(32),
   PRIMARY KEY(account_id, account_type, company_name),
   FOREIGN KEY(account_id, account_type, company_name) REFERENCES corporate_customer (account_id, account_type, company_name)
);

CREATE TABLE public_corp_customer
(
   account_id INT,
   account_type INT NOT NULL CHECK (account_type = 2),
   company_name VARCHAR(32),
   stock_ticker CHAR(6),
   PRIMARY KEY(account_id, account_type, company_name),
   FOREIGN KEY(account_id, account_type, company_name) 
   REFERENCES corporate_customer (account_id, account_type, company_name)
) INHERITS(corporate_customer);

CREATE TABLE government_customer
(
   account_id INT,
   account_type INT NOT NULL CHECK(account_type = 3),
   dept_nbr INT,
   country_code CHAR(2),
   PRIMARY KEY(account_id, account_type),
   FOREIGN KEY(account_id, account_type) REFERENCES account(account_id, account_type),
   UNIQUE(account_id, account_type, dept_nbr)
 );

The above design has some important limitations too (which are also described in the article above). For one thing, though it shouldn't be possible to have an account that is not a private, public, or government customer, it is possible to do so; you can have just accounts, corporate accounts that are neither public nor private... it becomes a nightmare to maintain. The CHECK constraints can hurt performance too, and you'll notice that there is both data duplication in the child entities, as well as missing information in the corporate child entities (country_code).

Which limitations you choose will be dependent on your DBMS vendor and how much headache you want to manage.

Answer 2

Before you commit to putting every class in the same table, I would look at the relationships they are involved in - not the attributes. It's trivial to leave certain fields null if record type is 'X', but it's incredibly unwieldy to try to have relationships that only apply to certain records in a table.

If the relationships of the classes with other entities is exactly the same, then you can stuff them all in one table with no real downside.

Answer 3

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 4

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 5

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.

Answer 6

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.