self-joint tables

[saralee]

Hi everyone,

I might have a data structure issue here, since my search function does not work properly.. so here is what i have so far-

i have tblHousehold and two baby tables tblBabies and tblBabies2, and these are self-joint with the indication that there are siblings who belong to the same household, so they have the same household IDs but different baby IDs.. I wonder if this self-joint structure is proper or not.. They have the exactly same fields, and only tblBabies links with other related tables such as tblLanguages and tblBabyEthnicity ..

Any ideas? Thanks a lot!

[jamesducker]

If I understand correctly, I'd have two TABLES:

tblHouseholds

tblBabies

But three TABLE OCCURRENCES in all:

1. tblOccHouseholds

2. tblOccBabies

Based on the tblBabies table. Each of these babies would have a household ID. There may be many babies in a household but all the babies go in one table.

3. tblOccBabies_siblings

Also based on the tblBabies table.

The join from tblBabies to tblBabies_siblings would be:

household ID = household ID

baby ID baby ID

Then from the context of any record viewed on tblOccBabies, the details of that baby's siblings would be returned by tblOccBabies_siblings.

Hope that helps.

Out of interest, why are you cataloguing babies?

James

[saralee]

Thanks, James. i did exactly the same thing as what you described here, so i suppose my data structure is good. i gonna look into other areas to solve my search function mystery then.. hmm...

Oh yeah, i'm working on a database for a baby studies center. haha it's quite cheerful here except the database is giving me much headache.. i'm learning.. :

btw, can you think of any reason that might be causing my search mystery? my org post is here.

http://www.fmforums.com/forum/showtopic.php?tid/216740/fromactivity/mytopics/

Thanks!