Index & FK Relationship On LookUp Tables

Question

Index & FK Relationship On LookUp Tables

wackoyacky

SSC Eights!

Points: 832
More actions
June 11, 2007 at 6:43 pm

#174554

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

CountryID and Status Yes.

According to many gurus and Sox, auditing data should be kept completely separate from the base data, and base database. So the 2 modified fields should be in another table. And yes they should also be indexed in that table (or even this one if you decide to keep the actual design).

wackoyacky SSC Eights! Points: 832 More actions · Answer 2

OIC. Thanks for the input.

For the modified fields, those are just high level auditing, I have a separate one that audits in detail.

But what if a single table uses a number of look up tables, does it mean that I have to index all of them? Wouldn't the insert & update will suffer?

I don't have huge amount of data to make the necessary test whether it has impact or not.

Thanks.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

That's one of those it depends questions. If you have 99% inserts vs 1% select, then you may drop a few indexes... Especially for auditing since theorically, only programmer will be looking at those. Both otherwise you should index them all. My 99 to 1 is a little extreme and can be applied at lower repartitions, but this is one of those questions where you need to know what the system will be used for and how the server reacts with each set of indexes.

wackoyacky SSC Eights! Points: 832 More actions · Answer 4

Well I think it make sense now, I should weigh whether which more is more heavily used, insert/update or select?

I guess I should go with indexing, and as for the foreign keys, I guess the primary purpose of that one is if I want to enforce data integrity on the backend level.

For the high level auditing, I do show to UI who and when it was created and who & when it was last modified as well.

Thanks ninja for your inputs!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

ok so you show the usernames of created and modified. That means you need to index the pk of the users table so that the joins made on that table are fast. You may also consider pinning the table in memory to make sure you always have superfast access to it (depending on table size and available memory).

This also means that you will most likely do very little searches on the base tables to see what users did what with th. So the index on that table may be uneeded (if you dont, or rarely search by that column only : without searching for the pk column for exemple).