Agree... we did that for years and we realized that using delimited lists was a bad approach. Sent some time to undo all that. What a pain.
-----Original Message----- From: James Holmes [mailto:james.hol...@gmail.com] Sent: Monday, August 01, 2011 7:57 AM To: cf-talk Subject: Re: Method Opinion Avoid the delimited list idea; it always bites you on the arse eventually. Use the first option. -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 1 August 2011 21:46, Robert Harrison <rob...@austin-williams.com> wrote: > > 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:346427 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
