3

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?

  • Are you using foreign keys in this design? If so how are they defined? An ERD would make it easier to help you. – James Anderson Sep 13 '16 at 8:15
  • @jamesanderson Well the OrgName field of the Account relation references the OrgName field of the Organization relation. Similar comments about the other fields apply. I hesitate to mark them as "foreign keys" because I read somewhere that foreign keys are never primary keys. But that's really what they are I suppose. Should I add that to the question do you think? – jayshua Sep 14 '16 at 1:40
  • Are the columns OrgName, AccountID, and InvoiceNumber unique in their respective tables? – James Anderson Sep 14 '16 at 8:55
  • 1
    It is not true that foreign keys are never primary feys. See the tag "shared-primary-key" for counter-examples. – Walter Mitty Sep 14 '16 at 11:07
  • In order to detect violations of 2NF or 3NF, you need to know the non key fields as well as the key fields. You haven't presented them in your question. – Walter Mitty Sep 14 '16 at 11:10
1

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

  • Is it possible to assume that there are no other fields? Say, the records are only kept to show existence? – jayshua Sep 15 '16 at 2:50
  • As Michael stated, without the other attributes, there is no way to tell if there is any violation of normalization. However, that also means that the tables as described could very well be perfectly normalized. The purpose of normalization is to limit the possibility of introducing anomalous data through DML operations. It does not mean that DML operations are more efficient. However, it does not require that DML operation be inefficient and so we have aids such as surrogate keys to help us when needed. – TommCatt Sep 15 '16 at 9:25
  • It is OK to have relations with all attributes in the key. Reference tables or enumerations could well be like this. Tables which implement many-to-many relationships are likely to have all columns in the key. My suggestion "B" is an example of one. Such tables are normalised by definition. There is another discussion to be had, separate from normalisation, about whether such tables accurately model to situation being addressed. – Michael Green Sep 15 '16 at 11:50
0

I think your keys are incorrect. I would have a schema like the following:

Organization
OrgName Primary Key

Account
AccountID Primary Key
OrgName Foreign Key

Invoice
InvoiceNumber Primary Key
AccountID Foreign Key 

This schema would conform to 3rd normal form. InvoiceNumber must be unique across all accounts. If it's not then a surrogate column that is unique in the table or a composite key of InvoiceNumber and AccountID could be used.

Currently your Invoice table violates 2nd normal form. It violates 2nd normal form because OrgName, as I have described above, is not required to be part of the key. If it is not part of the key that makes it a non-key attribute that does not rely on the key of InvoiceNumber. It will be repeated for every invoice created for an organisation.

  • 2
    There is no violation if (OrgName, AccountID) is the only UNIQUE key in Account. – ypercubeᵀᴹ Sep 14 '16 at 8:48
  • Yes that's true. If AccountID is not unique then perhaps a surrogate key that is unique would be better, but I guess that's just my opinion. There may not be a "correct" answer here. – James Anderson Sep 14 '16 at 8:52
  • 1
    If an invoice is only ever associated with the account for which it is created there is no problem retaining (AccountID, InvoiceNumber) as the key of Invoice. Transferring an account between organisations will not impinge on the invoices. – Michael Green Sep 14 '16 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.