Repeating Foreign Keys in Record, Best way to approach?

Question

I have in interesting situation.

I am calling an API which returns a Group. The members of that group are appended to the Group information using foreign keys....Group Info, Member1,member2, member3, member4, etc.

Depending on the Group, it may have only one foreign key appended or it might have dozens or more.

The Foreign key values are retrieved by another API call which returns the entire list of possible members.

These results have been imported into two tables, Groups, Members. The Group table has columns to accommodate the maximum number of foreign keys that were returned when I imported.

Of course it is straight forward to pick any particular FK and link it to the Member list to get the member name, but I am struggling to come up with a query that doesn't require explicit join to the Members table for all the possible Fks.

Anyone have a suggestion for a good approach for to tackling this?

Thanks.

Answer 1

So you are saying that you have a table like this:

CREATE TABLE Groups (GroupID  int NOT NULL PRIMARY KEY,
                    Member1 int NOT NULL,
                    Member2 int NULL,
                    ...
                    MemberN int NULL)

That's a very poor design. You should have a table GroupMembers:

CREATE TABLE GroupMembers (GroupID   int NOT NULL,
                           MemberID int NOT NULL,
                           CONSTRAINT pk_GroupMembers PRIMARY KEY(GroupID, MemberID)
)

This will make your life a lot easier.