In the process of creating a small database for a therapeutic boarding school with Access 2013, I have a problem when designing the relational database model.
Two times at least, I ran into a situation where there were many one-to-many relationships between two tables.
There are two one-to-many relationships between Parents and Children because a) some parents have more than one child in the school; b) the father and the mother often have different addresses and we have to contact them separately.
There are three (actually even more) one-to-many relationships between Staff and Children because a) every child has an educator of reference (Ref_Educ); b) every child has a person of reference in the board of directors (Ref_Dir); c) every child has a "godfather" (Ref_Godf). But these rules are soft. Sometimes, the person of reference Ref_Dir is not a member of the board of directors. Sometimes, the educator of reference is also the "godfather" of the child, and so on.
So it appears to me that the idea of setting many one-to-many relationships between these tables is somewhat logical, but on the other hand:
I cannot easily set them in the Relationships window of Access, which makes me fear that I have poorly designed the relationships.
In this similar question, it was suggested to "fix the database design". (However, I think that my problem is actually different, as a given person in the Staff table can be linked several times to the same Children entry.)
Could you give me some piece of advice? Thanks in advance.
Edit 2014-06-26 I followed your pieces of advice and updated the schema as follows:
As you can see, I added join tables, and lookup tables for the consistency of status.
It surely makes more sense and is less limited for the case we would want to add some features in the future.
But in the other hand, is it compatible with the fact that for now, there are a finite number of status (father/mother, ref_educ/ref_dir/ref_godf) that are meant to be individually hardcoded in our "Cartotheque" form?
To make things clearer, I draw a mock-up preview of how it should look: