Abdiyee Abdiyee - 1 year ago 43
SQL Question

How do i store multiple pieces of individual data within a single field

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.

Answer Source

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:

  • MEMBER (member ID... other fields)
  • GROUP (group ID, group_name)
  • MEMBERSHIP (member_id, group_id)

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download