From: Alan Seltzer

Date: 1 Jan 2006

I assign unique numbers to each of my orders but the numbers take account of the year.  For example, my first 2006 order will be numbered 1060001, just as my 2005 orders were numbered 105nnnn.  Have I violated the relational model by using numbers that imply the year and go back to 0001 at the beginning of the

new year? 

And a much more interesting question: does it really matter?  Is there an essential difference between a massive, multi-user relational database (such as those that Codd primarily has in mind in his writings and which take account of his IBM background and clientele), and a database that I alone will use, and where the amount of data is so limited that I have rough knowledge of its contents from memory before querying the database?

From: Fabian Pascal

Unless order numbers are somehow dictated externally, it is up to you to choose their numbering, so you can choose whatever key you want, which in this case is both surrogate and natural, so to speak (as long as you will never need the DBMS to break them).

Ed. Note: More accurately, the surrogate key becomes natural.

More generally, RM only requires that tables have at least one candidate key, but is mute on how to choose keys; that is a pragmatic, not theoretical decision. See chapter 3 in PRACTICAL ISSUES IN DATABASE MANAGEMENT for the four criteria which must be traded off in choosing candidate keys. A PRACTICAL DATABASE FOUNDATION paper on keys is forthcoming.

There is always a price to pay for not adhering to sound foundations, regardless of the specific database. However, some of the negative consequences are attenuated if the designer of the database is also the only user. That's because he knows and understands the database and is less likely to mess things up or get stuck. The price is much heavier if the database is shared by multiple users who are not familiar with the conceptual and logical model as its designer is.

Posted 3/3/06

© Fabian Pascal 2006 All Rights Reserved