Help with Intersection Tables

[SpencerBE]

Hi-

I am developing a database for my company and I am having a hard time setting it up correctly. Basically, there will be a user who logs in to complete reviews about employees who are on a project.

I have a form which has a listbox. The listbox loads all the projects the employee is assigned to. There will be ONE review per employee per project they are assigned. Right now, when the user dbl clicks the project listed in the box, it will load the evaluation form. Now, this is what I want to happen. I want the user to dbl click the listbox. When this happens, I want it to check if there is a review already completed. If there is, then load that record into the evaluation form. If there is not a review, I want it to prompt the user to complete one. "Do you wish to create a new review?" (Yes/No) If yes, load a blank evaluation form, create a new record in the Review table to be filled out.

I can handle the VBA code for most of the form stuff, however I am having a hard time arranging my tables correctly. I have attached my current relationships.

Any help would be greatly appreciated or suggestion for approach. If you need more information, please let me know. Thank you so much.

S

 

[Colin@Toyota]

Hi,

Just looking at your relationships for a few seconds, I have found what I believe to be a couple problems...

First, from the way the tables are setup, it looks as if one review can have many projects? is this right? or should each project have many reviews, at different intervals say (monthly, semi-annually, etc.)? If this is the case, you may wnat to consider not using the review_ID as a foreign key in the proj_int table. Instead, you should consider assigning a unique ID to each combination of employee_ID and project_ID.

In fact, the more I look at it, I think your proj_int table is redundant. Use a foreign key for employee_ID in the projects table. Use the review_ID field as a unique ID for each combination of project and reviewer. Also, your reviewed_by field should probably be a lookup to a reviewer table if there are going to be repeating entries.

I think a good place to start would be reading up on database normalization. There are plenty of threads on this forum, if you search for em. Alternatively, you can check out this link.

Or this one.

 

[Jurjen]

intersection table

From what i understand there are 3 main entities involved:
Project, Employee and Review

From your description i conclude that there is a many to many (N:N) relationship between Projects and Employees:

Basically, there will be a user who logs in to complete reviews about employees who are on a project. (...)
The listbox loads all the projects the employee is assigned to. There will be ONE review per employee per project they are assigned.

So the advice you got in the previous post is not good:

Use a foreign key for employee_ID in the projects table.

That would establish a many to one (N:1) relationship.

So your intersection table (Proj_Int) is basically OK. To enforce that you will have only one review per employee per project, you should make a combined primary key including the Project link and the Employee link. That will ensure that each Project-Employee combination only occurs once.