Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Looking for some expert views on this guys - I'm not a DBA by trade so would appreciate any advice. Comments on the rest of the schema aren't required as it is purely fabricated to illustrate my question.

Basically I'm working on a new feature for an existing system and am powerless to change the existing schema. So let's say that there currently is a Users table which has a primary key UserID that is of type uniqueidentifier. I want to create a new table called Alerts - related to the Users table in a one-to-many relationship (one User can have many Alerts). Alerts will be regularly queried by this foreign key. I will expect to index this field for performance.

My question is - from what I understand about Indexing strategies, indexing a uniqueidentifier column is (delicately put) not a good idea - so would it be a better idea to have an intermediary table to map these uniqueidentifier keys into a better indexing candidate like an integer?

Users

  • UserID uniqueidentifier {PK}
  • Username varchar(50)

Alerts

  • AlertID int {PK}
  • UserID uniqueidentifier {FK}
  • Message varchar(250)
  • Timestamp datetime

OR

Users

  • UserID uniqueidentifier {PK}
  • Username varchar(50)

UserMap

  • UserKey int identity(1,1) {PK}
  • UserID uniqueidentifier {FK}

Alerts

  • AlertID int identity(1,1) {PK}
  • UserKey int {FK}
  • Message varchar(250)
  • Timestamp datetime
share|improve this question
    
Did you mean to have UserKey in Alerts instead of UserID? –  Mark Storey-Smith Aug 31 '11 at 11:34
    
Also, do you really mean for UserKey to be VARCHAR(50)? –  Mark Storey-Smith Aug 31 '11 at 11:35
    
If you're going to equate a GUID to an INT in some mapping table, why not just do that in the primary table and drop the GUID altogether? –  Aaron Bertrand Aug 31 '11 at 12:51
    
Also one alternative you didn't consider is just adding an INT column to the Users table (populated the same way you would have populated UserMap). –  Aaron Bertrand Aug 31 '11 at 12:52
    
Have edited the second option to be what I meant originally.. not sure how it got mangled in translation! –  theAgz Aug 31 '11 at 13:10
show 2 more comments

3 Answers

up vote 5 down vote accepted

Ok, I am making a lot of assumptions (INT instead of VARCHAR(50) being one of them) with this answer, so feel free to correct me if needed. The problem with option B is that it introduces a new join to relate Users to Alerts without any real added benefit. If joining on the UserID, it is best to index the UserID, so you can utilize seeks for your joins.

For Option A, UserID will be the clustering key (index key for the clustered index) on the Users table. UserID will be a nonclustered index key on Alerts table. This will cost 16 bytes per Alert.

For Option B, UserID will be the clustering key on the Users table. UserId will probably be the clustering key in UserMap too, to make joining more efficient. UserKey (assuming this is an INT) would then be a nonclustered index key on the Alerts table. This will cost 4 bytes per Alert. And 20 bytes per UserMap.

Looking at the big picture, one relationship, for Option A, costs 16 bytes of storage, and involves 1 join operation. Whereas, one relationship, for Option B, costs 24 bytes of storage, and involves 2 join operations.

Furthermore, there are a possibility of 340,282,366,920,938,000,000,000,000,000,000,000,000 uniqueidentifiers and only 4,294,967,296 INTs. Implementing a uniqueidentifier to INT map for a this type of relationship could cause unexpected results when you start reusing INTs.

The only reason for creating this type map table, is if you plan on creating a Many to Many relationship between Users and Alerts.

Taking all of this into consideration, I would recommend Option A.

I hope this helps,

Matt

share|improve this answer
2  
+1 for "The only reason ... is if you plan on creating a Many to Many ..." –  Jack Douglas Aug 31 '11 at 13:16
add comment

Assuming your intended option B is to have UserKey in Alerts and for UserKey to be an INT rather than VARCHAR(50)... you might save some space and improve performance marginally if you expected there to be a very large number of alerts per user. But it's going to be minimal so I'd probably stick with option A.

The inefficiencies of GUIDs are due to their size (16 byte) compared to an INT (4 byte) and the fragmentation they inevitably lead too, unless sequential. This is magnified where a GUID is used as a clustered index key (as is the case for your User table?) because all non-clustered indexes contain the clustered index key. Kimberley Tripps series of articles on the topic are good grounding for understanding this.

Option B

  • Requires 20 bytes per user in UserMap
  • Saves 12 bytes per user in Alerts

Option A

  • Costs 12 extra bytes per alert in Alerts, compared to an INT UserKey in Option B

Caveat: Also assuming that the typical usage pattern would be to find alerts for a specified user, not results for a group of users.

share|improve this answer
add comment

Could you simply add an identity column to the Users table (not making it the primary key or clustering key) and put an unique non-clustered index on it, then you can join to it from the Alerts table (and put a foreign key in place)?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.