Table occurrences, one-to-one relationships

[Mandu]

Hi,

I have a table of Groups and a table of People. A Group has a number of positions: Treasurer, Chairperson, District Representative, etc. (about 8 in all) - each one of which is a person in the People table.

When I display the detailed info about a group, I want to be able to list each of those positions, and the name of the person currently filling it.

It seems most obvious to store a Person_ID for each of those 8 positions in the Group record. However, unless I'm confused, this means creating 8 different relationships, and 8 different People TO's. That seems like a lot of clutter and effort.

Is there a simpler way to do this, that I'm just not seeing?

Thanks,

Chap

[bruceR]

This is a standard join table issue. The positions do not belong in the group table. You need a Group_positions table, with, minimally, GroupID; PersonID; RecordID; Position.

[Mandu]

Thanks -

I get the GroupID and the PersonID, and the Position, but why RecordID? Or are you referring to a key for the join record?

[bruceR]

Yes, key for the join record.

[Mandu]

Okay - I think I know what I wasn't initially seeing:

I'm converting an old MS-Access database, which had a "layout" showing various positions (and the people holding them) for a group. These were entirely static:

Contact1

Contact2

Contact3

Treasurer

Secretary

Chairperson

etc.

and I was attempting to mimic this setup, with separate fields in the Group record for Contact1_PersonID, Contact2_PersonID, Contact3_PersonID, etc. Hence the proliferation of relationships.

If I understand your approach, it's to present all the positions for a group in a list (a portal would be simplest), rather than as a static set of independent fields.