Relationships (1 Viewer)

[JohnD]

I have been battling this for awhile and this is a result from the thread below:
http://www.access-programmers.co.uk/forums/showthread.php?t=97048

However, it seems more fitting to have the thread continue here as I am moving onto the relationships.

After setting up my tables and attempting to have Normalization - I am now faced with the Relationships of the tables. Can someone please look at the attached picture showing the relationships that I set up and let me know if I am way off or right on the money.

Thank you so much for the input.

John D

 

[JohnD]

I apologize, please review this attachment instead of the first.

 

[neileg]

Whenever I see three tables linked in a triangle, it rings alarm bells for me. Not being a trained programmer, I can't give you a scientific explanation, but I have very rarely found a triangular join that is really required.

tblOverallStatus looks like a bit of a mess. Apart from unknown and SpecialNotes, everthing else could be derived from a query based on StudentID. Take that out and it all looks much saner!

 

[JohnD]

To my understanding, wouldnt I need the tblOverallStutus as a junction table?
Im trying to think of how I could eliminate the triangle relationships and the only thing that I can think of is having the StudentID deleted from some of the tables and eliminate the relationship as well.

I was also wondering if I should move the SupervisorID over to the tblEmployer and have the relationship exist between those two tables?

 

[nateobot]

I agree with Neil. Although I don't really understanding your process, it seems that tblOverallStatus could be consolidated into your tblStudents table. Aren't all the various statuses(Education, Employment, Crime, etc) related to a student?

Also you might want to slightly alter your tblUnavailable table to only store StudentID, UnavailableID, and UnavailableReason, maybe a Notes field. That way you can control the reasons through your front end, rather than creating a new field and updating your front end/queries/reports etc if management comes up with a new Unavailable Reason. Same thing could be done with tblCrime.

 

[JohnD]

Okay, I have made some adjustments, but im a little lost because I thought I needed a junction table - or is that tblStudents?

Anyways, its attached - Thank you for the help Nateobot & Neileg

oops - realized that on tblStudents, the EmployerID is still there - this has been removed

 

[wazz]

i find your table names a tiny bit confusing, but, i think you are right - you need some composite (junction) tables.
- student can earn many degrees (1:M) (diplomas, credits, etc) and a degree can be given to many students (1:M): tblStudentEducation (same as your tblContinuedEd; but PK in tblContinuedEd should be = PK from tblStudent + PK from tblInstitution)
- student can have many jobs (1:M) and employer can hire many students (1:M): tblStudentEmployer (same as yours but the PK in tblStudentEmployer should be = PK from tblStudent + PK from tblEmployer)
- if a student may commit many crimes you need another composite table for that. (it's fine if you only track one crime/student).
- tblUnavailable i don't understand

 

[JohnD]

Okay, more changes. I went back and read what Pat H specified

The way you do the linking is with the junction table. Think about it this way:
A company may employ more than one student so the studentID cannot go in the company table.
A student may work for more than one company so the companyID cannot go in the student table.
The solution is a third table which holds the primary keys from the two tables plus any information specific to the relationship such as start date.

What im gathering is that a junction table will NOT have a PK. So taking what you suggested Wazz and what Pat explained, I came up with the following (view attachment)

Oh, and the tblUnavailable: I need to know if the student is either Unavailable for Placement or Completion and the reasons why. The fields military, medical, etc will be check boxes, so the data type is yes/no.

John D

 

[JohnD]

I did some research and Pat Hartman made this response in a thread:

ALL tables will have a primary key in a properly constructed database. In order for Access (Jet) to recognize a 1-1 relationship, two tables must be related on their primary keys, so by definition, the related tables need to use their foreign key as their primary key. Don't forget, the foreign key is defined as long integer. It cannot be an autonumber.

Does this mean that I do or do not need a PK on my junction tables, because the junction tables have essentially two PK's from the other tables and if im understanding this all - they are considered FK's when on the Junction tables?

 

[Pat Hartman]

EVERY table needs a primary key in a properly designed database. In junction tables, I usually use a multi-field key. To create a multi-field key, click on one field and while holding the cntl key, click on each other field. When all the fields are highlighted (up to 10), press the key icon on the toolbar. In the cases where the junction table will itself have "child" tables, I use an autonumber as the PK and make a unique, two-field index for the two foreign keys to prevent duplication.

The CrimeID does not belong in the student table because a student could commit multiple crimes. Place the student ID in the crime table.

The relationship between students and employers should go through the supervisor table and since students can have multiple jobs must be through a junction table:

tblStudentJobs:
StudentID
SupervisorID

The relationship is to supervisor rather than employer because the student reports to a particular supervisor at a company and if you do it via company, you have no easy way to link to a specific supervisor.

 

[JohnD]

Pat,
I am recieving an error when I attempt to make StudentsID & SupervisorID a multi-field PK:

Invalid Index Definition

I tried playing with the indexes a little and I still recieved the error. Searching on the forum pulled up nothing as well and the Access help mentioned that its most often used in a M:M relationship which I was under the impression (from reading other threads) that a M:M Relationship means that you have a poor design
--
tblCrime - the reason that I have it listed in students is because the tables Data type is all yes/no (they will be check box's on a form), with the exception of notes, which will be set to memo. We have no interest on what the student did. Essentially when speaking to them, we will list the crime in notes and check the appropriate box and how they were convicted. So technically, we dont need to have to list if they have had one or twenty crimes, just if it one of there crimes was a felony or what not.
--
Moving the Supervisor into the junction table makes perfect sense to me.

Thanks for the help once again

John D

 

[JohnD]

Fixed the problem

I went into Indexes and for some reason it had duplicates of the PK's and sometimes it tripled it. So, I deleted the extra Index's and it worked after.

My only question is this - We need the name (generally supervisor) of the person who can verify there employment. This is where we would enter into Supervisor. BUT, sometimes, the person to verify is someone in HR or DOP and we dont get a direct name. With this table design, if I list someone in HR under Supervisor, it will end up linking to only one company - I hope this makes sense. So, it seems that I would need to have StudentID, SupervisorID, and EmployerID all in a junction table to solve this problem. (in fact, change SupervisorID to ContactID, because we need a contact person, not a supervisor)

Am I understanding this correctly?

I have attached the relationships that I now have.

 

[Pat Hartman]

No the relationship is StudentID --> ContactID only The employer relationship is derived from the supervisor relationship.

So:

StudentID --> ContactID
              ContactID --> EmployerID

It isn't the M-M relationships that are recommended against, it is the 1-1 relationships. And the reason isn't that 1-1 relationships are bad in any way, it is simply that most people make them for the wrong reasons.