Hi! I'm designing a database in which I have a relation where there is no candidate key with a single field. I have determined, by definition, that 5 fields toghether are primary key (two years, two months and an ID which is an external key and refers to an ID of a bank account). I was wondering if it would be better to create a single field primary key (an auto incremented ID) instead or not... Is it something frequent to have so many fields as primary key? Can there be problems in future using so many fields?
Thanks!

I would stongly recommend putting a UNIQUE constraint on the natural key (the five column key), then creating a single column surrogate key (an autonumber will do fine) for a PRIMARY KEY.

-PatP

Hi! Thanks for your answer! In fact I remember my university teacher told to avoid too many fields in the primary key but... I can't remember the reason. Why do I have to avoid this? I also have a relation for bank accounts. I put as primary key the country code, ABI, CAB, CIN etc... Would it be better to apply the same concept there using an autonumber?
Thanks!

I usually prefer using an auto-incremented number as the primary key and putting a unique index on the multi-column series (also known as an alternate key). The reason is that if any of the columns get changed for some reason (for instance the format of the account number gets changed) and you have used the multi-column series as the primary key then you have to change it in every reference to the table. It also takes up more space in every table that references this one.

Some people argue that using the multi-column series is better, some that using the system assigned auto-incremented number is better. I prefer the auto-incremented simply because of the possibility of future change. An arbitrary number won't change, a key made up of user data might.

This is clear. Thank you very much!
Another question: does using an autonumber (ID) in the situation described decrease the degree of the normal form? I mean, assuming the relation was in BCNF, adding an autonumber makes the relation be no more in BCNF? I don't think so, right?
Thanks!

Since the relationship would only be by the auto number then it would be third normal form.

Assuming you enforce BOTH keys and not just one then your table will still be in BCNF. The autonumber should not be the only key of the table otherwise you would create non-key dependencies and other problems.

I didn't think surrogates were "allowed" in BCNF if a natural key exists? *shrug* I get bogged down in these definitions sometimes.

No normal form actually excludes the possibility of surrogate keys as far as I'm aware. It's just that most of the good literature on normalization theory doesn't have anything to say about surrogates.

Here are my thoughts. Take the following relation:

R {j,k,a} KEY {j,k}

BCNF just means that every non-trivial determinant is a superkey. So provided {j,k}->{a} is irreducible then R is certainly in BCNF.

Now add a surrogate:

R {i,j,k,a} KEY {j,k} KEY {i}

It seems to me there are two ways to understand a surrogate key.

One view is that i is a determinant because it is a value at some point in time. Keep in mind that classical normalization theory is concerned specifically with relations (i.e. values), not relvars (variables). Call this the "relation" view of surrogates.

There is another perspective: the "relvar" view. The relvar view is that i is not a determinant because it is a variable that may take on an arbitrary value. However, it is certainly unique and therefore it is harmless as a key. {j,k} must still be a key as well otherwise BCNF is violated. The dependency {i,j,k}->{a} is satisfied and {i,j,k} is a superkey therefore R is still in BCNF.

That's my interpretation. You won't find it in any textbook I know of however because most of the scientific texts aren't interested in the distinction between "natural" and "non-natural" keys whereas most of the books and articles aimed at practicing professionals aren't very interested in formal definitions.

As an aside, Codd defined a surrogate very differently to the conventional modern usage of the term. In his RM/T paper Codd proposed a surrogate "key" as a hidden attribute, more like an index than a key, and therefore presumably not subject to the usual design principles like normalization. However, almost everyone has forgotten Codd's definition and uses the term surrogate to mean just any generated key that doesn't have a meaning outside the database.

I'm looking at the BCNF definition.
A relation R is in BCNF if, any time that a non-trivial functional dependency X->A exists in R, X is a superkey of R.
If we denote the autonumber as A and the key formed by the multiple fields as B, we get both that A->B and B->A. In both cases the functional dependency is non-trivial and both A and B are superkeys. The other functional dependencies are related to the fact that A is primary key, and so A->Y, but A is always a superkey.
Am I doing anything wrong? According to this I would think BCNF remains.

EDIT: Sorry! I posted before I could read the previous message which says exactly what I thought.