Thank you Gabriel, more background information may result in a better concept, so here it is.
The web based application I am writing deals with people who have an a home for rent. They can sign up for small fee and enter their rental property into the application so its searchable and viewable by the internet public. I have a membership DB with single members table containing 20 columns which holds the users signup info. Members can list more than one rental property. Members has opportunity to enter some portion of the 140 columns of data to describe the rental property. Currently the real_estate DB contains a single property table of 140 columns. I have grouped the info in the table into 3 groups, mandatory info deals with the business of renting, second group deals with location, third group deals with house characteristics. All the 140 columns of data will be shown on the detail property screen. Fields that don't pertain to that individual property will show up as blank because the user did not enter data. All fields are 'varchar' to conserve on unused space. One single record per listed property has all the info retrievable by one read, but has undesirable effects entering all of it from single form. Making 3 tables would mean duplication of some common key fields to allow retrieving all the associated rows to combine the data for displaying. I would think since most of the activity will be people on the internet searching for rental property, performance would be better to retrieve all the info with a single read. This makes the 3 table idea a bad performer. It has been suggested to use session control as a staging function where the forms store the entered data so a single insert of all the data can be made to the table. Sessions store their data in standard flat files in /tmp. Flat files are not known for their access speed and performance, with 2 or 3 fields this is ok but 100+ fields and this may become a major performance bottleneck. My current thinking is to break the entering of the property info into 3 separate forms, the first with the mandatory info does a insert to create the row with the remaining 100 columns being seeded with $t=''. Group 2 & 3 will them be a update to the row using the 'set' keyword just touching the columns under their separate control. Does this sound like a workable solution or is there some other approach that is better suited to my application? Thanks to all the people who have replied all ready. -----Original Message----- From: Gabriel PREDA [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 15, 2006 5:01 AM To: Mysql Subject: Re: Very large from Hmmm... Let me say some thoughts... First *fbsd_user* said that he has *100 input fields plus 40 different drop downs.* And everybody is arguing that they prefer "*one single insert*". Is it just me... or are you thinking at a table with 140 columns ? Thinking at such a monster... all the above discussion is ok ! But who does a table with 140 columns... It's not good practice... it's no good at all... If we're not working with a monster like that... all discusion falls down... On the other hand if I have to insert all that info... in let's say... I don't know... 10 tables... what's the point of using "*one single insert*" how ca one use a "one single insert" to put data in 10 tables ? So... the design of the application follows in at least 50% of the cases the design of the DATABASE ! Give us a little more details about your database ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
