Atributes on the relationship 1:N or put them on entity (N)

[Misionero]

The empleoyees can working on only 1 project. But several empleoyees not work any project. The projects have one or more workers.
Hi. I wanted listen opinions about this model.

1º Question. Its better put the atributes worker's rol and begining date on the project, on the relationship or on the entity Employees.
2º If i prefer use atributes on the relationship, how is the next phase, wich tables i have to create in access?

3º exists differences between empleyees cardinality 0,1 or 1,1 when i pass the ER to access?

Thanks a lot for your knowledge.

 

[plog]

The empleoyees can working on only 1 project.

Are you sure? They may only work on 1 project at a time, but that doesn't mean they can't work on multiple projects during their employment. Employees can't switch projects? After a project ends, they don't go to a new project?

If an employee can really only go to one project then you would store the IDProject in the Employees table.

 

[gemma-the-husky]

@Misionero

I don't think you are getting this. This is two 1 to many relationships.

Workers table
Projects table
WorkedBy table

Workers 1 --- N WorkedBy N ---1 project.
There is NO direct relationship between the workers table and the projects table. (Well, there's a M:N correspondence, but we can't represent that in a relational database.)

Each worker is associated with multiple projects.
Each project has multiple workers.

Even if a worker is assigned to a single project only, when that project completes he will be assigned to a new project.

Even if you employ a worker and he only ever works on one project this is an unusual special case. In general your workers will be associated with more than one project.

You need to stop thinking about 1:1 relationships, because they are just mirages. All relationships are really 1 to many or many to many. A many to many will devolve into 2 x 1 to many. Therefore in a database all your relationships will be 1 to many.

If a worker starts on a project, I would add the date started to the row in the WorkedBy table. It's the date that the worker was assigned to the project (if that's the date you decide it should be). It's an attribute that's dependent on the worker and the project, so that's the only place it can be stored.

Having unassigned workers is nothing special. A worker does not have to be assigned to a project. You can find unassigned workers as they will have nulls against them if you create a query with a left join between Workers, and WorkedBy. (A standard unmatched query). An unattached worker is not a special case of a relationship. The CEO is unlikely to be attached to a project.

An orphan would be an entry in the WorkedBy table for a worker or project that doesn't exist. Asserting relations between table would prevent that ever arising, as Access would not then allow you to delete either a worker or a project where the worker or project is already in the WorkedBy table.

An unattached worker is not an orphan record.

 

[Misionero]

@Misionero

I don't think you are getting this. This is two 1 to many relationships.

Workers table
Projects table
WorkedBy table

Workers 1 --- N WorkedBy N ---1 project.
There is NO direct relationship between the workers table and the projects table. (Well, there's a M:N correspondence, but we can't represent that in a relational database.)

Each worker is associated with multiple projects.
Each project has multiple workers.

Even if a worker is assigned to a single project only, when that project completes he will be assigned to a new project.

Even if you employ a worker and he only ever works on one project this is an unusual special case. In general your workers will be associated with more than one project.

You need to stop thinking about 1:1 relationships, because they are just mirages. All relationships are really 1 to many or many to many. A many to many will devolve into 2 x 1 to many. Therefore in a database all your relationships will be 1 to many.

If a worker starts on a project, I would add the date started to the row in the WorkedBy table. It's the date that the worker was assigned to the project (if that's the date you decide it should be). It's an attribute that's dependent on the worker and the project, so that's the only place it can be stored.

Having unassigned workers is nothing special. A worker does not have to be assigned to a project. You can find unassigned workers as they will have nulls against them if you create a query with a left join between Workers, and WorkedBy. (A standard unmatched query)

I am trying it.. This world is very complicated for me ... Thanks a lot for your support. I am learning a lot of things with you and the others here...

 

[GaP42]

The empleoyees can working on only 1 project. But several empleoyees not work any project. The projects have one or more workers.

