On Sep 15, 2008, at 10:03 AM, Jason Pruim wrote:
On Sep 15, 2008, at 10:59 AM, Micah Gersten wrote:
Use 2 tables. You never know what the app might grow into and you
should do it right the first time.
That's what I was thinking too... Just wanted to hear it from
someone else... NOW I get to learn about foreign keys and how to
update things with them!
So if anyone knows of a good resource I'd appreciate it... Until
then, I'm off to search the web and figure this stuff out!
I'm sure Google will probably be your best resource for what you need
to find out. ;)
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
