Help with correct database/Table design

[Rapperdapper]

Hey all.

Maybe you could help me with my problem. Im still new at database design, and cant quite come to terms with my project and access way of doing things.

I have to keep a register of people who participate in projects. The projects can be of two different kinds. BUT (here comes the tricky part) The projects are being evaluated on three different indicators, with each one of these having 4 measurements, in the range of 4-0. That was a quick introduction. Now let me break it down in parts.

The people:

I have made a Uniqe identifier (Social Security number (PK))
First Name
Last Name
Department (This can be 4 different departsment) made a drop-down menu type.

The Projects:

Unique identifier (Project ID (PK))
Social Security number
Project Type
Start date
End date

Project type:

Unique identifier (TypeID (PK)
Project type (Cti / Regular)

How might i design this the best way, so i can combine the people with the projects there on. And which type.

There can only be one person, but he can be on many projects. These projects can vary in type. My problem is ensuring there connected proberly.

Furthermore, once the basic design is made i need to make evaluations based on their performance if they are on the projecttype "Cti".

here i need 3 x this:

Evaluations:

Objective (range 0-4)
Baseline reading (range 0-4)
Midway reading (range 0-4)
End reading (0-4)
Succes = Yes/NO (here i will do a End reading <= Objective formula).

Hmmm.. That was a rather long list, but i have sat working on this in three whole days, and im getting a little fed up with not knowing up-and-down.

Can you please help me?

 

[Rapperdapper]

I mean ofcourse "relation wise" i forgot to mention that sry.

 

[MarlaC]

A few questions:
First, can there be only one person per project? If not, you don't want SSN in your projects table (using SSN as ID is of questionable legality in this case, as well).
Second, do the employees refer to projects by database ID? If not, you probably want a field for project name or other identifier.
Third, how do your evaluations relate to the projects? You say you need 3x, but what does that mean? Each employee is evaluated 3 times during each CTI project?

 

[Rapperdapper]

A few questions:
First, can there be only one person per project? If not, you don't want SSN in your projects table (using SSN as ID is of questionable legality in this case, as well).
Second, do the employees refer to projects by database ID? If not, you probably want a field for project name or other identifier.
Third, how do your evaluations relate to the projects? You say you need 3x, but what does that mean? Each employee is evaluated 3 times during each CTI project?

Hey. Thx for the questions.

1. Yeah the project is unique to the person. So one person pr. project. But there is only two type of projects. So i "basically" wants to give the persons either an 1=CTi or 0=regular. And if they are an 1, they will be evaluated 3 times.
SSN is okay in this case, as it is an internal registry system. The "people" are signing contracts etc.

2. No they will type in Cti/regular - im not gonna do the datacollecting. Some employees or.

3. They are being measured in the different categories, but with the same measurement scale [Objective/Baseline/Midway/End - Rating]. So they have to like, combine to the one person.

Hope it answers your questions .