Eliminating redundant relationships when modeling Header/Detail relationships?

Question

I've got a model that looks something like this:

One Account has many Branches, and each Statement is generated for one Account. The model is redundant because the Account (the AccountID on the header) can be inferred from the BranchID on a transaction (a statement will always have one or more transactions).

Should the AccountID be removed from the StatementHeader, or is this level of redundancy OK? Or is there a better solution?

Answer 1

If you have the StatementHeader then it should have the AccountID to keep the referential integrity.

However it may be better to remove the StatementHeader completely and move the StatementDate into the Statement record. This would make things cleaner and make the model better describe what you want.

Answer 2

Is there any advantage of this redundant association? Can you build better/faster queries with it? If yes, then the redundancy is ok.

Answer 3

As a statement is historical, and usually read only, data some redundency is fine. I agree with Richard Harrison and would move both [AccountID] and [StatementDate] in to the [Statement] table; my reasoning being you say that an account has many branches so you will be genereating a statement for an Account.

Storing all this data in the same place will reduce joins and speed up reporting, which i assume is the reason for this database.

Answer 4

Sometimes, (real or perceived) redundancy is a consequence of a business rule. In this case, the business rule is: "a statement that is issued to an account shall contain only transactions for branches that belong to that particular account."

To enforce that rule, you could try to come up with a database schema that makes it impossible to violate it, or enforce it explicitly with a constraint or trigger. And that seems to be easier with StatementHeader.AccountID. In Oracle, you could write something like this:

create or replace trigger statement_has_unique_account
before insert or update on Statement
referencing old as old new as new
for each row
declare
  m integer;
  n integer;
begin
  select b.AccountID
  into m
  from Branch b 
  where b.ID = new.BranchID;

  select s.AccountID
  into n
  from StatementHeader s
  where s.ID = new.StatementID;

  if m <> n then
    raise_application_error(-1000, 'No way!');
  end if;
end;

Without AccountID in StatementHeader, you'd have to write a comparison with all the other AccountIDs from all other Statements that share the same StatementID, resulting in a more complicated sequence of statements.

So i would keep AccountID as a foreign key in StatementHeader and enforce the business rule explicitly with a trigger.