how to model a "sometimes dependent relationship"

[pietlinden]

I'm trying to model a weird situation. I'm slapping together a
database that logs user needs. Kind of like NWind, but instead of
customer-invoice-lineitems-products-suppliers, I have
(guardian)-client-needsdetails-needs-providesneeds-agency.

the needs/providers stuff is worked out. It follows the basic NWind
structure for products and suppliers or whatever.

The problem I'm trying to work out is this:
The clients are customers of the county Mental Health/Mental
Retardation agency. Basically the mentally ill do not need a legal
guardian, while the mentally retarded do. (broadly speaking). In other
words, some of the clients can share a guardian, but not all of them
require one, so "Client/Patient" can't really be modeled as a weak
entity/child of "Guardian".

The fun part is that I need to store mailing address info for these
clients. The MR portion will have a guardian who receives their
paperwork, while the MH population don't need a guardian.

This screws up the standard 1-M relationship that would exist between
client and guardian. How do I deal with addresses in this case?

Guardian---(1,M)---MR_Client---(1,M)---ClientNeeds---(M,1)---Need.
(etc)
[No Guardian] MH_Client---(1,M)---ClientNeeds---(M,1)---Need.

do each of the MH clients have themselves as a guardian, so only the
address info is filled in (No actual Guardian info)?

Just wondering how to deal with this, that's all. Any ideas? I was
thinking about subclassing, but that's not really right either.
Rebecca's example on Accessweb is organization/individual, but that's

PartyType---(1,M)---Party----(1,1)-----Person
|----(1,1)---------Organization

with a disjoin between Party and Person, and Party and Organization. I
understand that fine, but my case is that in my case, the parent table
(Guardian) is optional. And in database terms, that means that some
records can be orphaned, which is a no-no. Is my only option to deal
with this in code?

Okay, so what's the hangup? (Yes, this is going somewhere!)
I'm trying to figure out where/how to store contact/address information
- for all the MR population, the address belongs to the guardian. For
the MH population, it belongs to the individual. I guess I could put
the MH individuals in both the Guardian and the Client tables, but that
seems weird too...

Any ideas how to model this? Or general outlines on how to deal with
this in code? (Don't need a huge example, but a pointer in the right
direction.)

If I've left out some key info, let me know.
Thanks,

Pieter

 

[Allen Browne]

Hi Pieter

Hopefully you will get several suggestions on this one, so you can choose
one that suits you best, 'coz you are closer to the data than we are.

First thing that comes to mind for me is to put all the persons (clients,
guardians etc) into the one table, with a CarerID foreign key field that
self-joins to another in the same table (and possibly a PersonType lookup to
differentiate whether the entry is a client or guardian.)

The self-join opens the door for several ways to handle addresses, e.g.:
- address fields in the person table. If person's own address fields are
null, use the person's parent's address.
- related table of addresses, where one address (including addressee name)
can belong to multiple persons (so the patient's related address is actually
the address of their carer.)

HTH.

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

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

I'm trying to model a weird situation. I'm slapping together a
database that logs user needs. Kind of like NWind, but instead of
customer-invoice-lineitems-products-suppliers, I have
(guardian)-client-needsdetails-needs-providesneeds-agency.

the needs/providers stuff is worked out. It follows the basic NWind
structure for products and suppliers or whatever.

The problem I'm trying to work out is this:
The clients are customers of the county Mental Health/Mental
Retardation agency. Basically the mentally ill do not need a legal
guardian, while the mentally retarded do. (broadly speaking). In other
words, some of the clients can share a guardian, but not all of them
require one, so "Client/Patient" can't really be modeled as a weak
entity/child of "Guardian".

The fun part is that I need to store mailing address info for these
clients. The MR portion will have a guardian who receives their
paperwork, while the MH population don't need a guardian.

This screws up the standard 1-M relationship that would exist between
client and guardian. How do I deal with addresses in this case?

Guardian---(1,M)---MR_Client---(1,M)---ClientNeeds---(M,1)---Need.
(etc)
[No Guardian] MH_Client---(1,M)---ClientNeeds---(M,1)---Need.

do each of the MH clients have themselves as a guardian, so only the
address info is filled in (No actual Guardian info)?

