Re: Method Opinion
Did I say that? I said may be an option I just hate ayatollahs and never do and always do Good practice is a harmonious combination of common sense and intelligence, both being the fruit of experience, not just a set of black or white rules. On the other hand, if someone's posting a question like that to a list, chances are they should follow the accepted norm. There's a reason that third normal form is the accepted minimum database normalization - that reason is the huge amount of pain and suffering that comes from most violations of that norm. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:346449 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Method Opinion
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: AW 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
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: AW 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:346419 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Method Opinion
Avoid the delimited list idea; it always bites you on the arse eventually. Yeah, what James said. Been there, did that, and arse was summarily bitten. G! On Mon, Aug 1, 2011 at 10:57 AM, James Holmes james.hol...@gmail.comwrote: 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: AW 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:346422 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Method Opinion
Thanks. Delimited list nixed. 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: AW 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:346424 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Method Opinion
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: AW 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
RE: Method Opinion
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 querythe 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:rob...@austin-williams.com] 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: AW 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
Re: Method Opinion
A cross table is definitely THE standard way to do it. Although it is not considered good practice by database ayatollah, a delimited list may be an option when a reasonable number of possible values is involved. there are several advantages: 1º it is simpler to update in the database in case of modifications (only one field in one query); 2º the list is return directly in a list of checkbox input fields. 3º the select query returns directly a list, I'm still looking for a way to get directly a list in a query using a cross table. 4º CF is especially well equiped for list manipulation. However you have to make sure that the number of possible values cannot get too large in the future. For instance I use this technique for registration to activities in a convention. Participants will never attend thousands of activities, even in a 15 days convention. ~| 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:346432 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Method Opinion
And I was almost going to say, but Claude will post that a delimited list is a great idea to save you the trouble. -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 2 August 2011 02:43, wrote: A cross table is definitely THE standard way to do it. Although it is not considered good practice by database ayatollah, a delimited list may be an option when a reasonable number of possible values is involved. there are several advantages: 1º it is simpler to update in the database in case of modifications (only one field in one query); 2º the list is return directly in a list of checkbox input fields. 3º the select query returns directly a list, I'm still looking for a way to get directly a list in a query using a cross table. 4º CF is especially well equiped for list manipulation. However you have to make sure that the number of possible values cannot get too large in the future. For instance I use this technique for registration to activities in a convention. Participants will never attend thousands of activities, even in a 15 days convention. ~| 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:346440 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Method Opinion
a delimited list is a great idea Did I say that? I said may be an option I just hate ayatollahs and never do and always do Good practice is a harmonious combination of common sense and intelligence, both being the fruit of experience, not just a set of black or white rules. ~| 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:346442 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Method Opinion
I'm not sure how your religious prejudices come into this, but whatever. -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 2 August 2011 08:08, wrote: I just hate ayatollahs ~| 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:346443 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Method Opinion
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 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, 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
