family database

[Cliff Chapin]

I want to create a Family database some of these "families " are single
fathers with children some are single women with children they will be
assigned Rooms /w children.
what would be the best to relate to the childrem ?

[Steve]

Re: family database

Parent(s) are always a one-to-many relationship to children. You will need
two tables; one for parent(s) and ine for children. You are going to have to
decide how you are going to indicate a parent is a single parent and
implement that in the parent table.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdata sheet.com


"Cliff Chapin" <ccchapin@cox.n etwrote in message
news:d4uoi.9506 $6K3.843@newsfe 10.phx...

>I want to create a Family database some of these "families " are single
>fathers with children some are single women with children they will be
>assigned Rooms /w children.
what would be the best to relate to the childrem ?
>
>
>

[Tom van Stiphout]

Re: family database

On Sat, 21 Jul 2007 22:49:59 GMT, "Steve" <Sorry@private. emailaddress>
wrote:

Not necessarily. Just think of the classic Employee table with a
ManagerID field and a self-join. Single table. Same applies here.

-Tom.

>Parent(s) are always a one-to-many relationship to children. You will need
>two tables; one for parent(s) and ine for children. You are going to have to
>decide how you are going to indicate a parent is a single parent and
>implement that in the parent table.
>
>PC Datasheet
>Providing Customers A Resource For Help With Access, Excel And Word
>Applications
>resource@pcdat asheet.com
>
>
>"Cliff Chapin" <ccchapin@cox.n etwrote in message
>news:d4uoi.950 6$6K3.843@newsf e10.phx...

>>I want to create a Family database some of these "families " are single
>>fathers with children some are single women with children they will be
>>assigned Rooms /w children.
>what would be the best to relate to the childrem ?
>>
>>
>>

>

[Tony Toews [MVP]]

Re: family database

"Cliff Chapin" <ccchapin@cox.n etwrote:

>I want to create a Family database some of these "families " are single
>fathers with children some are single women with children they will be
>assigned Rooms /w children.
>what would be the best to relate to the childrem ?

I'd create a family table and assign the parent(s) and children to the family.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Steve]

Re: family database

In the classic Employee table, managers are employees also so one table
suffices. In the OPs scenaro you can reasonably assume children CAN NOT be
parents so the two table approach is more appropriate.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdata sheet.com



"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
news:u1b5a3lrm4 6f4tjm5g0g6v42p fn328s31i@4ax.c om...

On Sat, 21 Jul 2007 22:49:59 GMT, "Steve" <Sorry@private. emailaddress>
wrote:
>
Not necessarily. Just think of the classic Employee table with a
ManagerID field and a self-join. Single table. Same applies here.
>
-Tom.
>
>

>>Parent(s) are always a one-to-many relationship to children. You will need
>>two tables; one for parent(s) and ine for children. You are going to have
>>to
>>decide how you are going to indicate a parent is a single parent and
>>implement that in the parent table.
>>
>>PC Datasheet
>>Providing Customers A Resource For Help With Access, Excel And Word
>>Application s
>>resource@pcda tasheet.com
>>
>>
>>"Cliff Chapin" <ccchapin@cox.n etwrote in message
>>news:d4uoi.95 06$6K3.843@news fe10.phx...

>>>I want to create a Family database some of these "families " are single
>>>fathers with children some are single women with children they will be
>>>assigned Rooms /w children.
>>what would be the best to relate to the childrem ?
>>>
>>>
>>>

>>

>

[Steve]

Re: family database

I hope this does not mislead the OP. Family to parent is a one-to-many
relationship and needs two tables. Family to children is also a one-to-many
relationship so this also needs two tables. The family solution thus needs a
TblFamily, TblParent and TblChild.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdata sheet.com



"Tony Toews [MVP]" <ttoews@teluspl anet.netwrote in message
news:cuj7a31rvg q16l79sde5qp7c0 easdhle8u@4ax.c om...

"Cliff Chapin" <ccchapin@cox.n etwrote:
>

>>I want to create a Family database some of these "families " are single
>>fathers with children some are single women with children they will be
>>assigned Rooms /w children.
>>what would be the best to relate to the childrem ?

>
I'd create a family table and assign the parent(s) and children to the
family.
>
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Allen Browne]

