Hi Jim..


>>
Of course a surrogate key being always a single field obviates the need to apply 4th or 5th normal forms
<<

My understanding of the BCNF to be relevant, you must have a table with more than one concatenated candiate key. And, each of these keys must share a common data element. For example, if we have a table with fields A, B, C, D, and E, for BCNF to apply, something A+B and B+C - each being a candiate key and each sharing a common data element - B in this case would have to exist. Futher, you would have to have different sets of fields dependent on each key. For example, D being dependent on A+B and E being dependent on B+C. The resulting BCNF would be two tables. The first would have fields A, B, D. The second table would have fields B, C, E. These two tables would now be in the BCNF.

Finally, for the BCNF to apply, no single field can be sufficent as a primary key. Therefore, once you have introduced a surrogate key - which by itself would be sufficient to be a primary key - there is no need to split 1 table into 2 - and thus give rise to the BCNF.

That said, I think once a surrogate key exists, the BCNF is irrelevant.

Or, have I missed something here????

< jvp >

In Codd We Trust



>Martin,
>
>Although I am a proponent of using surrogate keys there is one downside, that is the presense of a surrogate key often means that there is at least one other candidate key in the table design (that being the natural key). This requires that the Boyce-Codd Normal Form be applied making 3rd NF not quite good enough. Of course a surrogate key being always a single field obviates the need to apply 4th or 5th normal forms, which is why I suggest using surrogate kayes always. That along with E. F. Coods statement that, "Intelligent keys aren't".