I would take a look at using the MINUS union operator, and a table of poll/user IDs that have completed the poll. MINUS should return only the records in the first part of the query that are not in the second part of the query....the first part being the list of candidates and the second part being the list of completed candidates.
Alternately, you could have a table that lists the candidates also include a status or completion date...and exclude those from an joined table. My $0.02... -----Original Message----- From: Robert Harrison [mailto:[email protected]] Sent: Monday, August 01, 2011 8:47 AM To: cf-talk Subject: Method Opinion Curious as to what you all think is the best method for something. I have a table that contains a list of polls. I have a user table that contains a list of possible persons who may complete the poll (it requires log-in access). I want to present a poll only one time so users can't complete a poll more than once, so I need to maintain a list of users who have completed a poll. I see two ways I can do this: 1. I can create a cross reference table that keeps users ID and Poll IDs (of users/polls completed), then use an SQL NOT IN to select polls for users who are NOT IN the completed poll table. 2. I can add a field in the POLLs record and put a delimited list of User IDs who've complete the poll, then not select any polls where the COMPLETED field contains the user ID of a given user. There are about 1,500 users. There will probably be not more than 10 polls going on at any one time. Poll history will be maintained for about 60 days. Thus, there may be thousands of COMPLETED records. Given that, is one of these methods better than the other, and if so, why? Should I use a delimited list in the POLL record, or should I use a cross-reference table with a join and NOT IN select... or should I do something different. Thanks Robert B. Harrison Director of Interactive Services Austin & Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 P : 631.231.6600 Ext. 119 F : 631.434.7022 http://www.austin-williams.com Great advertising can't be either/or. It must be &. Plug in to our blog: A&W Unplugged http://www.austin-williams.com/unplugged ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346429 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
