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."
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
