Re: Connecting to the Same Table Twice
- From: "tina" <nospam@xxxxxxxxxxx>
- Date: Tue, 24 May 2005 18:53:17 GMT
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
"Joy M" <ab520@xxxxxxxxxxxxxxx> wrote in message
news:eYJke.35768$Ph4.703334@xxxxxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>
.
- Follow-Ups:
- Re: Connecting to the Same Table Twice
- From: Al Williams
- Re: Connecting to the Same Table Twice
- References:
- Connecting to the Same Table Twice
- From: Joy M
- Connecting to the Same Table Twice
- Prev by Date: Connecting to the Same Table Twice
- Next by Date: How do I format text in proper noun format?
- Previous by thread: Connecting to the Same Table Twice
- Next by thread: Re: Connecting to the Same Table Twice
- Index(es):
Relevant Pages
|
