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@stripped
> From: mos99@stripped
> 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=1
>
_________________________________________________________________
Windows Live™: Keep your life in sync.
http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_022009
