Database design
-
Hi all (again!) I am new to sql database design, and am in the process of learning it all really quick....Our old POS program was written in a DOS 4G dbms, which is completely different from what I am learning. I have a quesion about database design. I opted to start with a timesheet program that I wrote originally in this other stated ide. I am looking to crate a database for a timesheet. I have thought about this for a while and came up with a few idea's, but would like some input, being new to this and all. I need to store values of the time clocked in and clocked out, in weekly intervals. Here it goes: Employee Info Table Includes employee number, name, password, security level and then a table for each day of the week, or a table with a timein and timeout for each week, with of course the employee id, or create a table at the time the employee first signs in with a timein and time out for each day of the week...each day also needs to be tagged with if the hours are regular work hours, vacation, jury duty, holiday, sick, etc. Thats basiclly the info I need stored. There is no need for an 'approved by' column, or things of that sort. A pretty basic program that gets the job done. Any advice/help is greatly appreciated! Thanx in advance!
-
Hi all (again!) I am new to sql database design, and am in the process of learning it all really quick....Our old POS program was written in a DOS 4G dbms, which is completely different from what I am learning. I have a quesion about database design. I opted to start with a timesheet program that I wrote originally in this other stated ide. I am looking to crate a database for a timesheet. I have thought about this for a while and came up with a few idea's, but would like some input, being new to this and all. I need to store values of the time clocked in and clocked out, in weekly intervals. Here it goes: Employee Info Table Includes employee number, name, password, security level and then a table for each day of the week, or a table with a timein and timeout for each week, with of course the employee id, or create a table at the time the employee first signs in with a timein and time out for each day of the week...each day also needs to be tagged with if the hours are regular work hours, vacation, jury duty, holiday, sick, etc. Thats basiclly the info I need stored. There is no need for an 'approved by' column, or things of that sort. A pretty basic program that gets the job done. Any advice/help is greatly appreciated! Thanx in advance!
Database design is not an easy topic to just jump into. I could attempt to provide a schema for what you are asking, but it would be a temporary fix at best. Better for you to learn to "fish" as the saying goes. In general, the goal of a database design is to prevent data redundancy and to organize your data into a logical fashion. Much beyond that would require far more than a simple forum post. I would suggest finding yourself a good database design book (I'm afraid I can't suggest one, perhaps another poster will be better able to) and perhaps taking advantage of Google to see what you can see. About.Com Database Design has quite a few interesting looking guides. After leading off with the above, for your specific problem I would say you're on the right track, an employee table tracking employee specific information. For your timesheet tracking, most decisions would require a more in depth knowledge of the domain (which you have), but to hazard a guess I would say something along the lines of a Timesheet table with EmployeeId, Date, ClockInTime, ClockOutTime, and WorkType. Of course, this all depends on how picky you are about data constraints, but based on what you've said it is what I would suggest. While the idea of separate tables for each day may seem more prudent, you need to think in terms of the data you will have to retrieve. Something as simple as retrieving an employee's total hours for the year would be costly, involving joining eight different tables. I hope I came across at least reasonably clear, and I wish you the best of luck in learning database design.
-
Database design is not an easy topic to just jump into. I could attempt to provide a schema for what you are asking, but it would be a temporary fix at best. Better for you to learn to "fish" as the saying goes. In general, the goal of a database design is to prevent data redundancy and to organize your data into a logical fashion. Much beyond that would require far more than a simple forum post. I would suggest finding yourself a good database design book (I'm afraid I can't suggest one, perhaps another poster will be better able to) and perhaps taking advantage of Google to see what you can see. About.Com Database Design has quite a few interesting looking guides. After leading off with the above, for your specific problem I would say you're on the right track, an employee table tracking employee specific information. For your timesheet tracking, most decisions would require a more in depth knowledge of the domain (which you have), but to hazard a guess I would say something along the lines of a Timesheet table with EmployeeId, Date, ClockInTime, ClockOutTime, and WorkType. Of course, this all depends on how picky you are about data constraints, but based on what you've said it is what I would suggest. While the idea of separate tables for each day may seem more prudent, you need to think in terms of the data you will have to retrieve. Something as simple as retrieving an employee's total hours for the year would be costly, involving joining eight different tables. I hope I came across at least reasonably clear, and I wish you the best of luck in learning database design.
Thanx for the input. Here is the solution I came up with. Table 1 - Employee Info Table 2 - Timecard - Employee ID, Time in, Time Out, Date, Work Type Table 3 - Table Name - Employee ID, Timecard Table name and for the programming solution, create a table everytime an employee signs in for the first time, and give that table a unique name, store that name in the table name table, along with the Employee ID. Does that sound feasable? Thanx again! Common sense...the least common of the senses used Jude
-
Thanx for the input. Here is the solution I came up with. Table 1 - Employee Info Table 2 - Timecard - Employee ID, Time in, Time Out, Date, Work Type Table 3 - Table Name - Employee ID, Timecard Table name and for the programming solution, create a table everytime an employee signs in for the first time, and give that table a unique name, store that name in the table name table, along with the Employee ID. Does that sound feasable? Thanx again! Common sense...the least common of the senses used Jude
You only need two tables. 1. Employee info : ID, Name , etc. 2. Timecard data: ID, TimeIn, TimeOut, Date, Work Type. All clock in records for all employees go in the 2nd table. A new record is added each ime a any employee cloicks in, clocking out updates the timeOut record (actually I would make a dateIn and date out field to allow for clock-out on different day than clock in. There is no need for a per employee table, since the per employee DATA can be extracted and summarized by queries any time (for example: "select TimeIn ,TimeOut, WorkType from TimeCard where IX = xxx and date between zzz and www") The goall\ is to store the data in a fashion that minimizes data duplication and data storage size while also minimizing complexity. A timecard table per employee makes no sense to me , and cerainly adds complexity. It would, on the other hand, make sense to have a "work Types" table, with WokTypeId,WorkTypeName as fields, so the id could be the only thing stored in the TimeCard table, thus minimizing data storage required, ad insuring data consistency (you can change the spelling or name used for work type, without breaking existing data, since the WorkID would remain the same). It also provides a quick place to build selection lists of the legal work types for your UI. Absolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burke
-
You only need two tables. 1. Employee info : ID, Name , etc. 2. Timecard data: ID, TimeIn, TimeOut, Date, Work Type. All clock in records for all employees go in the 2nd table. A new record is added each ime a any employee cloicks in, clocking out updates the timeOut record (actually I would make a dateIn and date out field to allow for clock-out on different day than clock in. There is no need for a per employee table, since the per employee DATA can be extracted and summarized by queries any time (for example: "select TimeIn ,TimeOut, WorkType from TimeCard where IX = xxx and date between zzz and www") The goall\ is to store the data in a fashion that minimizes data duplication and data storage size while also minimizing complexity. A timecard table per employee makes no sense to me , and cerainly adds complexity. It would, on the other hand, make sense to have a "work Types" table, with WokTypeId,WorkTypeName as fields, so the id could be the only thing stored in the TimeCard table, thus minimizing data storage required, ad insuring data consistency (you can change the spelling or name used for work type, without breaking existing data, since the WorkID would remain the same). It also provides a quick place to build selection lists of the legal work types for your UI. Absolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burke
Exactly as he said. Based on what you've already said, you would probaly want to extract your Security Level for your employees to another table as well, for the same reasons as the work type.
-
Exactly as he said. Based on what you've already said, you would probaly want to extract your Security Level for your employees to another table as well, for the same reasons as the work type.
Thank a whole lot for your input. I got it together and working. Very well appreciated! Common sense...the least common of the senses used Jude