Hi Paul, Thank you so much for getting back to me! Also, thanks for letting me know that there is no limit in the database, I am using MySQL, but I don't think that there is any difference int his issue from using this one compared to others.
Therefore I will be using the one table approach, as this seems much easier to me at this stage. I could also use this to filter the years(such as only getting the data from say 2003-2005). This was my main thought in seperating the tables. After looking at the db this morning, I also see that there are lots of missing data, which is listed as '999'. I am thinking that I could use if statement to deal with this issue. Somebody at work today gave me a MySQL book, which shows many good examples on getting the information from the db. I will reading this tonight, as there are lots of others things that I will want to do, as to list only certain aspects of the tables, etc. Thanks so much for your help, espeically the: Select year, month, day, location, time_wind >From dataTable Where year >= '#minyear#' And year <= '#maxyear#' I did not know this, as I usually use the * do display all the records. I will add the ID as the primary key(auto increment) since you think that this would be best. I will say that db interaction is my weakest aspect of working with ColdFusion. This is actually my first time doing the database setup on my own. Thanks again for your help`-` John ----- Original Message ----- From: Paul Kukiel <[EMAIL PROTECTED]> Date: Tuesday, September 18, 2007 7:43 pm Subject: RE: multi-table DB To: CF-Newbie <[email protected]> > If you had one big table then > > Select year, month, day, location, time_wind > From dataTable > Where year >= '#minyear#' > And year <= '#maxyear#' > > If there are multiple tables then you need more then one query. > > Select year, month, day, location, time_wind > From dataTable2001 > > Select year, month, day, location, time_wind > From dataTable2002 > > ect > > > It would be best to have a table such as: > > Id, year, month, day, location, time_wind > > And id being the primary key where id auto increments. > > Having a large table in a database is not issue and is probably a > simplerapproach. > > A quick google showed that for SQL server: > > There is no maximum number. You can keep adding rows until the > database runs > out of space. As far as other maximums go :- > > SQL 6.5 SQL 7.0 > Database size 1 TB 1,048,516 TB > File size 32 GB 32 TB > Files per database 32 32768 > > (http://www.windowsitpro.com/Articles/ArticleID/14356/14356.html?Ad=1) > > > Paul > > > -----Original Message----- > From: John Barrett [mailto:[EMAIL PROTECTED] > Sent: Wednesday, 19 September 2007 4:12 PM > To: CF-Newbie > Subject: multi-table DB > > Hi, > I have a huge database of daily records(about 60 years),and I have > made a > table for each year. I am confused what is the best way to have CF > get all > the data? > > for one year I can just do a cfquery of the table, no problem here, > but if I > want to view lets say 10 years of data, how would I go about this? > > the db as the form of: > year month day location time wind > > I was thinking I could use "year" as the primary key, but then > changingchanging the years is what leaves me so confused. I say > this as I think that > the first db field has to be the primary key? > > I hope that I wrote this good enough to where you can make sense of > this and > send some help. I could also show sample code as well. > > Somebody told me why not just have everything in one table, but > then that > would be way too big I think. > > Thank you very much, > John > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Get the answers you are looking for on the ColdFusion Labs Forum direct from active programmers and developers. http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648 Archive: http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:3062 Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
