Relationship Structure Help

[123James]

Hi everyone

I would appreciate some help redesigning a database structure.

Currently the database has a table holding students personal details. Linked to this table with a one-to-many link is a table holding subject reports.

Also linked to the student table are two other tables holding prior attainment and mentoring details. These table are linked one-to-one.

Personally I believe the two tables linked with one-to-one links should be merged with the students table to become one single table holding student data.

Is this right?

 

[FoFa]

Depends, would the updates be harder (more cluttered) if the tables were merged? What if you want to track say, mentoring history (sounds like you are not doing that today, but may). Or if they are mentoring more than one person?
Just because you have a one to one does not neccessarly mean they should be merged.

 

[123James]

Each of the tables are updated once at the beginning of each academic year. After that they are only used to view data or added to when a new student joins the school.

 

[Pat Hartman]

The mentoring table sounds like it should be related 1-m rather than 1-1. Can't a student have more than one mentor?

You need to review the entire schema to be sure that you don't have any repeating groups in any of the tables. 1-1 relationships are frequently created by novices because of the large number of fields that are required when 1-m relationships are flattened. In the real world, true 1-1 relationships are extremely rare.