Repeating Foreign Keys in Record, Best way to approach?

Don Thompson 0 Reputation points

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?


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
10,919 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 87,271 Reputation points

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

                        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.