By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 3,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

family database

P: n/a
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 ?

Jul 21 '07 #1
Share this Question
Share on Google+
18 Replies


P: n/a
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
re******@pcdatasheet.com
"Cliff Chapin" <cc******@cox.netwrote in message
news:d4****************@newsfe10.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 ?

Jul 21 '07 #2

P: n/a
On Sat, 21 Jul 2007 22:49:59 GMT, "Steve" <So***@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
re******@pcdatasheet.com
"Cliff Chapin" <cc******@cox.netwrote in message
news:d4****************@newsfe10.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 ?
Jul 22 '07 #3

P: n/a
"Cliff Chapin" <cc******@cox.netwrote:
>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/
Jul 22 '07 #4

P: n/a
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
re******@pcdatasheet.com

"Tom van Stiphout" <no*************@cox.netwrote in message
news:u1********************************@4ax.com...
On Sat, 21 Jul 2007 22:49:59 GMT, "Steve" <So***@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
re******@pcdatasheet.com
"Cliff Chapin" <cc******@cox.netwrote in message
news:d4****************@newsfe10.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 ?

Jul 23 '07 #5

P: n/a
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
re******@pcdatasheet.com

"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:cu********************************@4ax.com...
"Cliff Chapin" <cc******@cox.netwrote:
>>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/

Jul 23 '07 #6

P: n/a
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" <So***@private.emailaddresswrote in message
news:Yq****************@newsread2.news.pas.earthli nk.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]" <tt****@telusplanet.netwrote in message
news:cu********************************@4ax.com...
>"Cliff Chapin" <cc******@cox.netwrote:
>>>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/
Jul 23 '07 #7

P: n/a
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
re******@pcdatasheet.com


"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@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" <So***@private.emailaddresswrote in message
news:Yq****************@newsread2.news.pas.earthli nk.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]" <tt****@telusplanet.netwrote in message
news:cu********************************@4ax.com.. .
>>"Cliff Chapin" <cc******@cox.netwrote:

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/

Jul 23 '07 #8

P: n/a
"Steve" <So***@private.emailaddresswrote in
news:Yq****************@newsread2.news.pas.earthli nk.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(marriages) 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
re******@pcdatasheet.com

"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:cu********************************@4ax.com...
>"Cliff Chapin" <cc******@cox.netwrote:
>>>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

Jul 23 '07 #9

P: n/a
"Steve" <So***@private.emailaddresswrote in
news:zn****************@newsread2.news.pas.earthli nk.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
re******@pcdatasheet.com

"Tom van Stiphout" <no*************@cox.netwrote in message
news:u1********************************@4ax.com...
>On Sat, 21 Jul 2007 22:49:59 GMT, "Steve"
<So***@private.emailaddresswrote:

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
re******@pcdatasheet.com
"Cliff Chapin" <cc******@cox.netwrote in message
news:d4****************@newsfe10.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

Jul 23 '07 #10

P: n/a
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.FamilyID
PersonID relates to tblPerson.PersonID
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" <So***@private.emailaddresswrote in message
news:Mf******************@newsread1.news.pas.earth link.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" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@per-qv1-newsreader-01.iinet.net.au...
>Steve, I don't think you understood Tony's response.

"Steve" <So***@private.emailaddresswrote in message
news:Yq****************@newsread2.news.pas.earthl ink.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]" <tt****@telusplanet.netwrote in message
news:cu********************************@4ax.com. ..
"Cliff Chapin" <cc******@cox.netwrote:

>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.
Jul 23 '07 #11

P: n/a
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
re******@pcdatasheet.com

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@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.FamilyID
PersonID relates to tblPerson.PersonID
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" <So***@private.emailaddresswrote in message
news:Mf******************@newsread1.news.pas.earth link.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" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@per-qv1-newsreader-01.iinet.net.au...
>>Steve, I don't think you understood Tony's response.

"Steve" <So***@private.emailaddresswrote in message
news:Yq****************@newsread2.news.pas.earth link.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]" <tt****@telusplanet.netwrote in message
news:cu********************************@4ax.com ...
"Cliff Chapin" <cc******@cox.netwrote:
>
>>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.

Jul 23 '07 #12

P: n/a
Maybe in your family there are children having babies and maybe inbreeding
but that is not the typical family.

"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...
"Steve" <So***@private.emailaddresswrote in
news:zn****************@newsread2.news.pas.earthli nk.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
re******@pcdatasheet.com

"Tom van Stiphout" <no*************@cox.netwrote in message
news:u1********************************@4ax.com.. .
>>On Sat, 21 Jul 2007 22:49:59 GMT, "Steve"
<So***@private.emailaddresswrote:

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
re******@pcdatasheet.com
"Cliff Chapin" <cc******@cox.netwrote in message
news:d4****************@newsfe10.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

Jul 23 '07 #13

P: n/a

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.
Jul 23 '07 #14

P: n/a
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
re******@pcdatasheet.com


"Arch" <se*****@spam.netwrote in message
news:42********************************@4ax.com...
>
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.

Jul 23 '07 #15

P: n/a
"Steve" <So***@private.emailaddresswrote:
>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.
No, I meant a family table with basically an address and phone number on it.
Possibly a last name. No, more likely the "head of the household", however the
family defines it. Then a family member table with each adult and child in that
table.

I may not have phrased it or explained it very well though in hindsight.

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/
Jul 23 '07 #16

P: n/a
Welcome to the world of steve.

John... Visio MVP

"Arch" <se*****@spam.netwrote in message
news:42********************************@4ax.com...
>
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.

Jul 23 '07 #17

P: n/a
"Tony Toews [MVP]" <tt****@telusplanet.netwrote
I may not have phrased it or explained
it very well though in hindsight.
You can't phrase or explain something well enough that someone looking for
an argument won't somehow try to argue about it, even if that means
completely redefining what you said. I'd have thought that, by now, Steve
would have learned his lesson and would know better than to try to start a
fight in this newsgroup.

Larry Linson
who's been in CDMA since casting a vote approving
its creation, and seen it survive far worse than Steve


Jul 24 '07 #18

P: n/a
You seem to be crediting steve with some form of intelligence. He is just a
script kidding who subscribes to the Monty Python school of arguments and
abhors spoiling an argument with simple things like truth or facts.

John... Visio MVP

"Larry Linson" <bo*****@localhost.notwrote in message
news:1Nbpi.2827$Da.361@trnddc07...
I'd have thought that, by now, Steve would have learned his lesson and
would know better than to try to start a fight in this newsgroup.

Larry Linson
who's been in CDMA since casting a vote approving
its creation, and seen it survive far worse than Steve

Jul 24 '07 #19

This discussion thread is closed

Replies have been disabled for this discussion.