table relationships

[Kurt]

I'm designing a database to track information for child
abuse cases referred to a clinic. I need some help
relating tables.

The database will need to keep track of victims,
perpetrators, relatives of victims, case information, and
much more.

Some assumptions:

1. A victim may have one or more perpetrators.
2. A perpetrator may have one or more victims.
3. A victim may be a perpetrator one day.
4. A perpetrator may be a victim one day.
5. A victim may have one or more relatives, any of whom
could be the perpetrator in this or another case.
6. A victim can have multiple cases (e.g., abused last
year, then abused again this year).

For starters, I've come up with the following tables:

tblVictims
tblPerpetrators
tblVictimsPerps (junction table)
tblRelatives
tblCases

.. . . and related them like this:
http://members.cox.net/kheisler6/tables1.jpg

There are obvious problems with this design, but because
of all the assumptions (e.g., victims can become
perpetrators one day and vice versa), I wasn't sure how to
get started. Ideas?

Thanks.

Kurt

 

[Allen Browne]

Consider using a single table - tblPerson - rather than tblVictims and
tblPerpetrators.

Drag 2 copies of the tblPerson into the relationship window.
Access aliases the 2nd one as "tblPerson_1".
Relate tblVictimPerps.VictimID to tblPerson.PersonID.
Relate tblVictimPerps.PerpetratorID to tblPerson_1.PersonID.
(The 2nd instance of tblPerson lets you create 2 separate relations, as
distinct from a 2-field relation.)

In the same way, tblRelatives will have a VictimID and RelativeID, related
to tblPerson.PersonID and tblPerson_1.PersonID respectively.

Having a single table for persons should solve your concern that a person
can be in different categories (victims/perpetrator/relative).

 

[Tim Ferguson]

I wasn't sure how to get started. Ideas?

Requirements analysis. Business case. Outline proposal. Project budget.
Market search. Risk analysis...

In other words: what is it going to do? What is the problem it is going to
solve? How do you see it fitting in with your organisation? How much are
you willing to spend on it (money or time) and who is going to pay? Has
anybody already got one and what are the advantages of using one off=-the-
shelf? What happens if it goes wrong (don't forget legal cover, hot topics
here in europe and prolly usa too)?...


Just initial ideas...


Tim F

 

[Kurt]

In the same way, tblRelatives will have a VictimID

and RelativeID, related to tblPerson.PersonID and
tblPerson_1.PersonID respectively.

I could link tblRelatives.VictimID to tblPeople.PersonID.
However, when I tried to link tblRelatives.RelativeID to
tblPeople_1.PersonID it recognized it initially as a one-
to-one and then, upon reopening the relationship window,
created tblRelatives_1 and linked
tblRelatives_1.RelativeID to tblPeople.PersonID. Perhaps
this is okay?

Screenshot here:
http://members.cox.net/kheisler6/tables2.jpg

Kurt

 

[Allen Browne]

tblRelative would be a junction table between 2 copies of tblPeople.

Its fields would be just these:
VictimID Number (Long)
RelativeID Number (Long)
RoleID foreign key, indicating the kind of relationship.
Primary key could be the combination of VictimID + RelativeID. Or, if you
prefer, you could use a VictimRelativeID AutoNumber field as p.k.

With that structure, you can enter as many relatives as necessary into the
junction table for any victim.

 

[Kurt]

Here is the revised table design:
http://members.cox.net/kheisler6/tables3.jpg

Assuming this is right, my challenge now is setting up
frmVictims: a form to add a victim, along with his case
information (including perpetrator), his relatives,
etc.

I've come up with a layout like this:

frmVictims (based on tblPeople)
--fsubCases (based on tblCases)
-----fsubPerpetrators (based on tblVictimsPerps)

Screenshot here:
http://members.cox.net/kheisler6/frmVictims1.jpg

fsubPerpetrators is linked to fsubCases by VictimPerpID,
but there's no way to link fsubCases (i.e., tblCases) to
frmVictims (i.e., tblPeople). Also, because victims and
perpetrators are all in tblPeople, when I type in a
victim I can then select him as a perpetrator for this
same case!

Any guidance? Thank you for continued help.

Kurt

 

[Allen Browne]

Hi Kurt.

We can't walk you right though everything from design through interface and
reports, but I think you will need another form for entering the cases and
selecting the people involved.

You could then create a query based on tblCases and tblVictimsPerps, and use
it as the source for a continuous read-only subform on the Cases tab of
frmVictims, so you have a list of the cases the person is involved in. You
might set up the DblClick event of CaseID to open the relevant record in
frmCase.

To prevent a victim being the perpetrator also in tblVictimsPerps
1. Open the table in design view.
2. Open the Properties box (View menu)
3. Beside the Validation Rule in the Properties dialog, enter:
[VictimID] <> [PerpetratorID]

Note that the validation rule for the table is different from the Validation
Rule for the field (in the lower pane of table design).

You probably want to the Required property to Yes for both VictimID and
PerpetratorID fields; if not you may need to handle Nulls in the Validation
Rule as well, e.g.:
([VicitimID] Is Null) OR ([PerpetratorID] Is Null) OR ([VictimID] <>
[PerpetratorID])

Self-abuse doesn't count? Perhaps that would include teaching oneself how to
create databases.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is the revised table design:
http://members.cox.net/kheisler6/tables3.jpg

