Re: Question about databases and foreign keys

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