Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    How to add an extra element to my erd?

    Hello,

    I have an access database which is used to store individual users (tblusers) visits (tblvisits) to a library. One user can have many visits, and one visit may involve many activities (tlbactivities).

    I would like to add the option of allowing a Group of people (tblgroups) to make a visit to a library, but the part which I cannot get right is that an existing individual user may be a member of many groups, and one group may have many members. So if an individual comes for a visit with a group I need to capture that group visit, but also the induvidual user who was part of that group.

    I am not sure how to add a possible groups table, should it be related to tblusers, or tblgroups, or both? I have attached my erd if anyone can offer any suggestions.

    Thank you
    Attached Thumbnails Attached Thumbnails erd2.jpg  

  2. #2
    Join Date
    Aug 2004
    Posts
    364
    Any thoughts or suggestions?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    so a person can visit the library in their own capacity or as a member of a group, presumably you don't know if all members of a group will visit the library in the same visit.

    so it sounds like you need intersection tables to associate which groups a member belongs to
    that intersection is usually the PK of the intersecting tables combining to form the PK of the intersected table

    the you need intersection tables between a visit and individuals. arguably you could store the group as part of the visit table as there can only be one group in any one visit
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2004
    Posts
    364
    so a person can visit the library in their own capacity or as a member of a group, presumably you don't know if all members of a group will visit the library in the same visit.
    Yes that is correct.

    So am I correct in thinking I should create an intersection table between tblusers and tblgroups, using userID(pk) and groupID(pk) from the two table as the new intersection tables primary keys?

    And yes, one group can make one visit at a time.

    And then, is the new intersection table (lets say it is called 'tblBelongsTo') linked to my existing visits table?

    It is getting me so confused!

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    Could you explain again how many intersection tables I need, and where and to they are joining to?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    an intersection table is used when you need to model a many to many relationship

    Google
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    Yes, do I need one or two intersection tables?

    Firstly, an intersection table between groups and indivials? This new intersection table links to the visits table? Is that correct?

    And then secondly an intersection table between visits and individuals? This intersection table also then links to visits table Is that correct?

  8. #8
    Join Date
    Aug 2004
    Posts
    364
    Am I on the right track with this new ERD? If you could have a look and let me know I would appreciate it very much
    Attached Thumbnails Attached Thumbnails erd3.JPG  

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    I don't think you need to separate visit and beneficiaryVisit, because they are they describe the same thing.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Some thunklations:
    Is a beneficiary what was once a user? If so, can a user visit if they are not in a group?

    Imagine tblBeneficiaryGroups contains one row and one row only:
    Code:
    1     1
    And tblBeneficiaryGroupVisits contains one row and one row only:
    Code:
    2     2     2
    would that be valid?

    If a beneficiarygroup visits more than once do you need to record all visits?

  11. #11
    Join Date
    Aug 2004
    Posts
    364
    Sorry, yes a Beneficiary is a User, call them what you want, I forgot I may have changed that field name.

    Yes a User can visit when not in a group.

    All visits by an individual need to be recorded, and all visits by a group need to be recorded also.

    I have just realised that I may not have explained the group visit clearly, and this may confuse things more, but I need to mention it: One user can visit as a representetive of a whole group. What I mean is a Group visit may infact only have had one User making that visit on behalf of a group. (A group visit may be defined by a single user arriving at a library on behalf of a group to do photocopying for that group)

    Now my head really hurts!
    Last edited by moss2076; 04-15-10 at 16:28.

  12. #12
    Join Date
    Apr 2010
    Posts
    24
    Quote Originally Posted by moss2076 View Post
    I have just realised that I may not have explained the group visit clearly, and this may confuse things more, but I need to mention it: One user can visit as a representetive of a whole group. What I mean is a Group visit may infact only have had one User making that visit.
    I wouldn't worry about that. If they are acting as a single rep for a group, count them as a group, since they arn't there on a single user capacity.

    Just my 2 cents
    DevilsAdvocate
    ------------------------
    The elephant in the room
    ------------------------
    Ordo ab chao

  13. #13
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by DevilsAdvocate View Post
    I wouldn't worry about that. If they are acting as a single rep for a group, count them as a group, since they arn't there on a single user capacity.

    Just my 2 cents
    Thankyou for your input

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by moss2076 View Post
    Yes a User can visit when not in a group.
    Ok - so which table would you record this in?

    The other questions still stand too

    Quote Originally Posted by moss2076 View Post
    Now my head really hurts!
    Jolly good - that means things are going swimmingly.

  15. #15
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by pootle flump View Post
    Ok - so which table would you record this in?
    I think I would record this in the single visits table, and have a field to show wether the visit was part of a group or not? A yes/no combo field.

    In response to your other question -
    Imagine tblBeneficiaryGroups contains one row and one row only:

    Code
    1 1
    And tblBeneficiaryGroupVisits contains one row and one row only:

    Code:
    2 2 2
    would that be valid?

    If a beneficiarygroup visits more than once do you need to record all visits?
    yes I think that would be valid and yes I need to record all visits.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •