one to many relationship based on date

Question

I wrote an windows form app in c# that makes a presence/delay log for employees and now I'm trying to add a feature to that app which does the following :

Takes the log of that day (who is present and who came late to work) from it's database and assign it to the date of that day, so i'd be able to view one each separate day who was present and who came late to work.

To make it more clear :

Workers table : where 1 means the employee is present or delayed

ID--name----------presence-----delay

1--sam--------------1----------0---

2--jack-------------0----------0---

3--ted--------------1----------1---

Date table :

Day---------------present----delay-----absent

14/10/2012---------sam--------ted-------jack

------------------------ted-----------------------

and so on, i hope i made my idea clear.

how will the second table look like and how will the relation be ?

how will i view the result like the one in the date table ?

score 1 · Accepted Answer · answered Oct 14 '14 at 14:41

--Person--          --Presence--       --Delay--
ID                  ID                 ID
Name                Date               Date
// other info       PersonID           PersonID
                    IsPresent

First of all, you should hold Person table separately and use it's ID in other tables. I strongly recommend reading about data redundancy and database normalization. PersonID in the other tables refer to ID in Person table.

I think Presence should be logged for each day. Delay should be logged if only the Person shown up late that day.

score 0 · Answer 2 · answered Oct 14 '14 at 14:41

you may want to do something like this, may not be 100% but should get you going in the right direction. Run into any other questions let me know will help as i can

table: Employees { Id, Name } table: Logs {EmployeeId, DateCreated, OnTime (bit false if late)}

select Name
Status = CASE WHEN OnTime = 1 THEN "On Time" 
         WHEN OnTime is null THEN "Absent"
         ELSE "Late"
from Employees as e
left join Logs as l
    on e.Id = l.EmployeeId
Where l.DateCreated = @aDate
or l.DateCreated is null

to show the data the way you are looking to would then become a task for the report engine or a pivot table depending on the requirements

one to many relationship based on date

2 Answers2