I need advise on a table which is part of a development project I'm involved. A brief summary on the use of the table:
Data from multiple databases will be consolidated in this table for the purpose of full text searching. Full text index will be on a single column.
I'm trying to decide on the best candidate for the primary (and the clustering) key. There will be two important fields: ID and Type. ID is an identity field (which is also primary+clustering keys) on the source tables. Using just this field will result in a conflict because several source tables have the same ID (identity) field. Type is a look-up table. Id and Type together is unique.
I came up with the following scenarios:
- Id and Type as primary+clustering key.
- Create an identity field on the target table (primary+clustering) as well as a ID (synced with source's identity field) and Type.
- Create a separate Id field on the target table (clustering). Primary key will be source's ID and type.
What would be the best choice here?