I have an idea for a site and currently am beginning the design stage of the database. Basically the site is for a local area where I will get clients to sign up and pay for advertising. The site is area specific but if this goes well I want to expand this to other areas which means additional sites. These sites could possibly overlay each other, as in one client could have advertising on two sites because they are geographically close enough.
At first I thought of just having one DB for each site, which would require multiple entries for that client in each database. But then I was thinking of having one DB for all the sites which means only one client table and then I can have a 'site' table to flag on what site(s) that client should appear in.
I have made the rough draft and tested my site with this design and it works well, my main question is how performance is with MySQL. I know DB design is critical with performance but is it more dependent on who I host with? The concern is (fingers crossed) this goes well and there will be alot of people accessing multiple sites, but would be accessing the same DB. Should I just pay the extra money and get a good shared or dedicated hosting vs my current hosting company, DreamHost.
One database will almost certainly do, and will probably be orders of magnitude easier to manage than a separate database for each web site. Be sure that your host can support the web traffic you expect... Most can, but it pays (a lot) to ask up front.