designing indexes

25 pts.
Tags:
Database design
Indexes
JOIN statement
Primary keys
I have a question regarding database design. Lets assume that there is a table with 10 columns. I don't have any single column which I can choose as candidate for Primary key. Though, combination of 5 columns can uniquely identify records. Here, I created an identity column as a PK and added UNIQUE constraint on those 5 columns. I was thinking about creating non clustered index on these 5 columns. These 5 columns are common to many tables and are ,hence, used while in JOIN & WHERE clause. So, from performance perspective, it makes sense to have clustered index on columns which are widely used in Join clause. Joining on columns of clustered index is faster than that of non clustered index. So, I am not being able to understand which option is better... clustered index on PK column OR Clustered Index on 5 columns. I feel if I create a clustered Index on PK coulmn then I should also create a lookup table which contains PK column values each corresponding to comnbination of values from those 5 columns. In this way, pk column can also be used in joining. In such case, I will need lots of lookup table containing values for Identity key column(PK values) corresponding to combination of those 5 or more columns. One more thing: Identity column is like surrogate column. It doesn't contains data which makes sense to any user instantly. Please Advise, Thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

It sounds to me like you should remove the identity column from the table, and configure the natural key (the 5 columns) as the actual primary key. It seams to me based on the information provided that you aren’t making use of the primary key.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    I agree with Mrdenny. I would think that the identity column is not needed, and you should build your primary key with those 5 columns, and this way, an additional index might not be needed.
    83,215 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: