Re: Question about databases and foreign keys

On Sep 15, 2008, at 2:12 PM, Micah Gersten wrote:

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

Yes, I agree with that. That was just a quick example that I whipped up (and obviously didn't think enough about). ;) So, follow Micah's advice, people. =D ~Philip

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

--PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

"innerHTML is a string. The DOM is not a string, it's a hierarchal object structure. Shoving a string into an object is impure and similar to wrapping a spaghetti noodle around an orange and calling it lunch."