One to many

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Got a problem. I have a one to many relationship that is
working just fine. The one side holds family data. The
many side holds the family members data. On the many
side, I want to be able to select one person(from the
many side) as the head of family. However, if I make the
head of family field a Yes/No, then I can end up with
mulitiple head of family. Do I have to control this
through code(if 1 person is head and another is checked,
I have to run some code or a query) or is there a better
way?

thanks,
 
Steve,

Assuming there is a PK field (an autonumber?) in the members table, you
could add a field in the family table to hold the value of the PK field of
the head member. That way there could never be duplicates.

HTH,
Nikos
 
Assuming there is a PK field (an autonumber?) in the members table,
you could add a field in the family table to hold the value of the PK
field of the head member. That way there could never be duplicates.
Click to expand...

It also creates huge difficulties with queries like, "what family is Jo a
member of?"

The short answer is that there is no simple way to make this constraint
in a desktop system like Jet. If the OP can move up to a real dbms that
provides triggers, then the constraint can be built. Failing that, some
code behind the form can either (a) check for the non-existence of a head
of family before flagging the Member record or (b) silently de-heading it
before flagging the new one.

EG

Private Sub MemberForm_Update(Cancel as Integer)

Dim strWhere as String
Dim strSQL as String

' This counts how many heads there are in the current
' family
strWhere = "FamilyNum = " & me!FamilyNum & " AND " & _
"HeadOfFamily = TRUE"

If DCount("*", "Members", strWhere) > 0 Then
' there is already a head of the family
' Do this to de-head the family:
strSQL="UPDATE Members SET HeadOfFamily = FALSE " & _
"WHERE FamilyNum = " & Me!FamilyNum

' run it with error trapping
CurrentDB().Execute strSQL, dbFailOnError

' The alternative would be just to set
' Cancel = True
' and let Access GUI warn the user

End If

End Sub


.... or something along those lines. This is, of course, untested code so
treat it with great caution!! Bear in mind you have to stop people
messing with the tables in datasheets, VBA, SQL, Excel and so on, because
that will step past your code.

HTH


Tim F
 
Tim,

The method you suggest (in Jet) provides a solution, but (a) it's not the
simplest one, and (b) it does have the drawbacks you mention, therefore I
suggested the other solution. I'm not following you on how this creates
problems with qeurying family membership of a person, though; I fail to see
how adding a field in the Families table changes this vs. the original
design, it's still a select query on the two joined tables with a filter on
a Members table field, the way I envisage it. What do you mean?

Thanks,
Nikos
 
I fail to see how adding a field in the Families table changes
this vs. the original design, it's still a select query on the two
joined tables with a filter on a Members table field, the way I
envisage it. What do you mean?
Click to expand...

Just that: with a structure like


Families(FamilyNum, HeadofFam(FK ref Members), ...)

Members(PersonID, BelongsTo(FK ref Family), ...)

the first problem is that there is no rule that says Families.HeadOfFam
points to a Member who BelongsTo that family -- in other words, the
records

Families("Smith", "John",...)
Members("John", "Davies", ...)

give the information that John is head of the Smith family, while being a
member of the Davies family, which is presumably nonsense. Accordingly,
any attempt to make sense of this with a SELECT query is going to involve
some nasty UNIONs to include the Families.HeadOfFam values and the
Members.PersonID. In short, the semantics suggest that being a head of
family is a special kind of family membership, not something different.
The constraint that there can only be one head is probably a bit
artificial anyway -- I bet it won't be long before some particular family
objects that both parents want to be treated equally as joint heads. Such
is life!

All the best


Tim F
 
Tim,

Thanks for your reply. What I had in mind was that (a) the head of family
would have a record in the members table just like everybody else, so no
need for a Union, and (b) the pertinent field in the Families table would be
populated by means of a bound listbox or combo on a form, its recordset
filtered on FamilyID, so no chance of assigning a non-member (not through
the form, at least).
At the end of the day, there is no bulletproof design, no matter how hard
you try to account for every possible user error you can think of, there
will allways be a user with more imagination!
Now, on how many family members can claim headship, that's a different
issue, and not even a database one, as far as I'm concerned; I was merely
answering the question asked, not arguing the accuracy of the model,
although you do have a point there! Some would counter-argue, though, that,
realistically, the head of the family is always the wife!

Regards,
Nikos
 
(b) the pertinent field in the Families table would be
populated by means of a bound listbox or combo on a form, its
recordset filtered on FamilyID, so no chance of assigning a non-member
(not through the form, at least).
Click to expand...

Sorry: there are innumerable ways to do this including Excel, VBA,
querydef, table datasheets; even CorelDraw!...

PARAMETERS [AnyValidMemberIDYouLike] Number;
UPDATE Families
SET HeadOfFam = [AnyValidMemberIDYouLike]
WHERE FamilyNum = 127

and there is no constraint at the db engine level that
AnyValidMemberIDYouLike has to belong to the same family. And if it's
not protected by the engine, it's just not protected. Form events help to
manage but it's not reasonable to call them part of the data integrity
model.
At the end of the day, there is no bulletproof design, no matter how
hard you try to account for every possible user error you can think
of, there will allways be a user with more imagination!
Click to expand...

In Access, that is true because it's an incomplete implementation. As I
said in an earlier post, real DBMSs provide update triggers than can and
do provide exactly that bulletproof cover.

B Wishes


Tim F
 
Back
Top