I vote for 1 table per TableType this will keep your DB schema consistent with Architecture
Martin ______________________________________________ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Tue, 10 Feb 2009 11:03:46 -0600 > To: mysql@lists.mysql.com > From: mo...@fastmail.fm > Subject: Re: InnoDB: Thousands of Tables or Hundreds of Databases? > > At 04:30 AM 2/10/2009, you wrote: > >Thanks for your comments Mike. > > > >The largest table contains 48 columns (objects), the second largest 20 > >columns (users) and all the rest are less than 10 columns. The instance > >sizes range from 10MB to 1GB. > > > >Transactions and row locking are required. Most queries are updates, > >followed by writes, then reads (application mostly uses memcached and other > >forms of caching for reads). > > > >I have since thought of having 1 table type per database, resulting in > >'only' ~30 databases; this would be 'easier' to maintain, and each database > >(containing 1 table type) could be optimised for its ratio of reading : > >writing : updating. > > > >However, this approach would require a LOT of work to re-write the > >application's database layer. > > > >What approach would be best? > > Michael, > Does the saying "between a rock and a hard place" sound > familiar? :-) > > I feel you're going to have to create a test suite to benchmark both > solutions thoroughly before you start on the application code. You're going > to find pro's and con's with both designs but after benchmarking you're > going to know which one performs better both from a speed viewpoint and > maintenance viewpoint. The more time you spend testing the design, the more > confidence you'll have that it works and the less chance of throwing it > away and starting over later on down the road. Then you'll also be able to > present to your client some hard facts about each design. > > Mike > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com > _________________________________________________________________ Windows Live™: Keep your life in sync. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_022009
