Originally Posted by felgall
Any table that contains a derived key as well as a natural key is not fully normalised. Normalising it will get rid of the derived key.
While there are sometimes reasons for using a derived key in addition to a natural one (eg the natural key is very large) but the decision to undo that part of the normalisation should be done after the logical database design is completed when applying those changes to the logical design needed to make your physical design practical. Where the logical design can be converted to a practical physical design without undoing this normalisation then the derived key should not be added. Everywhere that your physical design differs from a fully normalised logical design you should have a specific reason for that change.
The tables described in my posts above are in 3rd Normal Form as far as I can see.
It is also entirely possible to use *just* Natural Keys for my Primary and Foreign Keys.
So that meets what you are saying about the "Logical Model Design Phase".
I am now in implementation, and I have chosen to add an "ID" field that is AutoIncrement, Null = No, and has a Unique Index to these tables because having such a field in each table...
1.) Makes it easier to identify a particular record. (Why try and find an Article Slug that is a VARCHAR(100) when I can simply look for ArticleID=3975?!)
2.) It makes joins much easier.
3.) As things grow, it *may* have performance implications, although that is yet to be seen.
4.) There is virtually *no overhead* in having a ubiquitous "ID" field with AutoIncrement and a Unique Index, so why not?!
BTW, thanks for the thoughts, but you clearly must be from the "Natural Key Camp", because there are millions of DBA's out there that would vehemently disagree.
They would say that you should always have an AutoIncrement ID field on every table - in addition to the Natural Keys. Further, they would insist that you *always* use a Surrogate Key to join tables!!
To me, that is as extreme as saying to never use Surrogate Keys - which I am not implying you said.
For me, I want to balance these two extremes, and ideally, have it both ways!