Just wondering how to deal with this, that's all. Any ideas? I was
thinking about subclassing, but that's not really right either.
Rebecca's example on Accessweb is organization/individual, but that's

PartyType---(1,M)---Party----(1,1)-----Person
|----(1,1)---------Organization

with a disjoin between Party and Person, and Party and Organization. I
understand that fine, but my case is that in my case, the parent table
(Guardian) is optional. And in database terms, that means that some
records can be orphaned, which is a no-no. Is my only option to deal
with this in code?

Okay, so what's the hangup? (Yes, this is going somewhere!)
I'm trying to figure out where/how to store contact/address information
- for all the MR population, the address belongs to the guardian. For
the MH population, it belongs to the individual. I guess I could put
the MH individuals in both the Guardian and the Client tables, but that
seems weird too...

Any ideas how to model this? Or general outlines on how to deal with
this in code? (Don't need a huge example, but a pointer in the right
direction.)

If I've left out some key info, let me know.
Thanks,

Pieter

 

[pietlinden]

That might be the only thing I didn't think of... or maybe I did.
Can't remember anymore. (Must be more careful not to drink an entire
pot of coffee at 5pm... ) Good plan! I'm on it.

Thanks a mil!
Pieter

 

[pietlinden]

when I'm awake, I'll see if I can't do a mailing label using
fConcatChild so I can get something like

WardList <result of fConcatChild>
c/o <Guardian Full>
Address
City, State Zip

It's just the fconcat with the self join that's got me boggled at the
moment. Maybe a nap will help...

 

[Allen Browne]

Hope the nap helps.

Perhaps some of us should form an AA group (Access Anonymous), to ensure we
look after ourselves.

 

[Larry Linson]

Not every Record need have a corresponding Record in the Related Table.
Most people expect to have a one to many with the "one" is the master and
the "many" are the related/child records, and enforcing referential
integrity will force there be a "master" before allowing creation of a
child.

In your case, however, the relationship would be an "inner join" to alow a
client Record without a Guardian. Thus, you can't specify "referential
integrity."

Larry Linson
Microsoft Access MVP

I'm trying to model a weird situation. I'm slapping together a
database that logs user needs. Kind of like NWind, but instead of
customer-invoice-lineitems-products-suppliers, I have
(guardian)-client-needsdetails-needs-providesneeds-agency.

the needs/providers stuff is worked out. It follows the basic NWind
structure for products and suppliers or whatever.

The problem I'm trying to work out is this:
The clients are customers of the county Mental Health/Mental
Retardation agency. Basically the mentally ill do not need a legal
guardian, while the mentally retarded do. (broadly speaking). In other
words, some of the clients can share a guardian, but not all of them
require one, so "Client/Patient" can't really be modeled as a weak
entity/child of "Guardian".

The fun part is that I need to store mailing address info for these
clients. The MR portion will have a guardian who receives their
paperwork, while the MH population don't need a guardian.

This screws up the standard 1-M relationship that would exist between
client and guardian. How do I deal with addresses in this case?

Guardian---(1,M)---MR_Client---(1,M)---ClientNeeds---(M,1)---Need.
(etc)
[No Guardian] MH_Client---(1,M)---ClientNeeds---(M,1)---Need.

do each of the MH clients have themselves as a guardian, so only the
address info is filled in (No actual Guardian info)?

Just wondering how to deal with this, that's all. Any ideas? I was
thinking about subclassing, but that's not really right either.
Rebecca's example on Accessweb is organization/individual, but that's

PartyType---(1,M)---Party----(1,1)-----Person
|----(1,1)---------Organization

with a disjoin between Party and Person, and Party and Organization. I
understand that fine, but my case is that in my case, the parent table
(Guardian) is optional. And in database terms, that means that some
records can be orphaned, which is a no-no. Is my only option to deal
with this in code?

Okay, so what's the hangup? (Yes, this is going somewhere!)
I'm trying to figure out where/how to store contact/address information
- for all the MR population, the address belongs to the guardian. For
the MH population, it belongs to the individual. I guess I could put
the MH individuals in both the Guardian and the Client tables, but that
seems weird too...

Any ideas how to model this? Or general outlines on how to deal with
this in code? (Don't need a huge example, but a pointer in the right
direction.)

If I've left out some key info, let me know.
Thanks,

Pieter