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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 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.
    69,045 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following