Relationship Set-Up

[doddy88]

Hi,

I am creating a database for a placements office at a university. Students have the option to undertake a placement in their 3rd year.

I've been advised to use joining tables but I thought they were only for many to many tables.

My current structure is:

tblStudents
StudentID - Number

tblContactDetails
ContactReferenceNumber - Autonumber

tblEmployerInfo
EmployerID - Autonumber

tblStudentContacts
scStudentID - Lookup Wizard linked to tblstudents - StudentID.
scContactReferenceNumber - Number/Long Integer

tblStudentEmployers
scStudentID - Loookup Wizard linked to tblstudents - StudentID.
scEmployerID - Number/Long Integer

Joining type wise, from studentID to scStudentID they both set as one-to-many but from EmployerID to scEmployerID and ContactReferenceNumber to scContactReferenceNumber they join as a one-to-one relationship. Is this right?

One student can have more than one employer as they might do two or three placements in a year. A student may also have more than one set of contact details as they differ from placement to placement. An employer can only have one student though and a contact details record can only belong to one student so I believe they should both be one-to-many and don't require the joining tables?

Regards

 

[jdraw]

Please post a jpg of your tables and relationships.

You talk about Placements, but you have no such Table?? Why??

 

[YNWA]

You need set up like this I think

Student tbl
Placement tbl

Within placement tbl you would have employerID which is brought in from an emplyer reference table if you have a set list of employers. If you just free type then no relationship is needed for empoyer.

What is contact details? what info does it store?

 

[doddy88]

Hi,

Sorry, yes I should have thought about the names better, tblemployerinfo is essentially the placements.

I think I have resolved the issue like YNWA states by reducing it to two tables a student and a placement table as the contact details are essentially the placement details such as company address, work number and email.