Question about databases and foreign keys

Hi everyone,

I just wanted to make sure that I am not making something more
complicated then it has to be.

I am working on a time clock application to use at my company, and so
far, I have a login table, and with a foreign key that links to the
time table. The thinking being, that when someone logs in, they get
assigned there employee number, and then it adds a new record for that
employee ID.

All I will be doing with the info, is grabbing the total amount of
time they were clocked in for each day, and then totaling for the week.

I'm planning on using timestamps to store the actual time, hopefully
making the math part easier :)

Do I need the foreign key and the 2 tables? Or should I just add the
column's into the database since it will be for a small company?

Ideas? Suggestions?

Thanks!


--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
11287 James St
Holland, MI 49424
www.raoset.com
japruim@raoset.com





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

Re: Question about databases and foreign keys

Use 2 tables. You never know what the app might grow into and you
should do it right the first time.

Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com



Jason Pruim wrote:
> Hi everyone,
>
> I just wanted to make sure that I am not making something more
> complicated then it has to be.
>
> I am working on a time clock application to use at my company, and so
> far, I have a login table, and with a foreign key that links to the
> time table. The thinking being, that when someone logs in, they get
> assigned there employee number, and then it adds a new record for that
> employee ID.
>
> All I will be doing with the info, is grabbing the total amount of
> time they were clocked in for each day, and then totaling for the week.
>
> I'm planning on using timestamps to store the actual time, hopefully
> making the math part easier :)
>
> Do I need the foreign key and the 2 tables? Or should I just add the
> column's into the database since it will be for a small company?
>
> Ideas? Suggestions?
>
> Thanks!
>
>
> --
>
> Jason Pruim
> Raoset Inc.
> Technology Manager
> MQC Specialist
> 11287 James St
> Holland, MI 49424
> www.raoset.com
> japruim@raoset.com
>
>
>
>
>

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

Re: Question about databases and foreign keys

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!


--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
11287 James St
Holland, MI 49424
www.raoset.com
japruim@raoset.com





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

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` > '' AND
`c`.`clock_out` < '');

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

Re: Question about databases and foreign keys

>
> 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!
>
>
> --
>
> Jason Pruim
> Raoset Inc.
> Technology Manager
> MQC Specialist
> 11287 James St
> Holland, MI 49424
> www.raoset.com
> japruim@raoset.com
>
>
>
>
>
This for mysql only but it can give some info you may find useful.

This is a very enlightening article for starters in order to see the sql
part.

http://articles.techrepublic.com.com/5100-10878_11-6035435.h tml

The mysql manual could also give you an idea on that.

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-co nstraints.html

--
Thodoris


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

Re: Question about databases and foreign keys

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



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` > '' AND
> `c`.`clock_out` < '');
>
> 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

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` > '' AND
>> `c`.`clock_out` < '');
>>
>> 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