[thelist] Single or multiple MySql databases.

[Duncan Hill]

On Wed, 22 Oct 2008 16:35:14 +0100, Jason Handby  
<jason.handby at corestar.co.uk> wrote:

>> On Wed, Oct 22, 2008 at 4:17 AM, Duncan Hill <dunkaz at gmail.com> wrote:
>> > I am building a new site that makes use of normal static html pages
>> plus 2
>> > separate Wordpress blogs, aMember membership management, and PHPlist
>> for
>> > newsletter posting etc.
>> > Apart from the static pages, the other apps all run from MySql
>> databases
>> > ... what are the pros and cons of using either a single database
> that
>> they
>> > all access, or individual databases for each app.
>> >
>> > I don't need to run any special queries on the databases (that I can
>> > foresee).
>> >
>> > Many thanks for any words of wisdom.
>>
>> I assume you mean databases on the same server rather than separate
>> database servers. If you put all the tables in the same database, it
>> is somewhat more difficult to make sure the user for wordpress does
>> not have access to aMember tables, etc. You would also need to make
>> sure that table names do not conflict across applications. I don't see
>> any benefit to putting all the tables in the same database in MySQL.
>
> I'd agree with Matt -- unless you have a specific reason for wanting
> everything in the same database, I'd keep them separate.
>
> The example he gives of user permissions is a good one. It doesn't
> perhaps matter so much if everything is being administered by the same
> person to begin with; but what happens if you get someone else to work
> on the blogs, or the newsletter, and want to give them access to only
> the relevant bits of the database? Another potential future headache is
> if you have to move a chunk of your work to a different host, e.g. for
> ownership or management reasons. Much easier to use a separate database
> from the outset, than to have to tease them apart later.
>
> Some hosting plans charge extra for having more than one MySQL database,
> so maybe that's what lies behind your question. If so, bear in mind that
> the saving from having just one database might be outweighted by the
> future cost of dealing with all of these complications.
>
>
> HTH
>
>
>
> Jason
>
Thanks Jason,

There is no problem with my server for multiple databases but the site is  
very much in development at the minute, I think that with the points that  
you and Matt have made I'll go for the separate databases.
Who knows what future features may or may not end up in the site and I  
certainly wouldn't like to have to pick them apart again.

A little bit of 'all the eggs .....' to consider as well. I don't have the  
database experience to take the risk.
I am busy setting up a test on separate databases and will probably stay  
with that. No-one has come up with compelling advantages to go with a  
single so I'll go traditional.

Thanks to all for the advice.

Duncan