0

I have 2 tables:

threads (thread_id)
users   (thread_id,user_id)

Users create threads by inviting other users to be on a thread, so multiple users can be on the same thread. I am trying to prevent users from creating new threads that have the same exact users (duplicate user sets). Does someone have a quick SELECT statement to test this?

Andrew Tibbetts
  • 2,874
  • 3
  • 23
  • 28
  • http://stackoverflow.com/questions/8474341/find-ids-where-values-are-only-in-array was close but I need to test for _all_ the users not just one or two of them (via IN). Otherwise I dont' have knowledge of any way to do it :\ – Andrew Tibbetts Aug 22 '12 at 14:18
  • 1
    There is not enough information to answer the question. How do you know who the proposed users are on the proposed thread? Clearly, the information is not in users, since you are trying to prevent the creation of the thread. – Gordon Linoff Aug 22 '12 at 14:18
  • The users on a thread are connected via the thread_id. when a user creates a thread a new thread_id is created in threads and any users (including creator user) will have a record in users with the new thread_id. – Andrew Tibbetts Aug 22 '12 at 14:20

1 Answers1

0

Since your user groups are dynamic (i.e. users can invite other users to a thread at any time), there is no strong reason to enforce the new thread validation. I would give up one of the requirements. Either make the user group static (i.e. a new user should define the group members at creation time) or allow users to create any threads they want. If the restriction on new threads is there to prevent spamming, use something else like CAPTCHAs (or a limit on # of new threads per hour per user) to accomplish the same objective.

You are going to run into SQL performance issues if you try to satisfy both requirements, since you'll be running multiple full-table scans on the database every time someone tries to start a new thread. Also, at any time you can end up in a situation where 2 threads have the same users. E.g. Thread 1 has users A, B and C. Thread 2 has users A and B. What happens when user B invites C to thread 2?

Vishal Bardoloi
  • 652
  • 5
  • 18