[RESOLVED] Database Design Help

zipp

Hello. I am working on a project that could best be described as a "time card" system. It is so employees can clock in/out and submit their time cards after a couple of weeks. It is also going to have some admin features like manage employees, edit cards, view unsent time cards, view past time cards, mark time cards as paid, and some other little things.

I am running into a problem trying to get my PHP scripts to work correctly with my database design. I am wondering if there is a better way to do things.

Right now I have 2 tables. 1 for login info, and 1 for time cards. There will be about 20 employees. Do you think it would be better to have a table for each one?

table1
unique id
what time card they are on (will increase when they "submit" their card)
login name
password
what position they hold at the company
if they are an admin or not

table 2
unique id
employee id
time card id
punch in
punch out
paid vacation time (admin will set this manually)
any expenses they need to add to the card
any drive time they had
if the card was submitted
if the card was paid

The problem I am having is that there will end up being 20 entries per "time card id" and I cant seem to get my PHP scripts to separate them by "employee id".

How would you go about doing this type of project?

I would really appreciate any help you can offer. I can supply more details if it would help.

tomhath

I'm not sure I completely understand the problem. Why is every employee id linked to every time card id?

For starters, I'd split the second table into two new tables. One that defines a time card, without an employee id, and a third table that connects an employee id to a time card id. Also, move the "what timecard they're on" from table one to the new third table.

So you have:

Employee (login, etc. no time card information)
TimeCard (time card id, punch in, etc, but no employee id)
Employee_Timecard (employee id, timecard id, status - current, submitted, etc)

zipp

Thank you! You nailed the question with a hammer. That is exactly what I was looking for. I figured out how to make it work with that ugly database layout I mentioned above, but I am going to redo it with the one you suggested! Thank you 🙂

tomhath

That's called "normalization". Pretty standard design approach, worth investing some time to read about it.

sun2009

I am using
-Windows XP (Professional)
-php 5
-Microsoft Access 2003
-Apache Server 2.2

I have done ODBC setting, system DSN setting selecting the database in Microsoft Access
Driver, now I dont know how to connect this database with the php code...I have searched net but no success everytime it gives new error, I am new php learner. Please help