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.

My database is for an HR application with performance reviews and I am interested in whether I should index my foreign key.

There are two tables involved. The first is a ratings table with 2 columns: ID (Integer, Primary Key) and Description (Varchar). It will only have 4 rows (poor, average, above average and excellent) and will "never" change.

The second table is a review table. There will be 1 row for each employee every six months. So we are looking at thousands of entries and not millions. Each row has approximately 25 evaluation fields, which are foreign keys into the rating table. Examples would be typical questions where employee is rated on Completing Work on Time, Works well with others etc...

From what I've researched, there doesn't appear to be any real reason to create an index on each of those keys since I will never be adding and/or deleting from the ratings table. Are there any other considerations that I should take into account? Or am I worrying about nothing since by db is so small?

share|improve this question

2 Answers 2

It may still be useful to have indexes on the larger table to help the optimizer for specific SELECT queries, even if you will "never" change any of the values.

However, without a lot more knowledge of your system and the types of queries you will run, it's pretty difficult for any of us to able to tell you whether you should have indexes there or not. I'm afraid it's just something you're going to have to test.

Some background in this great article by Erin Stellato:

http://www.sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys

You also might consider avoiding the joins in some cases - when you are sure the values will never change, and eliminating the join can simplify the execution plan, you can consider just inline constants, e.g.

SELECT CASE RatingID
  WHEN 1 THEN 'poor'
  WHEN 2 THEN 'average'
  ... 
END
FROM dbo.BiggerTable
...

Also I suggest never naming something ID. If it's a RatingID, call it RatingID everywhere it exists in the model.

share|improve this answer
    
Thank you for your observations. Almost all of my queries will just be lookups to get the Rating Description. I won't really be doing queries where I am selecting from Review where a particular evaluation is poor. –  Luke May 22 '13 at 16:48
    
@Luke well it seems to me that the rating descriptions will always be in memory anyway. What would it hurt to have indexes there? As for the other, just because you don't envision such queries now, doesn't mean a new requirement won't come up tomorrow. –  Aaron Bertrand May 22 '13 at 17:03
    
Perhaps I'm missing something. I would have an index on the ratings table even though it will always be in memory. My issue is do I add an index for each of the evaluation fields for an additional 25 indexes? Those indexes would only seem to help for a query that is looking for a specific value(s) in a specific evaluation, i.e. list of all employees who scored excellent on Delivers work on time. It may be best not to add any and then add indexes as the needs arise. Thanks again. –  Luke May 22 '13 at 23:20
    
@Luke do you have 25 tables with a RatingID column? Or a table with 25 different RatingID-related columns? –  Aaron Bertrand May 22 '13 at 23:21
    
I have 25 different RatingID related columns. The manager evaluates each employee in 25 different aspects of their job performance. The employee ends up with a score from 25 (1=poor for all aspects) to 100 (4=excellent for all aspects). On one hand it seems overkill to create 25 an index for each aspect. On the other hand, adds/updates are infrequent 1 per employee per six months so performance for maintaining 25 indexes shouldn't be to bad. –  Luke May 23 '13 at 15:24

My database is for an HR application with performance reviews and I am interested in whether I should index my foreign key.

Generally speaking, if the column will appear in a search condition such as a WHERE clause or a JOIN predicate, then it should probably be indexed. It depends on the exact queries that access the table.

In a greenfield project, my approach is usually to not create any supplementary indexes until I know they're necessary by analyzing the query plan of new queries as they're written. Most of the time, index-backed constraints used to ensure data integrity are sufficient to serve most queries efficiently.

There are two tables involved. The first is a ratings table with 2 columns: ID (Integer, Primary Key) and Description (Varchar). It will only have 4 rows (poor, average, above average and excellent) and will "never" change.

Unless there are mitigating circumstances, I suggest using a tinyint for the primary key of this table, as this will save you a bunch of storage space in the much larger table(s) that reference this table. (Maybe not much in absolute terms for this project, but in general, this is a good idea.)

The second table is a review table. There will be 1 row for each employee every six months. So we are looking at thousands of entries and not millions. Each row has approximately 25 evaluation fields, which are foreign keys into the rating table. Examples would be typical questions where employee is rated on Completing Work on Time, Works well with others etc...

I would suggest there is some work to be done to normalize this design. From what you've said, I can identify at least 5 tables involved:

Employees
Questions
Ratings
EmployeeEvaluations
EmployeeEvaluationQuestionRatings

The normalization work should be done first, and I think you'll find the question about indexing and performance largely goes away -- or becomes much more obvious -- after adding appropriate table constraints.

share|improve this answer
    
Thanks for your observations. –  Luke May 22 '13 at 23:14
    
@Luke: You're welcome. Let me know if you have any further questions about this. –  Jon Seigel May 23 '13 at 14:05

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.