Question on Indexing & PRIMARY key

[Anon]

Suppose I have 2 tables: users and score

In 'users' there is a PRIMARY key called 'user_id'. In 'score' there is a 'score' column and a 'user_id' column.There will be multiple scores for each 'user_id'. My question is whether I should make 'user_id' in the score table an index. Or is there something else I should do?

Thanks,
Doug

[toma42]

what back end are you using?

If you are using a good one like postgresql then you should create score with user_id as a reference to users.user_id.

If you're using a database that doesn't support referential integrity, create an index for user_id, usually user_id_idx (user_id)

Personally I prefer to have all foriegn keys named ref_othertablename so I know it's a foreign key (from ref_) and I know what table it goes to.

Tom

[Anon]

I'm using MySQL which doesn't support foreign keys. So I will make user_id an index in the score table. I'm using phpMyAdmin which I don't believe allows me to rename the index to something else.

Thanks for the info.
Doug