Database Question

[brandondrury]

As my various sites grow larger and larger, I find myself adding more features and more scripts all the time. For example, my home recording site has an SMF forum, Articlelive for articles, I'm considering adding Wordpress for my blogs, and now I think I'm going to add a glossary script.

My question is with all these different scripts running on one site, do I really need a seperate database for each one? I noticed a prefix thing on most of these scripts so I got to thinking that maybe I could just put any new scripts in the same database with a different prefix.

I'm totally ignorant when it comes to this sort of thing so if there is a quick read to get me up to speed, please direct me to it.

Thanks
Brandon Drury

[Slapyo]

You could create a different database for each one. But you should be fine as long as you have prefixes for each of the applications that use the database. It will definitely help cut back on the confusion.

[sketch]

It depends on whether you see one affecting another. For example, if SMF is constantly fetching SQL data like every 2 seconds, I'd look into separating just SMF into its own DB so it doesn't slow down the other programs. Otherwise, you're fine.

[DanInManchester]

Unless your looking at separating out onto separate hardware for each database server then it does't really matter. If you create another database or have tables with a different prefix your still utilising the same resources.

I guess in this scenario the issue is security as a separate database (on the same or differnt server) would offer you better control over security and users than the prefix option.

(assuming i've understood the problem correctly)

[brandondrury]

This all makes sense. Thanks a lot.

I guess as far as security is concerned, if I use one database with multiple 3 prefixes, I'm putting all my eggs into one basket. Right?

Brandon

[sketch]

I suppose since the on DB has only one password, then yes. Other than that, I don't think there's much difference. The only caveat is that if ever you should need to cross reference two or more databases, you'd have to make multiple DB connections and queries. But then good DB design would avoid that to begin with ;)

[khasmoth]

Seperate DB is the best solution to your question.It will allow you to sort everything fast.If a db crashes then you can easily trace what db needs to be paid attention.

[DanInManchester]

Separate tables in the same database shouldn't affect performance.
It may affect performance if instead of having 3 tables containing 1million records you have one table with 3 million. But even then well defined indexes and well written queries will solve most your problems.

I'd also question the crash issue....ceratinly within MS SQL server it is very easy to find where the bottle necks are and what are causing performance issues in your database.

I would only set up separate databases for distinctly separate applications. Under no circumstances would I take one application and divide it's database up. So if these websites are distinctly different you may want to do this but it won't offer you any real gains or losses unless like I said you move them to separate physical servers.

I'm not sure what database you are using but if it is what I call a 'real' it would be odd for your avarage site to reach the limits of it's capabilities unless the hardware is weedy. the chances are you can squeeze a hell of a lot more out of it through performance tuning and code optimisation but then we don't have much background to your setup so it's not a certain.