Re: family database

Steve, I don't think you understood Tony's response.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steve" <Sorry@private. emailaddresswro te in message
news:YqToi.9729 $rR.1264@newsre ad2.news.pas.ea rthlink.net...

>I hope this does not mislead the OP. Family to parent is a one-to-many
>relationship and needs two tables. Family to children is also a one-to-many
>relationship so this also needs two tables. The family solution thus needs
>a TblFamily, TblParent and TblChild.
>
"Tony Toews [MVP]" <ttoews@teluspl anet.netwrote in message
news:cuj7a31rvg q16l79sde5qp7c0 easdhle8u@4ax.c om...

>"Cliff Chapin" <ccchapin@cox.n etwrote:
>>

>>>I want to create a Family database some of these "families " are single
>>>fathers with children some are single women with children they will be
>>>assigned Rooms /w children.
>>>what would be the best to relate to the childrem ?

>>
>I'd create a family table and assign the parent(s) and children to the
>family.
>>
>Tony
>--
>Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
>read the entire thread of messages.
> Microsoft Access Links, Hints, Tips & Accounting Systems at
>http://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Steve]

Re: family database

I took Tony's response to say a Family table with Parent1 and Parent2 fields
and Child1, Child2, Child3, etc fields. In the OP's scenario, it is
reasonable to assume he means young children who can njot also be parents.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdata sheet.com




"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
news:46a407a4$0 $31437$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...

Steve, I don't think you understood Tony's response.
>
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>
"Steve" <Sorry@private. emailaddresswro te in message
news:YqToi.9729 $rR.1264@newsre ad2.news.pas.ea rthlink.net...

>>I hope this does not mislead the OP. Family to parent is a one-to-many
>>relationshi p and needs two tables. Family to children is also a
>>one-to-many relationship so this also needs two tables. The family
>>solution thus needs a TblFamily, TblParent and TblChild.
>>
>"Tony Toews [MVP]" <ttoews@teluspl anet.netwrote in message
>news:cuj7a31rv gq16l79sde5qp7c 0easdhle8u@4ax. com...

>>"Cliff Chapin" <ccchapin@cox.n etwrote:
>>>
>>>>I want to create a Family database some of these "families " are single
>>>>fathers with children some are single women with children they will be
>>>>assigned Rooms /w children.
>>>>what would be the best to relate to the childrem ?
>>>
>>I'd create a family table and assign the parent(s) and children to the
>>family.
>>>
>>Tony
>>--
>>Tony Toews, Microsoft Access MVP
>> Please respond only in the newsgroups so that others can
>>read the entire thread of messages.
>> Microsoft Access Links, Hints, Tips & Accounting Systems at
>>http://www.granite.ab.ca/accsmstr.htm
>> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

>

[Bob Quintal]

Re: family database

"Steve" <Sorry@private. emailaddresswro te in
news:YqToi.9729 $rR.1264@newsre ad2.news.pas.ea rthlink.net:

I hope this does not mislead the OP. Family to parent is a
one-to-many relationship and needs two tables. Family to
children is also a one-to-many relationship so this also needs
two tables. The family solution thus needs a TblFamily,
TblParent and TblChild.
>

You are wrong again. You need a table for people, and a table
for unions(marriage s) and a table for children. People can be
parents of children and children of parents.

The unions table contains a candidate key of HusbandID and
WifeID, each tied to the PersonID in the people table

The Children table contains a foreign key to the PersonID in the
People table and foreign keys to the unions table.

Your way does not handle second mariages nor multiple
generations.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And
Word Applications
resource@pcdata sheet.com
>
>
>
"Tony Toews [MVP]" <ttoews@teluspl anet.netwrote in message
news:cuj7a31rvg q16l79sde5qp7c0 easdhle8u@4ax.c om...

>"Cliff Chapin" <ccchapin@cox.n etwrote:
>>

>>>I want to create a Family database some of these "families "
>>>are single fathers with children some are single women with
>>>children they will be assigned Rooms /w children.
>>>what would be the best to relate to the childrem ?

>>
>I'd create a family table and assign the parent(s) and
>children to the family.
>>
>Tony
>--
>Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
>read the entire thread of messages.
> Microsoft Access Links, Hints, Tips & Accounting Systems at
>http://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog -
> http://msmvps.com/blogs/access/

