Connecting to the Same Table Twice

[Joy M]

Hi there -

I am designing a database for a nursery school. Info is collected for each
child, and for 2 parents of this child.
Parents have a unique set of data which is different from Child data.

What I am wondering is - Should I build a separate tblParent or include the
parent information as part of the child record ??

Initially I had tblChild and tblParent. There were fields, Parent1 and
Parent2, in tblChild which were foreign keys to tblParent.
But Access didn't like this setup, because it duplicated tblParent in the
Relationships diagram, when I tried to connect to it twice.
That is, it made tblParent and tblParent_1 on my relationship diagram.

Then I thought about putting the Parent information in the Child record,
because most of the time you are collecting info about 2 parents.
But it makes a really long child record, which is sort of hard to work with.

Should I be using a Junction table - with just the ChildID and the
ParentID?? One child can have > 1 parents. One parent can have > 1
children.

There is also information like emergency contact information and medical
information pertaining to the child. Should I store it in the child record,
or put it in
separate tables and link to it?

So I am having difficulty building my tables - what goes where ??!!

Your input will be greatly appreciated -- Thanks!

Joy

 

[tina]

suggest you start with:

tblChildren
ChildID (primary key)
CFirstName
CLastName
DateOfBirth
Gender
(other fields that describe the *child*)

tblRelationships
RelationshipID (primary key)
RelationshipName (parent, guardian, authorized pick-up, emergency contact,
etc)

tblCaregivers
CaregiverID (primary key)
GFirstName
GLastName
RelationshipID (foreign key from tblRelationships)
SpouseFirstName
SpouseLastName
Address1
Address2
City
State
Zip
if the parents live in the same household, you can fill in the spouse names
rather than creating an almost duplicate parent record for the spouse (or
you might put spouse names into a separate table linked to this table). if
the parents live in separate households, you would enter a Parent record for
each parent.
from here you may need one or more additional tables to hold the Caregiver
data. for instance, one caregiver might have multiple contact numbers -
home, work, cell, for instance. so you'd want a separate table for phone
numbers, again linked to this table. if you don't collect address
information on any caregiver except the parent(s), then suggest you put
address info into a separate table also.

tblChildCaregivers
ChildID (foreign key from tblChildren)
CaregiverID (foreign key from tblCaregivers)
(you can use the two fk fields as a combination primary key for this table,
or add a separate primary key field - probably an Autonumber.)
for each link between a child record and a caregiver record, you will have
one record in this table. one advantage of this "linking table" setup: if
you have two children from the same family, you don't have to enter separate
caregiver records for each one in tblCaregivers; you just have to enter
separate records in this table.

any data about the child that may have multiple values, should be in a
separate table. for instance, a child may have multiple physical conditions:
asthma and milk allergy, for instance. or be taking multiple medications.

your db will store data that's vital to the health and safety of the
children in the nursery school's care. so you need to make every effort to
build the db right "from the ground up", so it's accurate and reliable and
easy to use. i strongly recommend that you learn the basics of data
normalization and tables/relationships design guidelines. the time you
invest now will be repaid a thousand-fold as you build the rest of the
database on top of a properly structured foundation of tables/relationships.
for tons of info on these subjects, and other aspects of db design, see
http://www.ltcomputerdesigns.com/JCReferences.html
looking first at the "Database Design 101" and "Starting Out" links.

hth

 

[Al Williams]

As you look into database normalization, you may want to consider Allen
Browne’s modeling of human relationships at
http://allenbrowne.com/AppHuman.html

Also, Michelle Poolet’s article on supertypes and subtypes at
http://www.windowsitpro.com/SQLServer/Article/ArticleID/5226/5226.html

Hope that helps.

Al Williams

 

[Joy M]

Hi there -

Thanks, tina, for your input. You saved me a lot of (my precious valuable)
time!!

OK, another question, if you would be so kind. I think I know what to do,
but I would like your approval.

The report should look like this --
Info from tblChild
Info from query tblChild tblRelationships tblAdult
Info from query tblChild tblClass

The way I see it -
the basic child information,
all his relationships (e.g. parents, nanny, emergency contact, doctor)
the child's schedule of classes (they may go 3 mornings a week or 5
afternoons etc.)

So...question is...

Can I put these 3 reports into one. I am pretty sure I can, but I have
never done it before. I think I read about it somewhere.
(All I have done before is make reports from 1 table or a header table and a
details table.)

I think all the info would fit onto one page, but I see the info comes from
3 different sources. What is the best way of doing this?

Thanks !!! !!!

Joy

 

[tina]

well, reports are not my strongest area - i can usually figure out how to
get what i want after some fiddling, but not efficiently enough for me to
give very good suggestions in a newsgroup forum. i might start by building a
query based on tblChild and either one of the other two tables, linked on
the ChildID pk/fk fields. since both are "child" tables, to its' "parent",
probably doesn't really matter which child table you choose. base the report
on that query, grouping on the ChildID field. build a subreport based on the
third table. the main/sub reports will also be linked on the ChildID pk/fk
fields.

suggest you give it a try; then if you run into problems, post a specific
question in the microsoft.public.access.reports newsgroup for more (and
better) help than i'm likely to offer. include some specific information re
the table relationships, the report setup, and the specific problem you're
having - to help people help you.

hth