I have a table which stores information about a group. Information such as the group name, group id, the number of members... However, I would also like to store the codes of each individual member within a group. The way I initially tried to store the member codes was by creating individual fields. So i would have fields named MemeberCode1, MemeberCode2, MemberCode3. But the problem is a group can have 100 members which would mean 100 fields would be required to store the member codes individually.
My question is, Is there a way I can store an x amount of member Codes within a single field within my table or do simply make y fields and limit the number of users in a group to y.
This is a great example of when to use a separate table. You can create a second table, called "group", and store your group data there (group ID, group name).
I'm assuming that a member can belong to multiple groups, and a group can have many members. If so, you can then have a table in the middle of those two tables to capture the members in each group. You can call this table "memberships" or "group_member" or something.
It would contain the Primary Keys of each of the entries in the member and group tables.
So your table structure could be:
Finally, to find the number of members in each group:
SELECT group_id, COUNT(*) FROM membership GROUP BY group_id;
Or to get group data with that:
SELECT m.group_id, g.group_name, COUNT(m.*) FROM membership m INNER JOIN group g ON m.group_id = g.group_id GROUP BY m.group_id;