I came across this answer on Stack Overflow. The question itself isn't really relevant. The answer outlined a database design that I will paraphrase here:
Consider the relations Organization, Account, and Invoice. The Organization key is OrgName. The Account key is OrgName, AccountID. The Invoice key is OrgName, AccountID, and InvoiceNumber. As suggested by the keys, an organization has many accounts and an account has many invoices. (Note that AccountIDs can be reused across organizations and InvoiceNumbers can be reused across Organizations and Accounts – hence the need to have two and three attribute keys. Clarified further, AccountID and InvoiceNumber are not individually unique within their respective tables.)
It strikes me that if you need to move an account to another organization, that would necessitate an update not only in the Account relation, but also in the Invoice relation. This "redundancy" leads me to believe that a normal form is violated in this design, though I've been unable to identify which one.
Thus my questions is: Is a normal form violated in this design? If so, which one and why?
Best Answer
Normalisation talks about the atomicity of attributes within a relation and the dependency of attributes on candidate keys. Since there is no mention of non-key attributes in the question it is not possible to comment on the normalisation or otherwise of this design.
That said, I would like to comment on the semantics of this design with regards to the business requirement to move accounts between organisations. In this design you cannot move an account to another organisation. Primary keys, by definition, distinguish one item from another. The account's key includes the organisation id. Therefore an account for one organisation is a different thing to an account with a different organisation, even if the two accounts have the same values in all attributes.
By extension, moving an account would not cause Invoice to be updated. Rather, new rows would be created in Invoice, with values copied from the existing (Organisation, Account, Invoice) rows.
The old Invoice rows could be deleted or not, it doesn't matter to the argument. Although an insert followed by a delete looks to an outside observer like an update, we are talking about primary keys here, which define the existence and distinguishability of objects. If I were to replace your Ford by a Ferrari while your eyes were closed would you, on opening them, think "Wow, my car has spontaneously morphed!" No, one thing has gone and another, functionally similar thing arrived.
If the problem domain requires accounts to be movable yet still recognised as one continuing object you could
A) key the account off account id solely, and have organisation id as a foreign key called "current owner"
B) introduce a new table called "account transfer" with attributes
From owner
From account
To owner
To account
Start date
End date