2

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.

  1. 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.

  2. 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:

  1. I cannot easily set them in the Relationships window of Access, which makes me fear that I have poorly designed the relationships.

  2. 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:

  • 2
    These are not one-to-many relationships, they are many-to-many. A child can have many parents, a parent can have many children, so looking at them as one-to-many would be incorrect. – Mark Sinkinson Jun 25 '14 at 14:40
  • Well, a child can most certainly have many parents, but he or she can only have one mother and one father. Right now, the school office uses a "Cartotheque" (some kind of Rolodex file) and has an index card for every child. On every card, there are two columns: Father and Mother. I see this example as an indication that the relationship is not meant to be a "many-to-many" one. – patpdm Jun 25 '14 at 14:55
  • 3
    What if they have 2 fathers or 2 mothers? Or a step-parent with legal custody as well as 2 other parents? I'd strip this out to a 'parent_child' relationship table. Don't limit yourself to something that may require changing at a later date – Mark Sinkinson Jun 25 '14 at 15:01
  • It is true that Mother and Father look outdated. Until now, the practice at the school office has been to consider one or two responsible adults as "parents" and to mention the others in some kind of "Misc" field. They do not want to change that, if only for the layout of the "Cartotheque" cards, that they still want to be able to print (as reports). Maybe I should take your advice and create a junction table between Children and Parents. However, I would have to limit (for now) the number of parents to 2 and/or to select only the 2 first records for the individual index cards. – patpdm Jun 26 '14 at 6:29
3

It is a one to many from the "Children" table; one child to many staff, one child to one or more "ResponsibleAdults" (who could be any familial relationship). If you change Father & Mother fields to be Responsible1, Responsible2 and add Responsible1Relationship, Responsible2Relationship to "Children", you'll solve that problem. Alternatively, you can create a join or link table using just childID, ResponsibleID and relationship (1 or more records per child ID). You'll want a "FamRelationship" lookup table for consistency in your data entry (might be parent, aunt, grandparent, guardian, etc". This way Child Joe can have Parents Mike and Mary, while his cousin child Jane can have Uncle Mike and Aunt Mary. Both Children records would reference the same 2 what you're currently calling "Parents" records.

If a "staff" member is not a member of Board of Directors and it's important to note that, add a tinyint indicator to "staff" (true/false, 1/0).

  • Indeed, I think I will have a create a join table between Children and Parents. But note that if I keep Responsible1 and Responsible2 (let's say we do not care about their status), there will be two relationships between Children and the Children/Parents join table. However, I take your advice of combining a join table and a lookup table. (I will edit my question shortly to add a new version of the schema, following your advice and Mark Sinkinson's.) – patpdm Jun 26 '14 at 6:52
  • No, 2 relationships was not my suggestion. Either Resp1 and Resp2 in "Children" or join table. That's why I said "alternatively you can create.." The join table idea also applies to Child/Staff, as you've shown in updated "schema". – GDD Jun 26 '14 at 11:42
  • OK, sorry, I misunderstood this part of your post. As you probably noticed, English is not my mother tongue. Now, how would you call my "schema"? Scheme? Model? Drawing? – patpdm Jun 26 '14 at 12:39
  • Your English is just fine (certainly much better than my [any other language]). I just find "schema" funny. Yes, I'd call the drawing just that, what it represents is data model or table structures. – GDD Jun 27 '14 at 10:20
2

I would definitely advise using intersection and lookup tables on the staff side.

On the parent side it is more of a judgement call. Still, I'd use the intersections. Although you only have two parents you loose almost nothing by using the intersetion table (a little complexity) and gain flexibility. You should be able to hard-code the ParentStatus into whatever populates the 'mother' and 'father' fields (not an Access guru, sorry) of the example form, thereby maintianing the specificity of the original schema. You will be able to code other screens to allow for the muliplicity of blended families, for example. If it's important that each child has at most one "mother" and one "father" you can put a UNIQUE constraint on IT_Parents_Children.Ref_Child and .Ref_ParentStatus.

  • Very helpful comment. So I will work in this direction, and hard-code when needed. Thanks for the tip about the UNIQUE constraint. – patpdm Jun 26 '14 at 12:50
1

This would be easier:

create table individuals (
  individual_id int primary key,
  name text not null
);

create table individuals_relationships (
  base_individual_id int references individuals(individual_id),
  type text, --this should point to a look-up table in real life
  counter_individual_id int references individuals(individual_id),

  primary key (base_individual_id, type, counter_individual_id)
);

Sample data:

insert into individuals values
(1, 'Homer'),
(2, 'Marge'),
(3, 'Bart'),
(4, 'Lisa');

insert into individuals_relationships values 
(1, 'PARENT_OF', 3),
(1, 'PARENT_OF', 4),
(3, 'SIBLING_OF', 4),
(1, 'SPOUSE_OF', 2);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.