I've been working a long time on upgrades for the site and some of it is going to involve using mysql databases.

We are trying to get rid of the multiple logins to each program; forums, classifieds, showroom etc.

The programs I"m working with have the option of using one (shared) mysql database for everything, with different table prefixes for each one to keep the program data separate.

This spooks me and I setup individual databases for each program, all using one MAIN database only for the usernames. Data for each one is kept separate.

Is this wrong? My thinking was "if one gets toasted, then only one has to be restored/fixed or re-created". Perhaps the loads will be too high this way? Just not sure and thought maybe I should ask here before things move past the testing stages. I"m new to mysql and it shows.

I hope the explanation makes sense, been buried in this for the past while and have lost track of reality.

thanks... Al

hey Al
I would say off the top of my head use the single databse option if you can. But I'll need the specs of the server and if any other sites are hosted on to help determine which is best. Tim (TronDD) can also help out in this area.

Al, I have 5+ yrs. experience with MS SQLServer but none with MySql. I can offer some generic database advice but if Matt or Tim have MySql experience then I’ll defer to them.

As Matt suggested using a single database is the general rule of thumb, I’ll list some of the reasons it’s recommended.

Maintenance, more databases, more maintenance tasks/jobs to setup and manage. Full backups, transaction log (incremental) backups, index maintenance, table defragmentation. I’m using MS terminology but I’m sure similar tasks are required for MySql. I suspect, only a guess based on how I think this database is used, that index and table fragmentation are probably not issues.

Synchronization, when you have related data spread across multiple databases it becomes more difficult to assure that all the data remains in synch. Again this may not be an issue here. I doubt that little if any synchronization is required between the forums and the showrooms or classifieds. Again this is only a guess but if correct may be a good case for using separate databases.

Logins or authentication or permissions, terminology. Typically maintaining this in a single database is less involved than maintaining logins for multiple databases and again making certain they are synchronized. It appears you have an approach to deal with this by using a separate MAIN database only for the usernames. I’m not certain, that’s how I read it. You’ll access the user database to determine their permission to access other areas? Am I understanding you right?

Anyway, those are some of the issues or more correctly possible issues. So you have to decide if they are issues and if so, do you have a plan to deal with them. A single database is just a guideline because of some of the possible issues mentioned. There may well be reasons not to follow that guideline.

The last thing you mentioned was ‘loads being too high’. I’m not certain what is meant by that. Loads imposed by multiple databases vs. a single database? If this was MS SQLServer I would say that is not a concern. A quick google and 2 minute read says MySql is both mature and robust and I suspect the impact should be negligible. Perhaps you meant accessing the MAIN database all the time for user info? There are ways to deal with that if that is your concern. After weighing the options if you still want to go with separate database and access to the MAIN user database is a concern, I can offer some suggestions on design that may help. I’ll wait on that because I’m not certain it’s an issue.

If I can offer any other thoughts or advice feel free. Again, hopefully Matt or Tim have real world experience with MySql.

Al, if the main database goes down and all the programs depend on it for user names then won't all the programs be affected? My point is that even if the databases are separate there is still a critical depndency on the main database. If that's the case then why distribute the data where program isolation is the primary criteria? For reliabilty have you considered a single database with redundancy? For traffic and load could you have two servers that share the load? These are just ideas for you to ponder. I'm not a database expert but I have worked with distributed, inter-relational databases enough to know that a failure anywhere in the network can cause issues across the board. Finally makes some excellent points about the maintenace headaches and synchronization issues. my .02.

Thanks for the replies. I've just emailed one of the companies that the main database is relying on. I think there might be some concerns about it not being supported if any other programs share it. Not sure.

I've heard of problems with too many database connections open at once and the server choking, that's what I meant by load. Sometimes we hit 400 on chevelles.com at one time just for the forum, now if the ads and such were included it could be quite a bit.

Right now, one database holds the forum info and supplies 'only' the member username and password to the others. If it goes down, they all will, but if just one of the others quit, it won't affect anything. I guess my concern was all the eggs in one big basket.

I appreciate the replies and will evaluate all the suggestions.

That cleared things up a little for me. I'm still not familiar with the programs being used but I suspect that some type of 'connection pooling' is being used. It's almost a requirement in this type of environment. If connection pooling is being used then multiple database should not have any affect on the total number of connections open at anytime. You'll have to determine if connection pooling is being used, but if it is it should alleviate your concerns about load.