Assuming this is right, my challenge now is setting up
frmVictims: a form to add a victim, along with his case
information (including perpetrator), his relatives,
etc.

I've come up with a layout like this:

frmVictims (based on tblPeople)
--fsubCases (based on tblCases)
-----fsubPerpetrators (based on tblVictimsPerps)

Screenshot here:
http://members.cox.net/kheisler6/frmVictims1.jpg

fsubPerpetrators is linked to fsubCases by VictimPerpID,
but there's no way to link fsubCases (i.e., tblCases) to
frmVictims (i.e., tblPeople). Also, because victims and
perpetrators are all in tblPeople, when I type in a
victim I can then select him as a perpetrator for this
same case!

Any guidance? Thank you for continued help.

Kurt

-----Original Message-----
tblRelative would be a junction table between 2 copies of tblPeople.

Its fields would be just these:
VictimID Number (Long)
RelativeID Number (Long)
RoleID foreign key, indicating the kind of relationship.
Primary key could be the combination of VictimID + RelativeID. Or, if you
prefer, you could use a VictimRelativeID AutoNumber field as p.k.

With that structure, you can enter as many relatives as necessary into the
junction table for any victim.

 

[Kurt]

Thanks for all your help on this.

I have some more questions about my laundry, cooking, and
taxes, but perhaps I'll save those for another time.

Thanks again. - Kurt

-----Original Message-----
Hi Kurt.

We can't walk you right though everything from design through interface and
reports, but I think you will need another form for entering the cases and
selecting the people involved.

You could then create a query based on tblCases and tblVictimsPerps, and use
it as the source for a continuous read-only subform on the Cases tab of
frmVictims, so you have a list of the cases the person is involved in. You
might set up the DblClick event of CaseID to open the relevant record in
frmCase.

To prevent a victim being the perpetrator also in tblVictimsPerps
1. Open the table in design view.
2. Open the Properties box (View menu)
3. Beside the Validation Rule in the Properties dialog, enter:
[VictimID] <> [PerpetratorID]

Note that the validation rule for the table is different from the Validation
Rule for the field (in the lower pane of table design).

You probably want to the Required property to Yes for both VictimID and
PerpetratorID fields; if not you may need to handle Nulls in the Validation
Rule as well, e.g.:
([VicitimID] Is Null) OR ([PerpetratorID] Is Null) OR

[PerpetratorID])

Self-abuse doesn't count? Perhaps that would include teaching oneself how to
create databases.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is the revised table design:
http://members.cox.net/kheisler6/tables3.jpg

Assuming this is right, my challenge now is setting up
frmVictims: a form to add a victim, along with his case
information (including perpetrator), his relatives,
etc.

I've come up with a layout like this:

frmVictims (based on tblPeople)
--fsubCases (based on tblCases)
-----fsubPerpetrators (based on tblVictimsPerps)

Screenshot here:
http://members.cox.net/kheisler6/frmVictims1.jpg

fsubPerpetrators is linked to fsubCases by VictimPerpID,
but there's no way to link fsubCases (i.e., tblCases) to
frmVictims (i.e., tblPeople). Also, because victims and
perpetrators are all in tblPeople, when I type in a
victim I can then select him as a perpetrator for this
same case!

Any guidance? Thank you for continued help.

Kurt

-----Original Message-----
tblRelative would be a junction table between 2 copies of tblPeople.

Its fields would be just these:
VictimID Number (Long)
RelativeID Number (Long)
RoleID foreign key, indicating the kind of relationship.
Primary key could be the combination of VictimID + RelativeID. Or, if you
prefer, you could use a VictimRelativeID AutoNumber field as p.k.

With that structure, you can enter as many relatives as necessary into the
junction table for any victim.


In the same way, tblRelatives will have a VictimID
and RelativeID, related to tblPerson.PersonID and
tblPerson_1.PersonID respectively.

I could link tblRelatives.VictimID to tblPeople.PersonID.
However, when I tried to link tblRelatives.RelativeID to
tblPeople_1.PersonID it recognized it initially as a one-
to-one and then, upon reopening the relationship window,
created tblRelatives_1 and linked
tblRelatives_1.RelativeID to tblPeople.PersonID. Perhaps
this is okay?

Screenshot here:
http://members.cox.net/kheisler6/tables2.jpg

Kurt

message
I'm designing a database to track information for child
abuse cases referred to a clinic. I need some help
relating tables.

The database will need to keep track of victims,
perpetrators, relatives of victims, case information,
and
much more.

Some assumptions:

1. A victim may have one or more perpetrators.
2. A perpetrator may have one or more victims.
3. A victim may be a perpetrator one day.
4. A perpetrator may be a victim one day.
5. A victim may have one or more relatives, any of whom
could be the perpetrator in this or another case.
6. A victim can have multiple cases (e.g., abused last
year, then abused again this year).

For starters, I've come up with the following tables:

tblVictims
tblPerpetrators
tblVictimsPerps (junction table)
tblRelatives
tblCases

. . . and related them like this:
http://members.cox.net/kheisler6/tables1.jpg

There are obvious problems with this design, but
because
of all the assumptions (e.g., victims can become
perpetrators one day and vice versa), I wasn't sure
how to
get started. Ideas?

Thanks.

Kurt

.