8 Replies - 1333 Views - Last Post: 13 January 2013 - 05:12 PM

#1 Mohsin01  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 13-April 12

creating databse for hourly based workers.

Posted 10 January 2013 - 01:00 PM

hi guys,
i am trying to design sql database for hourly based working system for employee. where each employee will have his in out time for one month. i have made this in excel and now i am going to making this a complete software in c# with sql server. i came up with an idea that i will have to make table for each employee. so that i will have to use stored procedure for making tables for registering new employee.
Please tell me that is my idea right to make table for each user. if its not right then tell me some alternatives.
if its right than tell me how to create table on run time. i mean how to write stored procedure for creating tables on run time.

I am attaching my excel sheet picture so that you guys can understand me easily.

Is This A Good Question/Topic? 0
  • +

Replies To: creating databse for hourly based workers.

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9204
  • View blog
  • Posts: 34,577
  • Joined: 12-June 08

Re: creating databse for hourly based workers.

Posted 10 January 2013 - 01:02 PM

Quote

Please tell me that is my idea right to make table for each user. if its not right then tell me some alternatives.

A table for each user? That is not flexible and is a brittle concept.

Why not have a table called 'employee' and each row in said table is an employee at the company?

Then you have a table called 'hours' that would hold the employee's id and their clock in and clock out.. so each row is tied to a specific employee.
Was This Post Helpful? 0
  • +
  • -

#3 no2pencil  Icon User is online

  • Toubabo Koomi
  • member icon

Reputation: 5313
  • View blog
  • Posts: 27,211
  • Joined: 10-May 07

Re: creating databse for hourly based workers.

Posted 10 January 2013 - 02:21 PM

View PostMohsin01, on 10 January 2013 - 03:00 PM, said:

i came up with an idea that i will have to make table for each employee.
...
I am attaching my excel sheet picture so that you guys can understand me easily.

Did you make a spreadsheet for each employee? Then you should know that a table per each employee is overkill.

Apply a unique id, & you can verify everyone employee is unique. As modi123_1 then suggested, you can add employer as a column value. Thus you can link all employees to their rightful employer. Just as you have an employee table, you can have an employer table.

For each employer you have employees. For that employee you have hours.
Was This Post Helpful? 0
  • +
  • -

#4 Mohsin01  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 13-April 12

Re: creating databse for hourly based workers.

Posted 11 January 2013 - 12:49 PM

i think none of you got my point.i have made a full calender in sql as well.so, every employee's clock in and clock out will be entered in the cells. i only came up with each table for each employee idea. Please please Explain more so that i can understand.or give me some sql statements so that i will make database and understand it. I am attaching my excel sheet so that you guys can understand easily. this sheet is only for one person.

Thanks for taking look at my problem.
Was This Post Helpful? 0
  • +
  • -

#5 Mohsin01  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 13-April 12

Re: creating databse for hourly based workers.

Posted 11 January 2013 - 01:10 PM

i tried attaching sheet but it didnt. and i cant even edit my post. it says i dont have permission. so here is picture of my sheet.
Posted Image

Link of Picture
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9204
  • View blog
  • Posts: 34,577
  • Joined: 12-June 08

Re: creating databse for hourly based workers.

Posted 11 January 2013 - 01:14 PM

Quote

i only came up with each table for each employee idea. Please please Explain more so that i can understand.or give me some sql statements so that i will make database and understand it

No.. I think I understand what you are doing, but as I said you are doing it wrong. I've *MADE* employee time trackers.. I've *MAINTAINED* them as well.. Trust me.. one table per employee is the exact opposite of a best practice in this instance.

Side note - there is no sql to give you.. just redesign your table structure like we've said.
Was This Post Helpful? 0
  • +
  • -

#7 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5831
  • View blog
  • Posts: 12,683
  • Joined: 16-October 07

Re: creating databse for hourly based workers.

Posted 11 January 2013 - 01:30 PM

Yeah, I've seen stuff like that...

Now, right now, stop thinking like a spread sheet monkey.

What I see on the screen can be described with:
Employee:
	EmployeeId
	Fullname
	HourlyRate
	OTHourlyRate
	
EmployeeHours:
	EmployeeId
	DtBegin
	DtEnd



To give an idea of how what I'm seeing on the screen would be stored:
insert into EmployeeHours values
(1, '2013-01-09 07:00', '2013-01-09 12:00'),
(1, '2013-01-09 13:00', '2013-01-09 16:00'),
(1, '2013-01-10 07:00', '2013-01-10 12:00'),
(1, '2013-01-10 13:00', '2013-01-10 20:00')



Then it's all SQL to pull out reporting data as needed. Keep in mind, a database is for storing data. Duplicate data and layout is not stored. You make things pretty with report writers.
Was This Post Helpful? 0
  • +
  • -

#8 Mohsin01  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 13-April 12

Re: creating databse for hourly based workers.

Posted 13 January 2013 - 10:41 AM

Thank You Guys, That Helped me Alot. And i am now using tables differently as bhagvani said :). Thanks :)
P.s: Can you tell a little bit what is report writing? :)
Was This Post Helpful? 0
  • +
  • -

#9 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: creating databse for hourly based workers.

Posted 13 January 2013 - 05:12 PM

Report writing is the creation of a front-end interface that displays criteria-filtered data from the DB in a pretty format. Microsoft's Report Designer is an example of this, though there are other packages such as Crystal Reports. MS Access forms could also be considered a type of report writing interface.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1