>
>
>

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

Re: family database

"Steve" <Sorry@private. emailaddresswro te in
news:znToi.9728 $rR.8824@newsre ad2.news.pas.ea rthlink.net:

In the classic Employee table, managers are employees also so
one table suffices. In the OPs scenaro you can reasonably
assume children CAN NOT be parents so the two table approach
is more appropriate.

You can assume anything you like. You are the one making an ASS
out of U, not ME.

>
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And
Word Applications
resource@pcdata sheet.com
>
>
>
"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
news:u1b5a3lrm4 6f4tjm5g0g6v42p fn328s31i@4ax.c om...

>On Sat, 21 Jul 2007 22:49:59 GMT, "Steve"
><Sorry@private .emailaddresswr ote:
>>
>Not necessarily. Just think of the classic Employee table
>with a ManagerID field and a self-join. Single table. Same
>applies here.
>>
>-Tom.
>>
>>

>>>Parent(s) are always a one-to-many relationship to children.
>>>You will need two tables; one for parent(s) and ine for
>>>children. You are going to have to
>>>decide how you are going to indicate a parent is a single
>>>parent and implement that in the parent table.
>>>
>>>PC Datasheet
>>>Providing Customers A Resource For Help With Access, Excel
>>>And Word Applications
>>>resource@pcd atasheet.com
>>>
>>>
>>>"Cliff Chapin" <ccchapin@cox.n etwrote in message
>>>news:d4uoi.9 506$6K3.843@new sfe10.phx...
>>>>I want to create a Family database some of these "families "
>>>>are single fathers with children some are single women with
>>>>children they will be assigned Rooms /w children.
>>>what would be the best to relate to the childrem ?
>>>>
>>>>
>>>>
>>>

>>

>
>
>

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Allen Browne]

Re: family database

I hope that was intended as a joke, Steve.

There wasn't a smiley, so just in case you are seriously suggesting this, it
would be close to the worst design imaginable. Repeating fields (Child1,
Child2, ...) represent a fundamental violation of normalization techniques.
You can't even create a sensible set of relations between those tables
because of the number of fields you need to join. Or (if it's all one
flat-file table), you have no idea which field to search to find a child.

The most basic approach to "assigning the parents and children to the
family" would be something like this:

tblPerson (parents and children go here):
PersonID
Surname
Firstname

tblFamily (one record for each family):
FamilyID
FamilyName

tblFamilyPerson (one record for each person in each family):
FamilyID relates to tblFamily.Famil yID
PersonID relates to tblPerson.Perso nID
RoleID this person's role in this family (parent/guardian or
child)
PK would be FamilyID + PersonID.
So, if there are 2 parents and 3 kids in a "family", this table has 5
records with the same FamilyID.

An alternative approach (perhaps better suited for genealogical data) would
be skip the idea of families and record the relations between people
instead. It would then be possible to imply other relationships, e.g. if
John Smith is the father of Mary Smith and also the father of James Smith,
then James and Mary have a brother-sister relation (or perhaps half-brother,
step-brother, ...)

It depends whether the OP needs to store information about relationships
between individuals (the fact that A has legal responsibility for B) or
about households (which persons are regularly under the same roof.)

Finally, for Cliff: you may be finding this all very confusing as an answer
to your question. You might want to download the example in this article and
see if it helps you think through how this might be done:
People in households and companies - modeling human relationships
at:

http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steve" <Sorry@private. emailaddresswro te in message
news:MfUoi.1084 5$Od7.4557@news read1.news.pas. earthlink.net.. .

>I took Tony's response to say a Family table with Parent1 and Parent2
>fields and Child1, Child2, Child3, etc fields. In the OP's scenario, it is
>reasonable to assume he means young children who can njot also be parents.
>
>
"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
news:46a407a4$0 $31437$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...

>Steve, I don't think you understood Tony's response.
>>
>"Steve" <Sorry@private. emailaddresswro te in message
>news:YqToi.972 9$rR.1264@newsr ead2.news.pas.e arthlink.net...

