Question Attendance database (1 Viewer)

faca · May 21, 2018

Hello!

I want to create database that would record the attendance.

Example:
I have project and this project has more programs. Person can join to one or more programs in one or more projects.

The intetion of database would be to see to which projects/programs this person joined..

I have 3 tables:

Person (PersonID, Person details...)
Program (ProgramID,PersonID, ProgramName, StartDate, EndDate)
Project (ProjectID, ProjectName)

Relationships:
Student -> (one-to-many) -> Program
Project -> (one-to-many) -> Program

I already tried to make this but everytime i want to add another student to same program (program starts with new id like this would be new program)

Any suggestion on this?Would anyone do this in different way?

Thanks

CJ_London · May 21, 2018

From what you have provided I don't see the relationship between projects and programs

isladogs · May 21, 2018

There have been several posts on this topic recently, both here and on other forums.
Is this a school or college assignment?

Suggest you start by reading the threads at the bottom of this page.

faca · May 21, 2018

ridders said:
There have been several posts on this topic recently, both here and on other forums.
Is this a school or college assignment?

Suggest you start by reading the threads at the bottom of this page.

I have school practice in company and they need this database..

faca · May 21, 2018

This is not an actual attedance to check whether person came to program every day or not .. only to check to which he joined

Example:
(At the end of the year ill print report)

Person 1 joined to:
- Project Job
-- Program How to get the job
-- Program What to do when i find job?

- Project Car
-- Program Which car brands do i know?
-- Program Which car brand should i buy?

-----------------------------------------------

Person 2 joined to:
- Project Car
-- Program Which car brand should i buy?

Or

If ill check Program Which car brand should i buy? ill get list of joined persons:

Person 1
Person 2
Person 3
Person 89
Person 90
Person 45

This is what i want to do

Pat Hartman · May 21, 2018

You need at least two more tables to provide the relationships. You must remove PersonID from the program table since a program is not related to a single individual. This relationship is implemented using a junction table. It is not clear to me the relationship between projects and programs nor how these are connected to people so here is my guess.

Person (PersonID, Person details...)
Program (ProgramID, ProgramName, StartDate, EndDate)
Project (ProjectID, ProjectName)

ProjectProgram (ProjectProgramID, ProjectID, ProgramID)
ProjectPersons (ProjectProgramID, PersonID, JoinDate)

faca · May 21, 2018

Pat Hartman said:
You need at least two more tables to provide the relationships. You must remove PersonID from the program table since a program is not related to a single individual. This relationship is implemented using a junction table. It is not clear to me the relationship between projects and programs nor how these are connected to people so here is my guess.

Person (PersonID, Person details...)
Program (ProgramID, ProgramName, StartDate, EndDate)
Project (ProjectID, ProjectName)

ProjectProgram (ProjectProgramID, ProjectID, ProgramID)
ProjectPersons (ProjectProgramID, PersonID, JoinDate)

"It is not clear to me the relationship between projects and programs nor how these are connected to people so here is my guess."

Example:
Project would be adult education class 1a

Programs would be math, english, chemistry

Person can join only to math or to all 3

Mark_ · May 21, 2018

So Project is parent, Program is child.
You need one more table.

PersonProgram

This table has copy of PersonID and copy of ProgramID.

When you do a query, for each person you look for ALL PersonProgram records with matching PersonID. You then use the ProgramID for each record to look up the Program information.

PersonProgram can have more fields if you need.

Pat Hartman · May 21, 2018

Given that,
ProjectPersons (ProjectProgramID, PersonID, JoinDate)
should be called
ProgramPersons (ProjectProgramID, PersonID, JoinDate)

but it still joins to ProjectProgram

The reason for ProgramPersons linking to the junction table is because you want the specific program within a specific project just in case a program can belong to multiple projects as you indicated.

I would not use person/program. You will find analysis much harder. If someone wants all the programs of a project, just join to all the programs. You could use a button on the form to accomplish that rather than making the person doing the data entry manually select each program but I would only connect people to programs and never to projects. Just have the button insert multiple records. If you want to find out what projects a person is connected to, get the information by following the program link.

There should never be two ways to get to the same information. You would end up having to deal with two situations - one where the person is linked to a project via a program and the other were the person is linked directly to the project but not to any programs of the project. Don't do it.

faca · May 23, 2018

Pat Hartman said:
Given that,
ProjectPersons (ProjectProgramID, PersonID, JoinDate)
should be called
ProgramPersons (ProjectProgramID, PersonID, JoinDate)

but it still joins to ProjectProgram

The reason for ProgramPersons linking to the junction table is because you want the specific program within a specific project just in case a program can belong to multiple projects as you indicated.

I would not use person/program. You will find analysis much harder. If someone wants all the programs of a project, just join to all the programs. You could use a button on the form to accomplish that rather than making the person doing the data entry manually select each program but I would only connect people to programs and never to projects. Just have the button insert multiple records. If you want to find out what projects a person is connected to, get the information by following the program link.

There should never be two ways to get to the same information. You would end up having to deal with two situations - one where the person is linked to a project via a program and the other were the person is linked directly to the project but not to any programs of the project. Don't do it.

I've tried something and it seems fine:

Person (PersonID, info...)
PersonProgram (ID, PersonID, ProgramID, Date_of_signup)
Program (ProgramID, Name, Start, End, ProjectID)
Project (ProjectID, Name)

Program doesn't belong to multiple projects only to one !

Pat Hartman · May 23, 2018

Program doesn't belong to multiple projects only to one

Then you are correct, you don't need the junction table. You link directly to the Project table.

Question Attendance database (1 Viewer)

faca

New member

CJ_London

Super Moderator

isladogs

MVP / VIP

faca

New member

faca

New member

Pat Hartman

Super Moderator

faca

New member

Mark_

Longboard on the internet

Pat Hartman

Super Moderator

faca

New member

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread