creating tables on the fly? Should I or Shouldn't I?
I posted this before, but got no response. Does any one know if this is sound/standard practice and if it makes any sense in this context? Just a nudge in the right direction in all I need. Thanks! - I am designing an email application that will be used by multiple (100's - 1000's) of users. Each user will have their own account. When they send mail, the recipients are recorded to a table. this table is basically just a reference to the full recipient record and the email campaign id. If each user is sending email campaigns of 25,000 recipients + what is the best way to record and later generate reports on this data. Would it make sense to generate a campaign recipient table on the fly for each user of the system upon registration or first use? This way the total records per table would be a bit more manageable for report generation later. Is this sound practice? Or should I just have one huge table shared by all the systems users. I could see this table growing to 20,000,000 records within time. Would select statements and report generation on subsets of data from this monster table be slow? (SQL7) Are there benefits to leaving all of the data in one big table as opposed to generating this table on the fly for each user, therefor keeping the total size down? The table which holds the email campaigns recipients will need to be used in a number of joins to include additional email delivery stats. What do you think? Brook Davies maracasmedia inc. __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: creating tables on the fly? Should I or Shouldn't I?
Generating tables for users on the fly is liable to be a lot more work both in the short term and especially in the long run. My general feeling is that if you think having separate tables to limit the number of records per would be a significant advantage ( if not necessary for performance purposes, after having exhausted the use of indexes and locking hints ) that you should probably be replicating and load-balancing your database accross multiple servers. I don't have any personal experience with systems which create tables on the fly, however, so I can't say from experience that it was a disaster ... it is a semi-unknown -- I know how it can be accomplished with SQL Server using the sp_execsql stored procedure primarily ( that is internally, as opposed to using ColdFusion dynamic queries ) but I haven't actually done it. The fact that there are very few people who have worked with similar systems is another reason for hesitation, since it limits your ability to get help if something goes wrong. Isaac Dealey Certified Advanced ColdFusion Developer www.turnkey.to 954-776-0046 I posted this before, but got no response. Does any one know if this is sound/standard practice and if it makes any sense in this context? Just a nudge in the right direction in all I need. Thanks! -- --- I am designing an email application that will be used by multiple (100's - 1000's) of users. Each user will have their own account. When they send mail, the recipients are recorded to a table. this table is basically just a reference to the full recipient record and the email campaign id. If each user is sending email campaigns of 25,000 recipients + what is the best way to record and later generate reports on this data. Would it make sense to generate a campaign recipient table on the fly for each user of the system upon registration or first use? This way the total records per table would be a bit more manageable for report generation later. Is this sound practice? Or should I just have one huge table shared by all the systems users. I could see this table growing to 20,000,000 records within time. Would select statements and report generation on subsets of data from this monster table be slow? (SQL7) Are there benefits to leaving all of the data in one big table as opposed to generating this table on the fly for each user, therefor keeping the total size down? The table which holds the email campaigns recipients will need to be used in a number of joins to include additional email delivery stats. What do you think? Brook Davies maracasmedia inc. __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: creating tables on the fly? Should I or Shouldn't I?
I think the question that needs to be answered is do the account holders really want to know exactly when a particular email address was mailed, and what they were mailed, and if so do they really need to do the queries on the fly? Those are the only benfits I can see of keeping track of exactly which emailing a particular email address received. I do see how valuable that kind of info could be in trying to track response though. Maybe if you only stored the complete record of who got emailed what for a certain amount of time? That might make the hardware requirements managable. How about logging the mailing info to a log file, and developing a process to parse the files and generate fancy reports? They couldn't be done on the fly, but there might be an oppurtunity to charge for the reports that way :) As to the question of creating tables on the fly, I don't think it's a good idea...data must be normalized, resistance is futile :) That's my opinion anyway. -- Jon mailto:[EMAIL PROTECTED] Wednesday, July 31, 2002, 8:20:48 PM, you wrote: BD I posted this before, but got no response. Does any one know if this is BD sound/standard practice and if it makes any sense in this context? Just a BD nudge in the right direction in all I need. Thanks! BD - BD I am designing an email application that will be used by multiple (100's - BD 1000's) of users. Each user will have their own account. When they send BD mail, the recipients are recorded to a table. this table is basically just BD a reference to the full recipient record and the email campaign id. If each BD user is sending email campaigns of 25,000 recipients + what is the best way BD to record and later generate reports on this data. BD Would it make sense to generate a campaign recipient table on the fly for BD each user of the system upon registration or first use? This way the total BD records per table would be a bit more manageable for report generation BD later. Is this sound practice? Or should I just have one huge table shared BD by all the systems users. I could see this table growing to 20,000,000 BD records within time. Would select statements and report generation on BD subsets of data from this monster table be slow? (SQL7) Are there benefits BD to leaving all of the data in one big table as opposed to generating this BD table on the fly for each user, therefor keeping the total size down? The BD table which holds the email campaigns recipients will need to be used in a BD number of joins to include additional email delivery stats. __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: creating tables on the fly? Should I or Shouldn't I?
I would say providing such granularity gets expensive... Not that should or shouldn't do it... from my take you have a database that looks like: ID (autonumber) email_address (text) email_campaign (numerical) subscriber_identifier (numerical) - relates to the particular user generating the email If you have 25,000 as a hard max on per mail mailings, then worst case is 25,000 x users x duration of logging... Every 40 power users per day adds a million rows of data... 400 power users equals 10 million rows Growth is exponential. Manageability of the data is paramount to making the system work in my opinion So historical data should be 'warehoused' after so many days and made available in less than live time web response atmosphere... Multiple tables won't work in this scenario... due to volume and limits on indexes, overhead, etc I am sure someone will say otherwise... it's just an expensive road to travel... The one table approach is wonderful, because you can bulk remove old data from it in one big query... the other route would mean multiple efforts to trim each set of data... more operations, more likelihood for error... I run queries against a several million column access database with success... SQL varieties (MSSQL and MySQL) should fair well for multiple user environments. You should attempt to precompile things on schedule to another table with stats, long and large term stuff... The only thing people should live time be requesting is active campaign data... Noted, that this is a logging application... This is the type of stuff that normally breaks things... -paris Paris Lundis Founder Areaindex, L.L.C. http://www.areaindex.com http://www.pubcrawler.com (p) 1-212-655-4477 [finding the future in the past, passing the future in the present] [connecting people, places and things] -Original Message- From: Brook Davies [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 31, 2002 8:21 PM To: CF-Talk Subject: creating tables on the fly? Should I or Shouldn't I? I posted this before, but got no response. Does any one know if this is sound/standard practice and if it makes any sense in this context? Just a nudge in the right direction in all I need. Thanks! - I am designing an email application that will be used by multiple (100's - 1000's) of users. Each user will have their own account. When they send mail, the recipients are recorded to a table. this table is basically just a reference to the full recipient record and the email campaign id. If each user is sending email campaigns of 25,000 recipients + what is the best way to record and later generate reports on this data. Would it make sense to generate a campaign recipient table on the fly for each user of the system upon registration or first use? This way the total records per table would be a bit more manageable for report generation later. Is this sound practice? Or should I just have one huge table shared by all the systems users. I could see this table growing to 20,000,000 records within time. Would select statements and report generation on subsets of data from this monster table be slow? (SQL7) Are there benefits to leaving all of the data in one big table as opposed to generating this table on the fly for each user, therefor keeping the total size down? The table which holds the email campaigns recipients will need to be used in a number of joins to include additional email delivery stats. What do you think? Brook Davies maracasmedia inc. __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: creating tables on the fly? Should I or Shouldn't I?
Personally... I would create a good relational tables structure without any data replication... this should be fairly sufficient. Joe Certified Advanced ColdFusion Developer [EMAIL PROTECTED] -Original Message- From: Brook Davies [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 31, 2002 8:21 PM To: CF-Talk Subject: creating tables on the fly? Should I or Shouldn't I? I posted this before, but got no response. Does any one know if this is sound/standard practice and if it makes any sense in this context? Just a nudge in the right direction in all I need. Thanks! - I am designing an email application that will be used by multiple (100's - 1000's) of users. Each user will have their own account. When they send mail, the recipients are recorded to a table. this table is basically just a reference to the full recipient record and the email campaign id. If each user is sending email campaigns of 25,000 recipients + what is the best way to record and later generate reports on this data. Would it make sense to generate a campaign recipient table on the fly for each user of the system upon registration or first use? This way the total records per table would be a bit more manageable for report generation later. Is this sound practice? Or should I just have one huge table shared by all the systems users. I could see this table growing to 20,000,000 records within time. Would select statements and report generation on subsets of data from this monster table be slow? (SQL7) Are there benefits to leaving all of the data in one big table as opposed to generating this table on the fly for each user, therefor keeping the total size down? The table which holds the email campaigns recipients will need to be used in a number of joins to include additional email delivery stats. What do you think? Brook Davies maracasmedia inc. __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Creating tables on the fly or using one big table
I am designing an email application that will be used by multiple (100's - 1000's) of users. Each user will have their own account. When they send mail, the recipients are recorded to a table. this table is basically just a reference to the full recipient record and the email campaign id. If each user is sending email campaigns of 25,000 recipients + what is the best way to record and later generate reports on this data. Would it make sense to generate a campaign recipient table on the fly for each user of the system upon registration or first use? This way the total records per table would be a bit more manageable for report generation later. Is this sound practice? Or should I just have one huge table shared by all the systems users. I could see this table growing to 20,000,000 records within time. Would select statements and report generation on subsets of data from this monster table be slow? (SQL7) Are there benefits to leaving all of the data in one big table as opposed to generating this table on the fly for each user, therefor keeping the total size down? The table which holds the email campaigns recipients will need to be used in a number of joins to include additional email delivery stats. What do you think? Brook At 09:56 PM 7/26/02 -0700, you wrote: How bout cfif variables.choiceValue !--- process stuff- -- /cfif At 11:34 PM 7/26/02 -0500, you wrote: I have an if statement that I need to write, it is I am sure very simple, but for some reason i cannot think straight on it. Basically if this variable is a 0 or a then I don't want to process some stuff ... how would I write this? This code below doesn't work .. it still lets or 0's through... cfif (variables.choiceValue neq 0) OR (Len(variables.choiceValue) /cfif What am I not thinking of?! Paul Giesenhagen QuillDesign __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Repost: Tables on the fly
what was the code you put in a table to get it to display on the fly before the /table I have a search pages thats geeting so big that it needs to be able to be displayed on the fly Bill Wheatley Director of Development Allaire Certified ColdFusion Developer AEPS INC Allaire ColdFusion Consulting Partner www.aeps.com www.aeps2000.com 954-472-6684 X303 ICQ: 417645 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Tables on the fly
Ok i remember this being talked about a few months back but i cant find it in the archives. Whats the command you put in the Table structures for HTML ot make a TABLE display on the fly? You have to give it the command so it does not have to calculate on its own? Thanks! Bill Wheatley Director of Development AEPS INC Allaire ColdFusion Consulting Partner Allaire Certified ColdFusion Developer http://www.aeps.com ICQ: 417645 954-472-6684 X303 IMPORTANT NOTICE: This e-mail and any attachment to it is intended only to be read or used by the named addressee. It is confidential and may contain legally privileged information. No confidentiality or privilege is waived or lost by any mistaken transmission to you. If you receive this e-mail in error, please immediately delete it from your system and notify the sender. You must not disclose, copy or use any part of this e-mail if you are not the intended recipient. The RTA is not responsible for any unauthorized alterations to this e-mail or attachment to it ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
