Golly this is a long answer and I'm sorry if it sounds like an unhelpful rant. But I feel I need to make you rethink your strategy.
Forgive me for saying this, but it does rather sound as if you are reinventing the wheel and giving yourself a bunch of challenges along the way. A well set up MySQL db can handle millions of rows with little problem - especially if it is built correctly. Read this for starters: http://articles.classoutfit.com/2010/02/cakephp-basic-database-table-tuning-tips/ Cake *can* do more or less anything you want it to. To make your schema work you'd need to create a model for each table (so a model for member_us_ny, member_us_ca etc). What happens if you decide to bring in members from, say China? You'd have to add a new table and model at that time - which is not scalable, not ideal and not very easy to maintain. I know you are going to say "But we aren't planning on going to China" - but that will just demonstrate to me that you haven't thought about the future at all. In a well thought out relational database you just add a row in the countries table for the new country - and it's job done. Your way you are creating a design change - that breaks the rules. What will you do with your existing data if you decide to change your geographical spread? And how are you going to build and maintain any sort of relationships between these abstract tables - whether through tradition RI or at a query level? How are you going build cohesive indexes? How do you tie together all of your premium members, or your female members, or your members who haven't paid? Have you thought about how you would answer questions like "How many members have we got?", or "How many members do we have in Europe?", or "How many of these products did we sell in America?" or "How many products did we sell to premium members who are not in America?"? You are going to have to do a bunch of queries across a bunch of tables (even servers), get the information together and then do a whole load of processing and throw away the data you extracted and didn't need after all. All you have done is move (and grown) the problem, not solved it. But assuming that you want to go this way... The model contains the rules and logic for accessing the data, validating data before it's saved, saving it and so on. If you have more than one model for essentially the same data structure you are going to have to keep that logic synchronised if you decide to change your business rules. You'd then need controllers and views for the models - you could make these dynamic and instruct the controller to use a certain model based on the underlying table. You could even make the model dynamic by telling it which table to use at run time. But doing so would disable or impede Cake's ability to cache the model structure because it would be subject to change. And spreading different tables across different servers sounds crazy. A simple "products sold in these countries" query would involve: working out which member tables you need to query (which members are the American ones?), working out which product tables you need to query, working out which sales tables to query, working out which servers the tables are on, working out which queries you need to run, running the stack of queries, progressively storing the returned results somewhere (memory? cache? another table? another database?), combining the results, parsing the dataset and presenting the answer. Compare that with a single query to a single database on a single server - I know which I'd rather do. As far as caching is concerned, you aren't really solving or reducing the problem by changing the way you store the data. If your eventual query results in, say, 100,000 rows, then your web server will still need to handle that volume (although there are better ways of doing this). In fact, your way, you might need to bring back 700,000 rows just to find out you only needed 100,000 - or just to return a single row (see my sample queries earlier - "We sold x products in America last year"). You say you want MySQL to do the job - then I'd let it get on with it unimpeded. In my opinion, you are far better off building a pure, well indexed and well set up relational database with all your similar data in single, indexed, related tables, rather than abstracting that work and trying to manage it in PHP. All you are doing is shifting the work, adding complexity and exposing yourself to risk. Then I'd look at load balancing and hardware and reverse proxies to handle the volumes - not your way. You are trying to plough a field with a hammer because hammers are good at hitting nails. I wouldn't want to be the client still paying for design changes in five years time and I wouldn't want to be the developer who walks in after you. Maybe I have misunderstood what you are trying to achieve or your underlying business is going to be so huge and data demanding that expense is no problem - but I'd get off the ground first and deal with that (pleasant) problem later. Welcome to Cake, by the way. It's great. Jeremy Burns jeremybu...@me.com On 25 Mar 2010, at 10:06, cupidoly wrote: > I am newbie in CakePHP, using it only for 2 days. > > I am using PHP 5.2.10, MySQL 5.0.84, CakePHP 1.2.6. > > I have two questions. > > Scenario 1. While designing my site in paper, I decided to split my > tables into parts ( For example, member table will be like > member_us_ny, member_us_ca, member_intl_en, member_intl_fr, etc.) to > make it easier for MySQL manipulate member info. I know MySQL is able > to handle millions of rows, but still I think handling 10 thousands > rows is better than handling 1 million rows. This is just a concept. I > can get the user location as soon as they are accessing my site. > > Ques 1. Can I implement tables like this in CakePHP within a single db > model? > > **************************************************************** > > Scenario 2. I want to avoid relational db concept and implement my > site with structural concept, so that I can implement parts of DB in > different servers. For example, members in one server, products in > another, access_info & download_info in another server, and so on. I > know there are different options to implement this without using > MysQL. I want to do this because, I am trying to avoid MySQL Cluster > or MongoDB or MemcachedDB or anything like that. I want MySQL to do > the job. > > Ques 2. Will I be able to use multiple DB in CakePHP? > > Check out the new CakePHP Questions site http://cakeqs.org and help others > with their CakePHP related questions. > > You received this message because you are subscribed to the Google Groups > "CakePHP" group. > To post to this group, send email to cake-php@googlegroups.com > To unsubscribe from this group, send email to > cake-php+unsubscr...@googlegroups.com For more options, visit this group at > http://groups.google.com/group/cake-php?hl=en > > To unsubscribe from this group, send email to > cake-php+unsubscribegooglegroups.com or reply to this email with the words > "REMOVE ME" as the subject. Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en To unsubscribe from this group, send email to cake-php+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
