Robert: Personally, I would use a "Many to Many" lookup table, such as your suggestion # 1 below.
This lookup table (e.g. userPolls ) would contain two (2) columns that are Foreign Keys. The columns "userID" and "pollID" hold the Primary Keys of the Users table and Polls table, respectively. Some additional information can be found here: http://msdn.microsoft.com/en-us/library/ms190651.aspx Again, this is my approach; I would be interested in hearing what other folks suggest Cheers! Christian N. Abad - President Accessible Computing, Inc. 1210 McLaughlin Drive Charlotte, NC 28212 http://www.AccessibleComputing.com 704.900.1825 (Direct Line) christ...@accessiblecomputing.com -----Original Message----- From: Robert Harrison [mailto:rob...@austin-williams.com] Sent: Monday, August 01, 2011 9: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:346418 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
