What table should I host data linking two objects from separate tables? I've tried searching around, but I'm not too sure how to correctly formulate this question, and found nothing. Could be a duplicate.



What is the best way (most efficient way) to host data that will link two objects together from separate tables? Let me explain with an example; there is a table named Users and another named Groups, a user can be part of a group. How should I hold the information on the groups the users are in.

Should I:


Leave the Users table empty, and put an array of user ids under the group in the Groups table.
Leave the Groups table empty, and put an array of group ids under the user in the Users table.
Put an array of both the user ids, and group ids under both the user and the group in both tables.


We'll say for the example that both bits of information (users in group X, and groups in user X) will be polled an equal ammount, both are accessed in an equal manner. Also, there are more users than groups.

The design part of me tells me I should use solution 2, it seems like the prettier way to go. But I'm thinking solution 1 would be faster (between 1 and 2) as there are more users than groups. And of course, solution 3 would be the fastest, but it will take more memory, and it is harder to manage (one table could have different information than the other if the program has bugs).

I know these kind of decisions will only gain tiny ammounts of time, or memory, but I like micro-optimisation, so what would you recommend? Maybe there is another solution I am not aware of?
If Users can only be members of one group, you need only store a GroupId in the Users table. If not, the pattern you are looking for is commonly known as a "Join" table, containing only foreign keys and perhaps a generated primary key.

If you have primary key UserId (uniquely identifies a user record) for the Users table and GroupId for the Groups table, you can create a third table:

CREATE TABLE [dbo].[GroupUsers](
    [UserId] [int] NOT NULL,
    [GroupId] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[GroupUsers]  WITH CHECK ADD  CONSTRAINT [FK_GroupUsers_Groups] FOREIGN KEY([GroupId])
REFERENCES [dbo].[Groups] ([GroupId])
GO

ALTER TABLE [dbo].[GroupUsers] CHECK CONSTRAINT [FK_GroupUsers_Groups]
GO

ALTER TABLE [dbo].[GroupUsers]  WITH CHECK ADD  CONSTRAINT [FK_GroupUsers_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([UserId])
GO

ALTER TABLE [dbo].[GroupUsers] CHECK CONSTRAINT [FK_GroupUsers_Users]
GO


This design allows for one-to-one, one-to-many and many-to-many relationships. In practice, its performance and scalability are excellent, as long as the tables are properly indexed.

To get all users in a group given its GroupId:

SELECT *
FROM Users u
INNER JOIN GroupUsers gu
ON u.UserId = gu.UserId
WHERE gu.GroupId = @GroupId


Many times the relationships need to be traversed across the foreign key table. For instance, to select the groups a user belongs to, given their username (assuming there is a Username field in Users):

SELECT g.* 
FROM Groups g
INNER JOIN GroupUsers gu
ON g.GroupId = gu.GroupId
INNER JOIN Users u
ON gu.UserId = u.UserId
WHERE u.Username = @Username


I usually use views to simplify queries that need this sort of traversal.