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.
Userstable empty, and put an array of user ids under the group in the
Groupstable empty, and put an array of group ids under the user in the
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?