More databases or more tables? Which is better?

[yeppers]

Hi all,

I've just created a multi-phpbb-forum script, and have set it up to create a separate database for each forum, as opposed to just inserting new tables into 1 database, with a different prefix for each forum. (This has been done most of the time ive found).

Is there any down-side to having thousands of databases? As opposed to 1 giant database with tens of thousands of tables?

I would think mysql can easily handle thousands of separate databases. Just wondering your thoughts.

Thanks,
Rhett.

 

[un4given[MAD]]

I just couldn't pass your post by without replying :lol:

I've googled and found something which may be useful for you.

 

[sourcez]

How will you operate the tables? I'd imagine the main constraint is how the phpbb forums are updated - how are the tables related to physical files?

 

[yeppers]

Thanks for the link, un4given[MAD]. They have some good points there, some don't apply to this situation but i'm happy i went with multiple databases for most of the same reasons they suggest.

Hi sourcez,
Upon registration and verification the new database and user are created, and the user is granted privileges to that database only. Their mysql database and user information is also stored in a single table in the sites database. The phpbb default tables are then inserted into their database, with necessary modifications.

If at any stage i have to edit phpbb tables in 1 database, i can just create a script to make the same modifications in all databases. For instance if phpbb releases an update that alters the database, i'm quite sure i can figure out a way to loop it to modify each database.

As for the phpbb files, there is only 1 set of files, to keep disk space to a minimum. If i provide seperate files for each database also, this begins to take up a considerable amount of space. Although this way makes things MUCH simpler, and doesn't require editing of the phpbb system in the way i have. I'm currently working on a way to provide a seperate set of template files though, so each user can edit template files to an extent. I realize i can store template information in a database, but that runs against my grain.

The main reasons i'm going with different databases is because i believe VERY large databases crash more often, and yield much slower results. It's also harder to backup data. I can easily backup thousands of smaller databases.

-Rhett

 

[sourcez]

Yeah I'd agree with you on your db point; it'll allow you much more control over larger databases too so I'd probably do it in seperate db's.

I'd think for the templates you just need to give each user their own template folder which is a copy of the standard one (which they can edit). You could use mod rewrite and php to give each user access to a different one and no others?

 

[yeppers]

Yeah I'd agree with you on your db point; it'll allow you much more control over larger databases too so I'd probably do it in seperate db's.

I'd think for the templates you just need to give each user their own template folder which is a copy of the standard one (which they can edit). You could use mod rewrite and php to give each user access to a different one and no others?

Hi sourcez,

My knowledge of modrewrite is basic unfortunately, i did actually try that but failed. I think that modrewrite only works with externally called urls. So including or processing files within a php script ignore's that completely, it's just using filesystem paths i would assume. I did try experimenting with filesystem links, that's one way reading templates initially, but not editing thereafter.

I ended up editing about 500 variables within the phpbb files to create a dynamic template path. Took a few hours to get right, notepad++ is an invaluable tool!!! God i love it. It works.

I have a forum setup with a demo mod, so that you can change the template easily, for demo viewing. The user can hit install and it's copied to their template folder

Everything seems to be falling in place.

-Rhett.

 

[sourcez]

Sweet well good luck!

 

[Peter]

To be honest making so many modifications was not really necessary. The main thing you would have needed to do is have some way for the script to differentiate between each forum either via a dummy folder name that was caught with mod rewrite or a sub domain again the name caught by mod rewrite. Then I personally would have had it so that each db had a seperate config file maybe named after the folder/subdomain. The default config file would then just need to check if the forum exists then include the relevant config file.

There used to be a fairly popular script made by someone called sebflipper that did this and a bit more but I beleive that project is now defunct.

 

[yeppers]

Hi Peter,

Yes i remember sebs script .

The only reason i made so many modifications to variables paths was templates... I tried various ways to do this without editing variables, but this proved to be impossible (i believe), tried linking and mod rewrite, but mod rewrite will only work externally (out of script), if you know what i mean.

Now each person has their own separate template folder, while still running on the same phpbb script (The script itself takes a considerable amount of space). They are given 1 default template in that folder, to minimize space. They can then add templates via a demo template system i have, the template is automagically copied to their template folder and can be installed. Once they reach 5 templates in their folder, they have to delete one before adding more . This seems to be a great system to minimize disk space, and maintain customization for the user. It really didn't take too long with Notepad++'s awesome replace system.

Each forum has their own separate config file which is pointed to via mod rewrite and their subdomains value. I've made the necesssary adjustments to phpbb's admin system to rule out their doing anything naughty in their :P, the only hard part is making sure that data entered into the template editing system can't be used for malicious purposes...

Seems to be working fairly well with separate databases, the system works, just needs fine tuning. I'll post a link once i'm done if anyone's interested to see it working in action.

-Rhett.

 

[sourcez]

Would be interested in seeing it

 

[yeppers]

Would be interested in seeing it

No worries, should be done within a week, i was waiting on a domain name to be registered for weeks, and have been screwed over lol. Got a different domain yesterday, so i can start work again.

-Rhett

 

[Peter]

Yeah once up by all means send me a link to have a look will be interesting to see.

I was planning on making such a script for a particular project I was planning (not a forum host but a site that hosts a forum for each profile, may not make sense but if explained properly it would). Never got around to doing it properly yet thos as it would be completely integrated into a site and want to use the same authentication system throughout.

 

[jacobatwood]

In this case it should be better to use multiple databases instead of increasing the no. of table.

 

[yeppers]

Well after a lot of work it's functional.

For those interested: http://forums.do/ is ready to be tested.

Keep in mind the logo hosting system, and premium upgrades system are not yet active.

Feel free to test it out in any way you want, i'd love to make sure it's secure, i've taken a lot of time to ensure it's secure and safe. I hope it's been enough.

Given the testing i've done, i'm fairly certain multiple databases and users was the way to go. Everything's individualized, users have access only to their own db, and can only select, insert, update and delete within their own database. Creation privileges are revoked after tables are added.

Given this forum isn't meant for discussing the website itself, if you have any thoughts it's probably better to PM them to me or discuss it in another forum.

Thanks all,
-Rhett