conflicts in mail permissions

[Catherine Jo Morgan]

I'm making a db for a client whose clients often share a household. For
example, one household member may become a client, with one or more others
becoming clients later. So I have a table for People and another for
Households - so that mailings can go to a household and not be duplicated.

What if each person in the household sets mailing permissions differently?
One says "yes, send me news of new events" and another says "no, don't
contact me?" In this case, the mailing would have to go just to the
individual, not be addressed to the household. What's the simplest way to
handle this in the db?

It looks as if MailPermissions has to be a field in the People table only.
So somehow the query for a mailing list would have to check to see if all
persons in a household has the appropriate permission level for the mailing.
If so, the mailing would go to the household. If not, the mailing would go
to the individual(s). The address would be the same either way - it's just
the name that would vary. e.g. Jim Smith vs. Jim & Judy Smith.

Is this a workable approach? I'm still so focused on table design that I
haven't learned enough about queries so I don't even know if what I'm
envisioning will work. And it certainly seems complicated. Maybe there's a
much better way? TIA

[Edward]

"Catherine Jo Morgan" <cj******@hemc.net> wrote in message news:<Kf********************@hemc.net>...

I'm making a db for a client whose clients often share a household. For
example, one household member may become a client, with one or more others
becoming clients later. So I have a table for People and another for
Households - so that mailings can go to a household and not be duplicated.

What if each person in the household sets mailing permissions differently?
One says "yes, send me news of new events" and another says "no, don't
contact me?" In this case, the mailing would have to go just to the
individual, not be addressed to the household. What's the simplest way to
handle this in the db?

It looks as if MailPermissions has to be a field in the People table only.
So somehow the query for a mailing list would have to check to see if all
persons in a household has the appropriate permission level for the mailing.
If so, the mailing would go to the household. If not, the mailing would go
to the individual(s). The address would be the same either way - it's just
the name that would vary. e.g. Jim Smith vs. Jim & Judy Smith.

Is this a workable approach? I'm still so focused on table design that I
haven't learned enough about queries so I don't even know if what I'm
envisioning will work. And it certainly seems complicated. Maybe there's a
much better way? TIA

You have a 1..n (one to many) relationship between Household and
Occupant. These should be separate tables.

You don't say what the front end (if any) there is to this, but for
the sake of argument let's assume an Access application.

You need some method of capturing the Occupant's mailing preference,
with some kind of default (such as "No Mailings" or whatever suits
your client's business).

It would be relatively straightforward to check for the mailing
preferences of all Occupants and devise some code to send appropriate
mailings (per household if all occupants say "Yes, please" or per
occupant if one or more say "No, thanks".) Your design might mandate
a mailing permissions field on the Household table, but in the
information you provided this would seem to me to be derived data, and
therefore Not A Good Thing (TM).

Without table designs I can't be more specific. I am intrigued,
however, how you got the job of designing this database when what
appears on the face of it to be such a simple design and
implementation consideration is put to the public in this way.

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

[Catherine Jo Morgan]

"Edward" <te********@hotmail.com> wrote in message
news:25*************************@posting.google.co m...
Your design might mandate

a mailing permissions field on the Household table, but in the
information you provided this would seem to me to be derived data, and
therefore Not A Good Thing (TM).

Without table designs I can't be more specific. I am intrigued,
however, how you got the job of designing this database when what
appears on the face of it to be such a simple design and
implementation consideration is put to the public in this way.

Edward

Yes, I can see that the Household mailing permissions need to be calculated
from the mailing permissions of the individuals. Thanks. Now that you point
that out it seems obvious.

As to how I got a job designing a database when it's clearly way over my
head...long story, but I'm stuck with it. I'll just have to learn. Thanks
for helping.

[Edward]

"Catherine Jo Morgan" <cj******@hemc.net> wrote in message news:<wg********************@hemc.net>...

"Edward" <te********@hotmail.com> wrote in message
news:25*************************@posting.google.co m...
Your design might mandate

a mailing permissions field on the Household table, but in the
information you provided this would seem to me to be derived data, and
therefore Not A Good Thing (TM).

Without table designs I can't be more specific. I am intrigued,
however, how you got the job of designing this database when what
appears on the face of it to be such a simple design and
implementation consideration is put to the public in this way.

Edward

Yes, I can see that the Household mailing permissions need to be calculated
from the mailing permissions of the individuals. Thanks. Now that you point
that out it seems obvious.

As to how I got a job designing a database when it's clearly way over my
head...long story, but I'm stuck with it. I'll just have to learn. Thanks
for helping.

I don't know what your level of expertise is, but if you get stuck on
anything, post it here. 95% of us are happy to help - I have had some
really tricky things sorted out by people on c.d.m-a (For example, if
you want a hand with the SQL to determine various mailing options, put
up the table designs and the requirements and I'm sure you'll get what
you want)

Edward
--
The reading group's reading group
http://www.bookgroup.org.uk