Separate table in case of surrogate key?

Question

I have been reading the heated debates on composite vs surrogate keys on Stack Overflow and other websites, and even though puzzled about some of the arguments given, I feel I am aware of the pros and cons of each.

I feel tempted to go for surrogate keys, but now I have another question. Let me explain my situation. I have a table consisting of 5 integers that make up a unique combination, and I have several tables referencing this entity. Right now all tables include the full 5 fields and all JOIN operations have to mention all 5 fields as a join criterium. Now I have added a surrogate key. But should I remove all 5 fields from all tables in favour of a surrogate foreign key that references the 'main' table?

I feel unsure about doing this, since each of the 5 fields separately is frequently used as a selection criterium in the other tables. I have indexes defined on them, in a certain natural order, so that the selection operations perform fast. If I migrate all 5 fields to a separate table, I'm afraid that when I want to select on let's say the first three of them, I'll have to define a JOIN and select from that, but no indexes will have been defined on that JOIN since it has just been generated, and I will suffer a performance loss.

Or should I somehow define a VIEW for each table, including the 5 keys, and index on that?

I feel confused, and after typing all this, inclined to stick with natural keys again. Help?

Answer 1

Unless you have a very specific goal for introducing a surrogate key, I'd probably leave it alone as long as it works for you.

Answer 2

I'd suggest to have one table with your 5 unique fields + surrogate PRIMARY KEY. In that table you can create indexes on each of the fields + unique constraint on 5 fields. Then, instead of having 5 fields in other tables you, will have just 1 column .