Option one is definitely the way to go. If you use a delimited list you lose the speed and flexibility of indexed SQL, as the list forces you to do a hierarchical search for the user id each time.
On Mon, Aug 1, 2011 at 6:46 AM, Robert Harrison <[email protected]> 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, w ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:346444 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