Like others who have responded this does appear a very odd constraint - to limit an employee to only working on one project. Perhaps the scope was not fully described / defined. There are at least two scenarios:
1. The model represents only the most recent / current allocation of employees to a project, and they may only be engaged in one project currently. The entity Projects might then be more appropriately named CurrentProjects. Note that in this data structure you would not be able to determine which employees have worked on projects (even current projects, where they have left the project after completing their assigned work). That the appears to be a poorly designed structure. No history of employees working on projects is retained.
2. If the intention of the model was to capture the engagement of the employee in each project they were assigned to, with the understanding that they may not be assigned to a project or assigned only to one project at one time, then the model is incomplete in terms of the attributes required to describe that assignment. Firstly, "Rol" (Role) indicates that the employee may be assigned the project in a Role. If the employee remains on the same project but assigned a new role, what would you expect to happen with regard to the "Work By" record? A new record or an update to the record? If the latter case, then the history of who was engaged in what role in a project is lost. The attribute "FirstDateOnProject" is insufficient to define the period the employee is assigned to the project in a designated role. The end date for that engagement is also needed to capture the probable intent.

Re your questions:
Under scenario 1: the (current)Project - Employee is 1:N and the attributes Rol(e) and FirstDateOnProject would then belong with the Employee
Under scenario 2: the Project - Employee relationship is M:N. Such a relationship can only be resolved by the creation of the "Worked By" entity (Period of engagement on a project) - this would hold Rol(e), FirstDateonProject (in that role) and the LastDateOnProject (in that role). Such a structure would also permit a relaxation of the constraint that the employee may can only be engaged in a maximum of one project at one time.
hence 3 tables are needed in Access, (and any other relational database)

I am not sure I understand the third question: what is the 1:1 you are referring to? The cardinality in Project to Worked By AND Employee to Worked By will both be 1:N. The Participation in both of these cases will be such that for any Worked By record there must be 1 and only one PROJECT and there must be one and only one EMPLOYEE, while the EMPLOYEE record to Worked By record would be described as an Employee may be assigned through one or more Worked By records. For a PROJECT - the relationship is may have one or more employees assigned through Work By records (A Project may exist initially without anyone assigned to it as it is established).

 

[Misionero]

Like others who have responded this does appear a very odd constraint - to limit an employee to only working on one project. Perhaps the scope was not fully described / defined. There are at least two scenarios:
1. The model represents only the most recent / current allocation of employees to a project, and they may only be engaged in one project currently. The entity Projects might then be more appropriately named CurrentProjects. Note that in this data structure you would not be able to determine which employees have worked on projects (even current projects, where they have left the project after completing their assigned work). That the appears to be a poorly designed structure. No history of employees working on projects is retained.
2. If the intention of the model was to capture the engagement of the employee in each project they were assigned to, with the understanding that they may not be assigned to a project or assigned only to one project at one time, then the model is incomplete in terms of the attributes required to describe that assignment. Firstly, "Rol" (Role) indicates that the employee may be assigned the project in a Role. If the employee remains on the same project but assigned a new role, what would you expect to happen with regard to the "Work By" record? A new record or an update to the record? If the latter case, then the history of who was engaged in what role in a project is lost. The attribute "FirstDateOnProject" is insufficient to define the period the employee is assigned to the project in a designated role. The end date for that engagement is also needed to capture the probable intent.

Re your questions:
Under scenario 1: the (current)Project - Employee is 1:N and the attributes Rol(e) and FirstDateOnProject would then belong with the Employee
Under scenario 2: the Project - Employee relationship is M:N. Such a relationship can only be resolved by the creation of the "Worked By" entity (Period of engagement on a project) - this would hold Rol(e), FirstDateonProject (in that role) and the LastDateOnProject (in that role). Such a structure would also permit a relaxation of the constraint that the employee may can only be engaged in a maximum of one project at one time.
hence 3 tables are needed in Access, (and any other relational database)

I am not sure I understand the third question: what is the 1:1 you are referring to? The cardinality in Project to Worked By AND Employee to Worked By will both be 1:N. The Participation in both of these cases will be such that for any Worked By record there must be 1 and only one PROJECT and there must be one and only one EMPLOYEE, while the EMPLOYEE record to Worked By record would be described as an Employee may be assigned through one or more Worked By records. For a PROJECT - the relationship is may have one or more employees assigned through Work By records (A Project may exist initially without anyone assigned to it as it is established).

Ok, it is true. The real relationshipo is N:M, because when the employee finish his job, he can begin new project. Thanks a lot.