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

Any thoughts or suggestions?

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

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!

Could you explain again how many intersection tables I need, and where and to they are joining to?

an intersection table is used when you need to model a many to many relationship

Google

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?

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

I don't think you need to separate visit and beneficiaryVisit, because they are they describe the same thing.

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:
And tblBeneficiaryGroupVisits contains one row and one row only:
would that be valid?

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

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!

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

Ok - so which table would you record this in?

The other questions still stand too

Jolly good - that means things are going swimmingly.

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 -
yes I think that would be valid and yes I need to record all visits.