>>>I hope this does not mislead the OP. Family to parent is a one-to-many
>>>relationsh ip and needs two tables. Family to children is also a
>>>one-to-many relationship so this also needs two tables. The family
>>>solution thus needs a TblFamily, TblParent and TblChild.
>>>
>>"Tony Toews [MVP]" <ttoews@teluspl anet.netwrote in message
>>news:cuj7a31r vgq16l79sde5qp7 c0easdhle8u@4ax .com...
>>>"Cliff Chapin" <ccchapin@cox.n etwrote:
>>>>
>>>>>I want to create a Family database some of these "families " are single
>>>>>fathers with children some are single women with children they will be
>>>>>assigned Rooms /w children.
>>>>>what would be the best to relate to the childrem ?
>>>>
>>>I'd create a family table and assign the parent(s) and children to the
>>>family.

[Steve]

Re: family database

Allen,

It was no joke! I was not suggesting that design, I took Tony's
recomendation to mean that design. I don't agree with a design like that at
all!!

Take a look at the Op's original post. He IS NOT looking for a genealogical
database. It appears he is looking for some type of registration database to
assign families to rooms. If the OP assigns a parent to a room, the parent's
children also get assigned to the same room. He doesn't care about
grandparents, aunts, uncles, etc.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdata sheet.com





"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
news:46a41b56$0 $31422$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...

>I hope that was intended as a joke, Steve.
>
There wasn't a smiley, so just in case you are seriously suggesting this,
it would be close to the worst design imaginable. Repeating fields
(Child1, Child2, ...) represent a fundamental violation of normalization
techniques. You can't even create a sensible set of relations between
those tables because of the number of fields you need to join. Or (if it's
all one flat-file table), you have no idea which field to search to find a
child.
>
The most basic approach to "assigning the parents and children to the
family" would be something like this:
>
tblPerson (parents and children go here):
PersonID
Surname
Firstname
>
tblFamily (one record for each family):
FamilyID
FamilyName
>
tblFamilyPerson (one record for each person in each family):
FamilyID relates to tblFamily.Famil yID
PersonID relates to tblPerson.Perso nID
RoleID this person's role in this family (parent/guardian or
child)
PK would be FamilyID + PersonID.
So, if there are 2 parents and 3 kids in a "family", this table has 5
records with the same FamilyID.
>
An alternative approach (perhaps better suited for genealogical data)
would be skip the idea of families and record the relations between people
instead. It would then be possible to imply other relationships, e.g. if
John Smith is the father of Mary Smith and also the father of James Smith,
then James and Mary have a brother-sister relation (or perhaps
half-brother, step-brother, ...)
>
It depends whether the OP needs to store information about relationships
between individuals (the fact that A has legal responsibility for B) or
about households (which persons are regularly under the same roof.)
>
Finally, for Cliff: you may be finding this all very confusing as an
answer to your question. You might want to download the example in this
article and see if it helps you think through how this might be done:
People in households and companies - modeling human relationships
at:

http://allenbrowne.com/AppHuman.html

>
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>
"Steve" <Sorry@private. emailaddresswro te in message
news:MfUoi.1084 5$Od7.4557@news read1.news.pas. earthlink.net.. .

>>I took Tony's response to say a Family table with Parent1 and Parent2
>>fields and Child1, Child2, Child3, etc fields. In the OP's scenario, it is
>>reasonable to assume he means young children who can njot also be parents.
>>
>>
>"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
>news:46a407a4$ 0$31437$5a62ac2 2@per-qv1-newsreader-01.iinet.net.au ...

>>Steve, I don't think you understood Tony's response.
>>>
>>"Steve" <Sorry@private. emailaddresswro te in message
>>news:YqToi.97 29$rR.1264@news read2.news.pas. earthlink.net.. .
>>>>I hope this does not mislead the OP. Family to parent is a one-to-many
>>>>relationshi p and needs two tables. Family to children is also a
>>>>one-to-many relationship so this also needs two tables. The family
>>>>solution thus needs a TblFamily, TblParent and TblChild.
>>>>
>>>"Tony Toews [MVP]" <ttoews@teluspl anet.netwrote in message
>>>news:cuj7a31 rvgq16l79sde5qp 7c0easdhle8u@4a x.com...
>>>>"Cliff Chapin" <ccchapin@cox.n etwrote:
>>>>>
>>>>>>I want to create a Family database some of these "families " are
>>>>>>single
>>>>>>fathers with children some are single women with children they will be
>>>>>>assigne d Rooms /w children.
>>>>>>what would be the best to relate to the childrem ?
>>>>>
>>>>I'd create a family table and assign the parent(s) and children to the
>>>>family.

>

[Steve]

Re: family database

Maybe in your family there are children having babies and maybe inbreeding
but that is not the typical family.



"Bob Quintal" <rquintal@sPAmp atico.cawrote in message
news:Xns9975E52 303625BQuintal@ 66.150.105.47.. .

"Steve" <Sorry@private. emailaddresswro te in
news:znToi.9728 $rR.8824@newsre ad2.news.pas.ea rthlink.net:
>

>In the classic Employee table, managers are employees also so
>one table suffices. In the OPs scenaro you can reasonably
>assume children CAN NOT be parents so the two table approach
>is more appropriate.

>
You can assume anything you like. You are the one making an ASS
out of U, not ME.
>

>>
>PC Datasheet
>Providing Customers A Resource For Help With Access, Excel And
>Word Applications
>resource@pcdata sheet.com
>>
>>
>>
>"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
>news:u1b5a3lrm 46f4tjm5g0g6v42 pfn328s31i@4ax. com...

>>On Sat, 21 Jul 2007 22:49:59 GMT, "Steve"
>><Sorry@privat e.emailaddressw rote:
>>>
>>Not necessarily. Just think of the classic Employee table
>>with a ManagerID field and a self-join. Single table. Same
>>applies here.
>>>
>>-Tom.
>>>
>>>
>>>>Parent(s) are always a one-to-many relationship to children.
>>>>You will need two tables; one for parent(s) and ine for
>>>>children. You are going to have to
>>>>decide how you are going to indicate a parent is a single
>>>>parent and implement that in the parent table.
>>>>
>>>>PC Datasheet
>>>>Providing Customers A Resource For Help With Access, Excel
>>>>And Word Applications
>>>>resource@pc datasheet.com
>>>>
>>>>
>>>>"Cliff Chapin" <ccchapin@cox.n etwrote in message
>>>>news:d4uoi. 9506$6K3.843@ne wsfe10.phx...
>>>>>I want to create a Family database some of these "families "
>>>>>are single fathers with children some are single women with
>>>>>children they will be assigned Rooms /w children.
>>>>what would be the best to relate to the childrem ?
>>>>>
>>>>>
>>>>>
>>>>
>>>

>>
>>
>>

>
>
>
--
Bob Quintal
>
PA is y I've altered my email address.
>
--
Posted via a free Usenet account from http://www.teranews.com
>

[Arch]

Re: family database


Geez resource. How on earth did you make this assumption:

>I took Tony's response to say a Family table with Parent1 and Parent2 fields
>and Child1, Child2, Child3, etc fields. In the OP's scenario, it is
>reasonable to assume he means young children who can njot also be parents.

From this:

>>>I'd create a family table and assign the parent(s) and children to the
>>>family.

You are defending the poor solution that you recommended with
arguments that are totally illogical. The solution that Tony (and
others) recommend doesn't build in the clumsy limitations that yours
does.

[Steve]

Re: family database

Geez arch. can't you read? The OP said "..... will be assigned Rooms /w
children." The OP isn'tooking for a geneaology database. It appears he is
looking for some type of registration database for assignin families with
small children to rooms. If the OP assigns a parent to a room, the parent's
children also get assigned to the same room. He doesn't care about
grandparents, aunts, uncles, etc.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdata sheet.com






"Arch" <send.no@spam.n etwrote in message
news:42h9a3p4ng jfvgcgihim9ghc6 bst92ksnc@4ax.c om...

>
Geez resource. How on earth did you make this assumption:
>

>>I took Tony's response to say a Family table with Parent1 and Parent2
>>fields
>>and Child1, Child2, Child3, etc fields. In the OP's scenario, it is
>>reasonable to assume he means young children who can njot also be parents.

>
From this:
>

>>>>I'd create a family table and assign the parent(s) and children to the
>>>>family.

>
You are defending the poor solution that you recommended with
arguments that are totally illogical. The solution that Tony (and
others) recommend doesn't build in the clumsy limitations that yours
does.