Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

many-to-many setup quandry

Discussion in 'Information Technology' started by betwalk@gmail.com, Jul 28, 2006.

  1. betwalk@gmail.com

    betwalk@gmail.com
    Expand Collapse
    Guest

    Hi everybody-

    Let's see if I can ask this as simply as possible.

    Tables in an existing system:

    tblContacts
    ----------------
    ContactID=pk
    assorted fields like fname, Lname, etc.

    tblChildren
    ---------------
    ChildID=pk
    ContactID=fk
    assorted fields like fname, age etc.

    tblDogs (this table is new)
    ---------------------
    DogID=pk
    assorted fields like DogName, breed, etc.

    tblJoin (this table is also new)
    ----------
    JoinID=pk
    DogID=fk
    contactID=fk

    This is being used by an organization that trains service dogs.
    Orginally this database just tracked donors and their family info (yes,
    there is a separate table for donations...) They've asked me to help
    them expand it to include tracking information about dogs, now, as
    well. The purpose of the join table is to help link the various people
    who come through these dogs' lives until they are placed with a client.
    (ie: breeder, donor, puppy raiser, trainer, etc.) Some people are
    adults and sometimes a participant is a student trainer, coming from
    the children table.

    I got everything set up beautifully and working very well, until I
    remembered the kids. I forgot the kids and had only set up
    relationship between the dogs and the tblContacts. My heart sank!

    I'm confused about how to structure the join table so that I can have a
    many-to-many relationship between the dogs and ALL the possible people,
    not just the adults. Can I add ChildID as a foreign key to the
    tblJoin, allowing a possible many-to-many relationship between dogs and
    children? But then relationship rules would constantly be broken, since
    either ContactID or ChildID would have to be left empty each time a
    record is entered to this table.

    Is there some way for me to combine tblContacts and tblChildren and
    then set up the many-to-many relationship between tblDogs and this new
    dataset?

    I'm kinda stumbling around here. I'd be grateful for any guidance on
    this-

    Thanks- Betsy
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Employment generation board setup in Punjab (New Kerala) Breaking News Sep 17, 2007

  3. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    You may not like the advice, but what you need to do is merge tblContacts
    and tblChildren, and use data in the Table to determine (when determination
    is needed, which is unlikely to be every time it is accessed) whether the
    Contact is a child or an adult.

    With enough work, you could probably make it work with the unnormalized
    structure, but it'd be extra work over and over again in the future, too.

    Larry Linson
    Microsoft Access MVP

    <betwalk@gmail.com> wrote in message
    news:1153362873.418591.133270@h48g2000cwc.googlegroups.com...
    > Hi everybody-
    >
    > Let's see if I can ask this as simply as possible.
    >
    > Tables in an existing system:
    >
    > tblContacts
    > ----------------
    > ContactID=pk
    > assorted fields like fname, Lname, etc.
    >
    > tblChildren
    > ---------------
    > ChildID=pk
    > ContactID=fk
    > assorted fields like fname, age etc.
    >
    > tblDogs (this table is new)
    > ---------------------
    > DogID=pk
    > assorted fields like DogName, breed, etc.
    >
    > tblJoin (this table is also new)
    > ----------
    > JoinID=pk
    > DogID=fk
    > contactID=fk
    >
    > This is being used by an organization that trains service dogs.
    > Orginally this database just tracked donors and their family info (yes,
    > there is a separate table for donations...) They've asked me to help
    > them expand it to include tracking information about dogs, now, as
    > well. The purpose of the join table is to help link the various people
    > who come through these dogs' lives until they are placed with a client.
    > (ie: breeder, donor, puppy raiser, trainer, etc.) Some people are
    > adults and sometimes a participant is a student trainer, coming from
    > the children table.
    >
    > I got everything set up beautifully and working very well, until I
    > remembered the kids. I forgot the kids and had only set up
    > relationship between the dogs and the tblContacts. My heart sank!
    >
    > I'm confused about how to structure the join table so that I can have a
    > many-to-many relationship between the dogs and ALL the possible people,
    > not just the adults. Can I add ChildID as a foreign key to the
    > tblJoin, allowing a possible many-to-many relationship between dogs and
    > children? But then relationship rules would constantly be broken, since
    > either ContactID or ChildID would have to be left empty each time a
    > record is entered to this table.
    >
    > Is there some way for me to combine tblContacts and tblChildren and
    > then set up the many-to-many relationship between tblDogs and this new
    > dataset?
    >
    > I'm kinda stumbling around here. I'd be grateful for any guidance on
    > this-
    >
    > Thanks- Betsy
    >
     
  4. betwalk@gmail.com

    betwalk@gmail.com
    Expand Collapse
    Guest

    Larry Linson wrote:
    > You may not like the advice, but what you need to do is merge tblContacts
    > and tblChildren, and use data in the Table to determine (when determination
    > is needed, which is unlikely to be every time it is accessed) whether the
    > Contact is a child or an adult.
    >
    > With enough work, you could probably make it work with the unnormalized
    > structure, but it'd be extra work over and over again in the future, too.
    >
    > Larry Linson
    > Microsoft Access MVP

    ---------------------------------------
    Hi Larry-

    Thanks for this quick reply! Are you saying that I can merge this data
    via a query for the purpose of the join? Or are you suggesting that
    the original structure needs to be changed with parents and their
    children all in one table...?

    Sorry - but I'm not catching your full meaning. Can you explain a
    little more?

    Betsy
     
  5. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    There should be one "people" Table, with both adults and children, and some
    way to distinguish between them for those cases where that is needed -- that
    will be simpler if you have a date-of-birth Field.

    (If, instead, you put an adult/child Field, then you let yourself in for
    maintenance chores when a child reaches the age to be considered an
    adult -- you have to know when that happens, and run a Query to update the
    adult/child field.)

    Larry Linson
    Microsoft Access MVP


    <betwalk@gmail.com> wrote in message
    news:1153365352.382959.319300@75g2000cwc.googlegroups.com...
    > Larry Linson wrote:
    >> You may not like the advice, but what you need to do is merge tblContacts
    >> and tblChildren, and use data in the Table to determine (when
    >> determination
    >> is needed, which is unlikely to be every time it is accessed) whether the
    >> Contact is a child or an adult.
    >>
    >> With enough work, you could probably make it work with the unnormalized
    >> structure, but it'd be extra work over and over again in the future, too.
    >>
    >> Larry Linson
    >> Microsoft Access MVP

    > ---------------------------------------
    > Hi Larry-
    >
    > Thanks for this quick reply! Are you saying that I can merge this data
    > via a query for the purpose of the join? Or are you suggesting that
    > the original structure needs to be changed with parents and their
    > children all in one table...?
    >
    > Sorry - but I'm not catching your full meaning. Can you explain a
    > little more?
    >
    > Betsy
    >
     
  6. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    It might be simpler to put the adult/child state in the join table as it may
    be necessary to know what the person was when they had contact with the dog,
    rather than what they are now.

    Possibly a DOB field in the people table and then record the person type
    when the relationship is made?

    So something like

    tblContacts
    ----------------
    ContactID=pk
    assorted fields like fname, Lname, etc.
    dob

    tblDogs (this table is new)
    ---------------------
    DogID=pk
    assorted fields like DogName, breed, etc

    tblJoin (this table is also new)
    ----------
    DogID=fk
    contactID=fk
    contactType



    --

    Terry Kreft


    "Larry Linson" <bouncer@localhost.not> wrote in message
    news:eUzM2A7qGHA.4492@TK2MSFTNGP05.phx.gbl...
    > There should be one "people" Table, with both adults and children, and

    some
    > way to distinguish between them for those cases where that is needed --

    that
    > will be simpler if you have a date-of-birth Field.
    >
    > (If, instead, you put an adult/child Field, then you let yourself in for
    > maintenance chores when a child reaches the age to be considered an
    > adult -- you have to know when that happens, and run a Query to update the
    > adult/child field.)
    >
    > Larry Linson
    > Microsoft Access MVP
    >
    >
    > <betwalk@gmail.com> wrote in message
    > news:1153365352.382959.319300@75g2000cwc.googlegroups.com...
    > > Larry Linson wrote:
    > >> You may not like the advice, but what you need to do is merge

    tblContacts
    > >> and tblChildren, and use data in the Table to determine (when
    > >> determination
    > >> is needed, which is unlikely to be every time it is accessed) whether

    the
    > >> Contact is a child or an adult.
    > >>
    > >> With enough work, you could probably make it work with the unnormalized
    > >> structure, but it'd be extra work over and over again in the future,

    too.
    > >>
    > >> Larry Linson
    > >> Microsoft Access MVP

    > > ---------------------------------------
    > > Hi Larry-
    > >
    > > Thanks for this quick reply! Are you saying that I can merge this data
    > > via a query for the purpose of the join? Or are you suggesting that
    > > the original structure needs to be changed with parents and their
    > > children all in one table...?
    > >
    > > Sorry - but I'm not catching your full meaning. Can you explain a
    > > little more?
    > >
    > > Betsy
    > >

    >
    >
     
  7. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Terry Kreft wrote:
    > Possibly a DOB field in the people table


    The OP indicated that age is being captured. It could be that age
    coupled with the date captured (or even date entered into the DB) could
    be adequate (i.e. the timestamp + interval approach to modelling time
    in SQL) for determining whether the person is considered a child at any
    point in time during a dog's life. For simplicity, let's assume DOB is
    available...

    > > The purpose of the join table is to help link the various people
    > > who come through these dogs' lives until they are placed with a client.
    > > (ie: breeder, donor, puppy raiser, trainer, etc.) Some people are
    > > adults and sometimes a participant is a student trainer, coming from
    > > the children table.

    >
    > It might be simpler to put the adult/child state in the join table as it may
    > be necessary to know what the person was when they had contact with the dog,
    > rather than what they are now.


    I would not recommend the table name 'tblJoin'. Prefixes aside, I'd
    avoid an entity/relationship name of 'join'. How about 'Roles'?

    I think the OP omitted some necessary start and end date columns from
    this table if they are to fulfil the primary role "help link the
    various people who come through these dogs' lives until they are placed
    with a client".

    The implication is that a child can be a trainer but none of the other
    roles mentioned. Therefore, I agree the status of adult/child should be
    checked when the person is assigned a role.

    It is often said in these groups that a calculation should not be
    stored in a column. If DOB was available (or could be approximated
    based on timestamp + age), your adult/child state would seem to be an
    example of storing a calculation.

    An alternative is to store the DOB in the 'roles' table with
    identifier, of course, and a foreign key back to the 'people' table (or
    a multi-table CHECK constraint but DRI is generally preferred). This
    way CHECK constraints or Validation Rules could compare DOB with the
    start and end date columns e.g. to ensure a child isn't assigned the
    role 'breeder', to ensure someone isn't assigned a role before they
    were born, etc.

    I note your intention was to keep things simple and the lack DOB and
    start and end dates for roles suggests the OP may be merely interested
    in the roles, rather that when or in what order those roles were
    assigned (i.e. no temporal elements). In this case, having distinct
    roles for adults and children (e.g. 'trainer' and 'student trainer'
    respectively) could suffice.

    Jamie.

    --
     
  8. betwalk@gmail.com

    betwalk@gmail.com
    Expand Collapse
    Guest

    Wow, you all have given me a lot to think about!

    There are fields that I have not mentioned in all the tables described.
    All I outlined here were the pertinent fields for setting up the
    relationships.

    I need to mull this through my pea brain for a few days...! What this
    challenges for me is the idea that the kids are currently set up in a
    one-to-many relationship (on the many side, of course) with the
    families listed in the contacts table. How do I keep this integrity if
    the kids are in the same table as their parents? How would I handle
    any family that has more than one kid? I'm missing the larger picture
    here, I bet.

    Hmmm. I need to think on this!

    Thanks for all your input - I value it a LOT!

    Betsy
     
  9. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    betwalk@gmail.com wrote:
    > the kids are currently set up in a
    > one-to-many relationship (on the many side, of course) with the
    > families listed in the contacts table. How do I keep this integrity if
    > the kids are in the same table as their parents?


    With a relationship table to model families, perhaps. This article may
    help:

    'Hollywood Couples' by Joe Celko:
    http://www.intelligententerprise.com/010101/celko.jhtml

    Jamie.

    --
     

Share This Page