Index & FK Relationship On LookUp Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Index & FK Relationship On LookUp Tables

Author

Topic

wackoyacky
Starting Member

25 Posts

Posted - 2007-06-11 : 12:42:55

Hi!

It's been always said that it is best to put index on commonly joined fields in the table. But putting too much index on the table would cause the table to be slow on insert and update.

My question is, how do you deal with your fields that uses look up tables? Like for example for these fields

- CountryID(smallint)
- CreatedBy(int)
- ModifiedBy(int)
- Status(tinyint)

Those fields don't come a big part in the table, though when I query the table I always join them with their respective primary table to get their respective text value. Do I still need to put Index & FK relationships to these fields?

What fields are normally good candidates for index or fk relationships?

Thanks,
Enzo

chrisrock
Starting Member

12 Posts

Posted - 2007-06-12 : 08:47:36

Putting too many indexes on your table will slow down the inserts and updates BUT you need to weigh the pros and cons of this.

What needs to be faster? The inserts and updates or the selects? Is this a reporting database? If so, I'm sure the selects are more important.

Do you have a sql statement that I can take a look at?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-12 : 11:26:39

Need fkeys if you like to enforce data integrity.

wackoyacky
Starting Member

25 Posts

Posted - 2007-06-12 : 21:06:13

@chrisrock
You enlighten me with your inputs :) I guess you're right I have to weigh down which one is most commonly used, insert/update or select. Well I guess most of the time SELECT statements were most commonly executed rather than the insert update, so I guess it's worth putting the indexes.

My sample query would be something like

SELECT FirstName, LastName .... FROM Candidate A
INNER JOIN Country B ON A.CountryID = B.CountryID
INNER JOIN Status C ON A.StatusID = C.StatusID
INNER JOIN Users D ON A.CreatedBy = D.UserID
INNER JOIN Users E ON A.ModifiedBy = E.UserID
INNER JOIN Table1 F ON A.T1 = F.T1
INNER JOIN Table2 G ON F.T2 = G.T2
LEFT JOIN Table3 H ON A.T3 = H.T3

The query is something like that and the joins goes on if there are new fields that use look up tables.

@rmiao
Thanks for reminding me. I almost forgot the main purpose of foreign keys :) I guess for those not so important fields, I need not to create fkeys. But would there be any problem if a table has too many fk relationships? Or the Primary table having so many tables related to it?

Thanks guys for you inputs

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-12 : 21:24:58

There is no performance impact to have multiple fkeys in the table.

Kristen
Test

22859 Posts

Posted - 2007-06-13 : 03:14:15

FKey will prevent you accidentally saving a Child record that has no Parent. In my experience one of the main benefits of this is to find program bugs! Your program logic is sure that the Parent exists, but there is actually a bug ... and it doesn't exist.

Other major benefit is during Data Import, to ensure referential integrity is maintained.

I'm not sure that an index on CreatedBy(int) is going to help. However, you definitely need an index on UserID in your Users table - but that is probably the Primary key, so shouldn't be a problem there.

An index on CreatedBy(int) is probably not a big deal. I presume this data never changes, so there is NO performance penalty with Update [NOTE: you MUST have a Clustered Index, otherwise there WILL be a performance penalty for ALL indexes on an update that has to move the record - e.g. because it gets bigger]. However, ModifiedBy(int) presumably changes almost every time the record is Updated, so this will require a change to the index as well as the record.

If you do a query "WHERE ModifiedBy = @MyUserID" then an index on ModifiedBy will help, of course, so if that query is sufficiently frequent it makes an index more worthwhile.

I treat these things on a case-by-case basis. Often in a JOIN the column(s) in the Child record are often Criteria for queries too. Other than that I look at the query plan, create an index, re-check the query plan, and then drop the index if it didn't make any/sufficient difference.

Kristen

wackoyacky
Starting Member

25 Posts

Posted - 2007-06-14 : 12:20:46

Thanks Kris for more explanation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources