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