Re: Question about databases and foreign keys

You'll actually want to have the User Id in the clocking table, not the other way around. User Id is the foreign key because it has a many to one relationship with the time logging. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Philip Thompson wrote: > > > I'll throw this out there though. When dealing with foreign keys and > multiple tables, remember to index appropriately. For example: > > Table users: > user_id int(10) primary key > -- other fields here -- > clock_id int(10) > > Table clocking: > clock_id int(10) primary key > clock_in int(10) > clock_out int(10) > > In table clocking, clock_id is already indexed because > it's primary. > Be sure to index clock_id in users so that when you join on the > two, > you'll have optimal speed! > > ALTER TABLE users ADD INDEX > (clock_id); > > And an example query... > > SELECT u.user_id, > c.clock_in, > c.clock_out FROM > users u > INNER JOIN clocking c ON > u.clock_id = > c.clock_id WHERE > (u.user_id = > '$user_id' AND c.clock_in > > '<sometime>' AND > c.clock_out < > '<someothertime>'); > > Something along those lines. I always find it useful to have unique > names throughout the database, so if you reference a name, you know > where it originated. Because of this, I can just look at users > and > determine that clock_id is a foreign key. > > Hope that helps a little! > > ~Philip >