one to many design

Discussion in 'Access Table Design' started by inungh, Sep 3, 2010.

  1. inungh

    inungh Guest

    I have a table design base on business requirements that have
    folliwng:

    Parent table

    Key1

    Child Table

    Key1
    Key2
    Key3

    Does it make sense in the database design?
    If it does not make sense in the database design, should I review the
    business rules again?
    Do I need have a middle table which has Key1 and Key2?
    It seems MS Access does not give me one to many link to have one key
    in parent and 3 keys in the child table.

    Your information and help is great appreciated,
     
    inungh, Sep 3, 2010
    #1
    1. Advertisements

  2. On Fri, 3 Sep 2010 08:14:25 -0700 (PDT), inungh <> wrote:

    >I have a table design base on business requirements that have
    >folliwng:
    >
    >Parent table
    >
    >Key1
    >
    >Child Table
    >
    >Key1
    >Key2
    >Key3
    >
    >Does it make sense in the database design?
    >If it does not make sense in the database design, should I review the
    >business rules again?
    >Do I need have a middle table which has Key1 and Key2?
    >It seems MS Access does not give me one to many link to have one key
    >in parent and 3 keys in the child table.
    >
    >Your information and help is great appreciated,


    Does your child table have three *FIELDS*? or three *RECORDS*?

    There's an old saying: "fields are expensive, records are cheap". If you have
    three fields your table design is probably incorrect. Could you please explain
    a bit more about the real-life situation? What kind of Entities do the two
    tables represent? Does each Parent record have many Child records? Does each
    Child record potentially relate to multiple (three? up to three? maybe more?)
    Parent records?
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Sep 3, 2010
    #2
    1. Advertisements

  3. inungh

    inungh Guest

    On Sep 3, 11:25 am, John W. Vinson
    <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
    > On Fri, 3 Sep 2010 08:14:25 -0700 (PDT), inungh <> wrote:
    > >I have a table design base on business requirements that have
    > >folliwng:

    >
    > >Parent table

    >
    > >Key1

    >
    > >Child Table

    >
    > >Key1
    > >Key2
    > >Key3

    >
    > >Does it make sense in the database design?
    > >If it does not make sense in the database design, should I review the
    > >business rules again?
    > >Do I need have a middle table which has Key1 and Key2?
    > >It seems MS Access does not give me one to many link to have one key
    > >in parent and 3 keys in the child table.

    >
    > >Your information and help is great appreciated,

    >
    > Does your child table have three *FIELDS*? or three *RECORDS*?
    >
    > There's an old saying: "fields are expensive, records are cheap". If you have
    > three fields your table design is probably incorrect. Could you please explain
    > a bit more about the real-life situation? What kind of Entities do the two
    > tables represent? Does each Parent record have many Child records? Does each
    > Child record potentially relate to multiple (three? up to three? maybe more?)
    > Parent records?
    > --
    >
    >              John W. Vinson [MVP]
    >  Microsoft's replacements for these newsgroups:
    >  http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    >  http://social.answers.microsoft.com/Forums/en-US/addbuz/
    >  and see alsohttp://www.utteraccess.com- Hide quoted text -
    >
    > - Show quoted text -


    Thanks for the message,
    Child table has 3 keys fields, but only one key field in the parent
    table.
    Yes, there are multi records in child table relate to parent table.
    This is what I guess that I am not in the right direction. It will too
    long to expalin the business rules here.

    In short, parent table is tbluser which key is UserID
    Child table is a User Template to fill the activity when user enter.
    The problem is the activity (ActivityID) has one child called element
    (ElementID)
    If I want to have the user template table to have element level then
    my user template table must be

    UserID,
    ActivityID and ElementID, but without middle table which has UserID
    and ActivityId,

    Thanks again for helping,
     
    inungh, Sep 3, 2010
    #3
  4. On Fri, 3 Sep 2010 08:46:09 -0700 (PDT), inungh <> wrote:


    >Thanks for the message,
    >Child table has 3 keys fields, but only one key field in the parent
    >table.


    What's the relationship? Which field is it linked to?

    >Yes, there are multi records in child table relate to parent table.
    >This is what I guess that I am not in the right direction. It will too
    >long to expalin the business rules here.


    Well... then it will take even longer for me to explain the answer, since I
    don't really understand the question!

    >In short, parent table is tbluser which key is UserID
    >Child table is a User Template to fill the activity when user enter.
    >The problem is the activity (ActivityID) has one child called element
    >(ElementID)


    How are Users, Activites and Elements related?

    >If I want to have the user template table to have element level then
    >my user template table must be


    What is a "template table"? That doesn't appear to be an Access term.

    >UserID,
    >ActivityID and ElementID, but without middle table which has UserID
    >and ActivityId,


    That's not a question, and I'm not at all sure what you're trying to say!

    Let's keep at this... again, if you could describe (in general terms, you
    needn't post your entire business rule set) what real life Entities each table
    represents, and how those entities are related to one another in the real
    world, we should be able to help.
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Sep 3, 2010
    #4
  5. inungh

    inungh Guest

    On Sep 3, 1:36 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
    wrote:
    > On Fri, 3 Sep 2010 08:46:09 -0700 (PDT), inungh <> wrote:
    > >Thanks for the message,
    > >Child table has 3 keys fields, but only one key field in the parent
    > >table.

    >
    > What's the relationship? Which field is it linked to?
    >
    > >Yes, there are multi records in child table relate to parent table.
    > >This is what I guess that I am not in the right direction. It will too
    > >long to expalin the business rules here.

    >
    > Well... then it will take even longer for me to explain the answer, sinceI
    > don't really understand the question!
    >
    > >In short, parent table is tbluser which key is UserID
    > >Child table is a User Template to fill the activity when user enter.
    > >The problem is the activity (ActivityID) has one child called element
    > >(ElementID)

    >
    > How are Users, Activites and Elements related?
    >
    > >If I want to have the user template table to have element level then
    > >my user template table must be

    >
    > What is a "template table"? That doesn't appear to be an Access term.
    >
    > >UserID,
    > >ActivityID and ElementID, but without middle table which has UserID
    > >and ActivityId,

    >
    > That's not a question, and I'm not at all sure what you're trying to say!
    >
    > Let's keep at this... again, if you could describe (in general terms, you
    > needn't post your entire business rule set) what real life Entities each table
    > represents, and how those entities are related to one another in the real
    > world, we should be able to help.
    > --
    >
    >              John W. Vinson [MVP]
    >  Microsoft's replacements for these newsgroups:
    >  http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    >  http://social.answers.microsoft.com/Forums/en-US/addbuz/
    >  and see alsohttp://www.utteraccess.com


    I have user, activity, element and user template four tables.

    user table has following fields
    user id, user name, user code, user rights.....

    actitivy table has following fields
    activity id, activty code, activity name, actiivty description,

    Element table has following fields
    Element id, element code, element name, element description,

    and I am design user template to let user fill the information from
    user template table base on user creates the combination of activity
    and element the relationship between activity and element is many to
    many.

    I need have a composite key for user template table which is user id,
    activity id and element id to get right template base on user's
    activity and element selection.

    It seems that I need use the middle table of activity and element
    table for their many to many relationship and user template table to
    link with user table and activity element table.

    Thanks again for helping,
     
    inungh, Sep 3, 2010
    #5
  6. On Fri, 3 Sep 2010 10:50:28 -0700 (PDT), inungh <> wrote:

    >It seems that I need use the middle table of activity and element
    >table for their many to many relationship and user template table to
    >link with user table and activity element table.


    That is correct. A many to many relationship does indeed need an additional
    table, related one to many to each parent table. If each User can be involved
    in many Activities, and each Activity may involve many Users, you need an
    intermediate table with one-to-many relationships to each.

    I still don't understand how you're using the term "template". To me a
    template is a table (or form, or other object) which can be used to create a
    new table. That does not appear to be the case here.
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Sep 3, 2010
    #6
  7. inungh

    inungh Guest

    On Sep 3, 2:32 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
    wrote:
    > On Fri, 3 Sep 2010 10:50:28 -0700 (PDT), inungh <> wrote:
    > >It seems that I need use the middle table of activity and element
    > >table for their many to many relationship and user template table to
    > >link with user table and activity element table.

    >
    > That is correct. A many to many relationship does indeed need an additional
    > table, related one to many to each parent table. If each User can be involved
    > in many Activities, and each Activity may involve many Users, you need an
    > intermediate table with one-to-many relationships to each.
    >
    > I still don't understand how you're using the term "template". To me a
    > template is a table (or form, or other object) which can be used to create a
    > new table. That does not appear to be the case here.
    > --
    >
    >              John W. Vinson [MVP]
    >  Microsoft's replacements for these newsgroups:
    >  http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    >  http://social.answers.microsoft.com/Forums/en-US/addbuz/
    >  and see alsohttp://www.utteraccess.com


    template is the name of the table I am design.

    thanks again,
     
    inungh, Sep 4, 2010
    #7
  8. inungh

    tina Guest

    this is an old post, but just in case you're still working on it:

    as i understand it, one activity may have many elements, and one element may
    be included in many activities - as you posted previously, that is a
    many-to-many relationship. a template is the assignment of one or more
    specific elements to a specific activity - the "union" or "join" table
    between activities and elements, forming the -many side of a one-to-many
    relationship with each of those two tables. and one user may create many
    templates, but each template is created by only one user - a one-to-many
    relationship.

    if the above is correct, try the following tables/relationships structure,
    as

    tblUsers
    UserID (primary key)
    UserName
    UserCode
    UserRights

    tblActivities
    ActivityID (pk)
    ActivityCode
    ActivityName
    ActivityDescription

    tblElements
    ElementID (pk)
    ElementCode
    ElementName
    ElementDescription

    tblActivityElements
    UserID (foreign key)
    ActivityID (fk)
    ElementID (fk)
    <you could assign a surrogate primary key here, or use the three foreign key
    fields as a combination primary key.>

    relationships would be
    tblUsers.UserID 1:N tblActivityElements.UserID
    tblActivities.ActivityID 1:N tblActivityElements.ActivityID
    tblElements.ElementID 1:N tblActivityElements.ElementID

    though if you are tracking additional data that describes a specific
    *activity* of a specific *user*, you may want two tables here instead of
    one, as

    tblUserActivities
    UserActivityID (pk)
    UserID (fk)
    ActivityID (fk)
    <other fields that describe a specific activity as related to a specific
    user - there should be no fields describing elements, in this table.>

    tblUserActivityElements
    UserActivityID (fk)
    ElementID (fk)
    <you could assign a surrogate primary key here, or use the two foreign key
    fields as a combination primary key.>

    relationships for the two tables above would be
    tblUsers.UserID 1:N tblUserActivities.UserID
    tblActivities.ActivityID 1:N tblUserActivities.ActivityID
    tblUserActivities.UserActivityID 1:N
    tblUserActivityElements.UserActivityID
    tblElements.ElementID 1:N tblUserActivityElements.ElementID

    hth
    tina


    "inungh" <> wrote in message
    news:...
    On Sep 3, 1:36 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
    wrote:
    > On Fri, 3 Sep 2010 08:46:09 -0700 (PDT), inungh <> wrote:
    > >Thanks for the message,
    > >Child table has 3 keys fields, but only one key field in the parent
    > >table.

    >
    > What's the relationship? Which field is it linked to?
    >
    > >Yes, there are multi records in child table relate to parent table.
    > >This is what I guess that I am not in the right direction. It will too
    > >long to expalin the business rules here.

    >
    > Well... then it will take even longer for me to explain the answer, since
    > I
    > don't really understand the question!
    >
    > >In short, parent table is tbluser which key is UserID
    > >Child table is a User Template to fill the activity when user enter.
    > >The problem is the activity (ActivityID) has one child called element
    > >(ElementID)

    >
    > How are Users, Activites and Elements related?
    >
    > >If I want to have the user template table to have element level then
    > >my user template table must be

    >
    > What is a "template table"? That doesn't appear to be an Access term.
    >
    > >UserID,
    > >ActivityID and ElementID, but without middle table which has UserID
    > >and ActivityId,

    >
    > That's not a question, and I'm not at all sure what you're trying to say!
    >
    > Let's keep at this... again, if you could describe (in general terms, you
    > needn't post your entire business rule set) what real life Entities each
    > table
    > represents, and how those entities are related to one another in the real
    > world, we should be able to help.
    > --
    >
    > John W. Vinson [MVP]
    > Microsoft's replacements for these newsgroups:
    > http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    > http://social.answers.microsoft.com/Forums/en-US/addbuz/
    > and see alsohttp://www.utteraccess.com


    I have user, activity, element and user template four tables.

    user table has following fields
    user id, user name, user code, user rights.....

    actitivy table has following fields
    activity id, activty code, activity name, actiivty description,

    Element table has following fields
    Element id, element code, element name, element description,

    and I am design user template to let user fill the information from
    user template table base on user creates the combination of activity
    and element the relationship between activity and element is many to
    many.

    I need have a composite key for user template table which is user id,
    activity id and element id to get right template base on user's
    activity and element selection.

    It seems that I need use the middle table of activity and element
    table for their many to many relationship and user template table to
    link with user table and activity element table.

    Thanks again for helping,
     
    tina, Nov 7, 2010
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Earl Anderson

    One-To-Many Problem is One Too Many Design Problems

    Earl Anderson, Oct 12, 2004, in forum: Access Table Design
    Replies:
    2
    Views:
    99
    Tim Ferguson
    Oct 13, 2004
  2. CAD Fiend

    Confused about one-to-many or many-to-many relationships

    CAD Fiend, Jul 6, 2005, in forum: Access Table Design
    Replies:
    4
    Views:
    320
    CAD Fiend
    Jul 7, 2005
  3. Bill R via AccessMonster.com

    Many to Many to Many

    Bill R via AccessMonster.com, Aug 24, 2005, in forum: Access Table Design
    Replies:
    7
    Views:
    92
    Tim Ferguson
    Aug 25, 2005
  4. Debra Farnham

    How many one to many relationships are too many?

    Debra Farnham, Oct 24, 2006, in forum: Access Table Design
    Replies:
    7
    Views:
    382
    Graham Mandeno
    Oct 25, 2006
  5. tedzbug

    Many, Many, Many........ One? I'm lost

    tedzbug, Jan 4, 2007, in forum: Access Table Design
    Replies:
    9
    Views:
    268
    Graham Mandeno
    Jan 8, 2007
  6. Hal
    Replies:
    6
    Views:
    193
  7. Ahmed
    Replies:
    1
    Views:
    136
    Allen Browne
    Mar 8, 2010
  8. Ahmed
    Replies:
    2
    Views:
    235
    Roger Carlson
    Apr 26, 2010
Loading...