Re: Connecting to the Same Table Twice
- From: "Al Williams" <AlWilliams@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 25 May 2005 07:28:04 -0700
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
"tina" wrote:
> 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
> >
> >
>
>
>
.
- References:
- Connecting to the Same Table Twice
- From: Joy M
- Re: Connecting to the Same Table Twice
- From: tina
- Connecting to the Same Table Twice
- Prev by Date: Access form not connected to table
- Next by Date: Re: Linking with Contacts from Outlook
- Previous by thread: Re: Connecting to the Same Table Twice
- Next by thread: Re: Connecting to the Same Table Twice
- Index(es):
Relevant Pages
|
