File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

index on username or on a surrogate key ?

 
Hussein Baghdadi
clojure forum advocate
Bartender
Posts: 3479
Clojure Mac Objective C
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi.
My application uses surrogate keys for my domain entities.
User class contains 'username' instance variable which it is guarnteed to be unique at the database level (through SQL constraint).
The application never use the id field to inquire any saved User object since it has no business meaning, but it uses username column alot.
So, whats better ?
creating index on id column ? or on username column ?
Yes, sounds like a stupid question (username is an obvious choice), but I'm not SQL guru.
Thanks.
 
steve souza
Ranch Hand
Posts: 862
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you use either column as a foreign key in another table? If so, the surrogate key could be used in joins and if you ever want to change a user name (say due to a typo) it would be easier to change. You can create unique indexes on both keys, regardless which is your primary key.
 
Joan Horta Tosas
Ranch Hand
Posts: 59
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If I recall correctly, most databases I've used automatically create indexes for primary keys, so the question would only be to create another index for username. My first response is yes, because as you said, you often access your data through this field. In the applications I use to do there are high volumes of data, so it wouldn't be difficult to find more than one index for the same table in our databases, based on the different criterias used to select that data.
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic