Re: assigning codes to events

In article <d8eb949e.0206181605.6a3361f2_at_posting.google.com>, Chris Cubley <chriscubley_at_hotmail.com> wrote:
>
>To me, this approach seems to violate the first normal form by making
>the ResultCode field non-atomic. It then indicates both that the
>letter was returned and the reason why the letter was returned. Am I
>correct in this or am I misinterpreting the 1NF?

What is non-atomic for the client/user may still be atomic for the database, and it is the database perspective that is the most relevant to 1NF. Think for example of BLOBs that represent XML documents. As long as the database treats these as black boxes you can have BLOBs in you fields and still be in 1NF. So if in none of your SQL queries you start computing with the code to retrieve the encoded information, then the database is treating it as an atomic value.

However, chances are that even if in your current SQL queries you treat it today as a black box, someday in the future you will probably want to do a select or a join based on the encoded information, e.g., select all delivered letters, and in that case you should split it out into separate columns so that indices can be defined separately on them. So even in that case it would probably be wise to anticipate on this.

Finally, your solution is more robust wrt. future extensions; you can add as many columns as you like and the current code will keep on working without much change, whereas in the encoding-solution you may in certain cases have to rethink your encoding.

• Jan Hidders