I apologize if this question has (undoubtedly) been asked before, however I've been hearing conflicting advice from colleagues and the internet.

I have a table of "groups" and each group has a list of members. My initial implementation plan was to have a table that mapped user IDs to group IDs. However, a colleague suggested using a different method, where members would be stored in a blob of information related to a "group" entry. I cannot remember why this was suggested, but he seemed to think it would be more efficient because a member-group mapping table could grow very large, very quickly.

I can't see why it would be a better approach, but I suppose I'm just asking if there is a better alternative to simply having a user ID - group ID map table?

Thanks for your time!

  • 6
    You are not Facebook. A user<>group mapping table is not going to be an exabyte of data. Properly indexed, it won't be a problem. No idea what a "blob" would entail, but it sounds like nonsense. – Philᵀᴹ Feb 9 '14 at 20:33
  • How many rows are you expecting for the users and groups tables? – Max Vernon Feb 9 '14 at 20:38
  • @Phil - I'll have to double-check with my colleague to see what he meant. And while I realize that this table won't be absurdly large in all likelihood, if it were, would I further combat that with a change to the database design? Or would that be a hardware-scaling issue? Thanks for the input :) – javasauce Feb 9 '14 at 21:10
  • @MaxVernon - They can both be arbitrarily large, but (obviously) there would be more users than groups. – javasauce Feb 9 '14 at 21:11
  • 1
    You can have both ways. Create the standard user<>group mapping table as input. If you need the grouped table you can group by groupId and concat the user IDs. PostgreSQL for example offers an Array datatype for this cases and an array_agg aggregate function for grouping. – Alexandros Feb 9 '14 at 22:04

Within a database, what you're describing is a table. Tables do not have order, but the data can be indexed to allow easier maintenance and retrieval.

You could always store data as a CSV list, XML, or a variety of other methods, but you should also consider what you're going to do with your data, and you're likely to find that a table ticks more boxes than most. If you're concerned about space, then compression strategies will help a lot, without losing the searchability.

| improve this answer | |
  • I'm in complete agreement with you currently. My colleague suggested using views for each group to return a list of members associated with each group. His proposal is that by following this methodology, we'll have better query performance across the board, but I feel like with proper indexing, query performance would be fairly similar. Any further thoughts? – javasauce Feb 10 '14 at 21:37
  • A view is just a stored subquery. There would be no difference between using: SELECT col1, col2 FROM dbo.Group1Stuff; and SELECT col1, col2 FROM dbo.MainTable WHERE col3 = 'Group1'; – Rob Farley Feb 10 '14 at 23:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.