|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
I am trying to write a decimal value to a table, but it is only showing the integer part.
The definition for the column is :
Hours decimal 5 scale 3 allow nulls precision 5
What the debugger has is like "9.233", but the table states 9?
Thanx in advance!
The code for storing:
SqlCommand cmdSetHours = new SqlCommand(strUpdate,conn);
cmdSetHours.CommandType = CommandType.StoredProcedure;
cmdSetHours.Parameters.Add("@TotHours",SqlDbType.Decimal).Value = decTotalHours;
cmdSetHours.Parameters.Add("@EmpId",SqlDbType.Int).Value = m_intEmployeeID;
cmdSetHours.ExecuteNonQuery();
the procedure is :
(
@TotHours decimal,
@EmpId int
)
AS
UPDATE Friday
SET Hours = @TotHours
WHERE EmpID = @EmpId
RETURN
|
|
|
|
|
TheJudeDude wrote: I am trying to write a decimal value to a table, but it is only showing the integer part.
What is the type being used for the field? I'd look at the field and make sure it is not an integer.
Paul
|
|
|
|
|
The definition for the column is :
Hours decimal 5 scale 3 allow nulls precision 5
but, Thanx for the quick reply!
|
|
|
|
|
Have you looked at what is going on in the following line?
cmdSetHours.Parameters.Add("@TotHours",SqlDbType.Decimal).Value = decTotalHours;
I tried to replicate your problem from the database side, and couldn't  I would suspect something going on with the setup for the stored procedure.
I hope this helps some,
Paul
|
|
|
|
|
Well, in the debugger, the paramTotHours = #.## ( whatever that value may be)
Thanx again
-- modified at 19:10 Friday 10th February, 2006
Got it...didn't define the parameter correctly, as in
@TotHours decimal (5,2).
Thanks for the help again!
|
|
|
|
|
I need to find a way to change the background color of rows in my DataGridView if the row is marked as modified. Note that this is not the same as changing the background color of a cell when its value changes. The latter can be done easily through the CellValidating and CellValidated events, though this is completely unsatisfactory because the change in color gets lost when you sort a column. But I need the whole row's background color to change.
This seems to imply to me that I need to override the cell classes to override their Paint handlers. The difficulty with this is that in the Paint handler of a cell I can't find a way to get access to the cell's bound DataRow to tell if its RowState is modified. I can only see a way to get hold of the DataRow for the currently selected grid row, not for any row in general. If the grid has been sorted, then you cannot use the cell's grid row to index correctly into the dataset's table.
Any definitive help would be greatly appreciated.
-- modified at 12:12 Friday 10th February, 2006
|
|
|
|
|
can i use a subquery field to my main query.
i.e
select x.field1,y.field2
from tbl1 as x
where x.field3 not in (select y.field3 from tbl2 as y)
|
|
|
|
|
Yes, do be aware that a 'not in' query can be very lengthy if your tables are large.
|
|
|
|
|
thanks,
than what can i do ?
|
|
|
|
|
Sorry - didn't see your reference of y.field2 in the SELECT. Your original query will not work.
You can try a left join:
SELECT
x.field1,
y.field2
FROM
tbl1 x
LEFT JOIN
tbl2 y
ON (x.field3 = y.field3)
WHERE
y.field2 IS NULL
-- modified at 14:37 Friday 10th February, 2006
|
|
|
|
|
thanks, through it i solved my problem.
thanks again.
|
|
|
|
|
try this,may work.
select x.field1,y.field2
from tbl1 as x，tbl2 as y
where x.field3 != y.field3
青山隐隐水迢迢，秋尽江南草未凋。二十四桥明月夜，玉人何处教吹箫。
|
|
|
|
|
The above will work, although you could also just do a normal inner join and use an inequality instead of the usual equality join, instead of the above example with a cartesian product and a where clause.
SELECT x.field1, y.field2
FROM tbl1 as x
INNER JOIN tbl2 as y
ON x.field3 != y.field
|
|
|
|
|
Hi
what is the difference between joins & sub query?
what is the difference between having & compute?
tnx in advance
-ramani
|
|
|
|
|
|
Hi friends,
I have been developed one clien-server application using VB.NET and ADO.NET tech's. I installed my application and MS SQL Server 2000 in server system. but when I am going run the application at Client system it gives the error message as : "Server access denaied" and/or "Unable to connect the server system."
But my application is running perfectly after I installed the Database server at client system without current working datebase.
So I feel that it is required to install the MS SQL server on both server system and client systems. I am not interesting to do in this way.
Kindly, give me the proper solution for problem.
wish me as All The BEST.
|
|
|
|
|
There is no need to install MS SAL SERVER on client end.
Just provide the Server name at the client in your application
server name may be the IP address or the Server name.
|
|
|
|
|
Also install MDAC 2.7 on client machine.
It will definatly work.
Pankaj Kulkarni
|
|
|
|
|
It'll work so long as he built the connection string properly...
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
