More databases or more tables? Which is better?

BillyConnite · 02-27-2011, 08:52 PM

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] · 02-28-2011, 05:14 AM

I just couldn't pass your post by without replying

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

sourcez · 02-28-2011, 02:40 PM

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?

BillyConnite · 02-28-2011, 03:46 PM

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.
????: NamePros.com http://www.namepros.com/programming/704428-more-databases-or-more-tables-better.html

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 · 03-01-2011, 02:23 AM

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?

BillyConnite · 03-01-2011, 04:03 AM

Originally Posted by 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.
????: NamePros.com http://www.namepros.com/showthread.php?t=704428

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 · 03-02-2011, 02:47 PM

Sweet well good luck!

Peter · 03-08-2011, 04:26 PM

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.

BillyConnite · 03-08-2011, 08:12 PM

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.
????: NamePros.com http://www.namepros.com/showthread.php?t=704428

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

, 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 · 03-09-2011, 08:48 AM

Would be interested in seeing it

BillyConnite · 03-09-2011, 02:00 PM

Originally Posted by sourcez

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.
????: NamePros.com http://www.namepros.com/showthread.php?t=704428

-Rhett

Peter · 03-10-2011, 11:52 AM

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 · 03-18-2011, 06:16 AM

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

BillyConnite · 03-29-2011, 06:48 PM

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.
????: NamePros.com http://www.namepros.com/showthread.php?t=704428

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

02-27-2011, 08:52 PM	THREAD STARTER #1 (permalink)
BillyConnite Join Date: Jul 2005 Location: Coffs H, Australia Posts: 3,456	More databases or more tables? Which is better? 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. __________________ Free Forums / GoDaddy Coupon Codes (NEW DOMAIN!) / Free Arcade Script / <?='Your computer is '.(1?fine:broken).'.'?>

02-28-2011, 05:14 AM	#2 (permalink)
un4given[MAD] NamePros Member Join Date: Aug 2008 Location: Solar System \ Earth \ Ukraine \ Kiev Posts: 43	I just couldn't pass your post by without replying I've googled and found something which may be useful for you. __________________ Tired playing Freecell, Klondike or Spider? Discover 800 solitaire games at SolitairesUnlimited.com and play'em all!

02-28-2011, 02:40 PM	#3 (permalink)
sourcez NamePros Regular Join Date: Nov 2007 Location: UK Posts: 403	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? __________________ 3cc Internet

02-28-2011, 03:46 PM	THREAD STARTER #4 (permalink)
BillyConnite Join Date: Jul 2005 Location: Coffs H, Australia Posts: 3,456	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. ????: NamePros.com http://www.namepros.com/programming/704428-more-databases-or-more-tables-better.html 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 __________________ Free Forums / GoDaddy Coupon Codes (NEW DOMAIN!) / Free Arcade Script / <?='Your computer is '.(1?fine:broken).'.'?>

03-01-2011, 02:23 AM	#5 (permalink)
sourcez NamePros Regular Join Date: Nov 2007 Location: UK Posts: 403	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? __________________ 3cc Internet

03-02-2011, 02:47 PM	#7 (permalink)
sourcez NamePros Regular Join Date: Nov 2007 Location: UK Posts: 403	Sweet well good luck! __________________ 3cc Internet

03-08-2011, 04:26 PM	#8 (permalink)
Peter NamePros Expert Join Date: Nov 2003 Location: Scotland Posts: 5,074	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. __________________ Manage your portfolio using my new Domain Portfolio Management script. Securing Your Domain Name From Theft

03-08-2011, 08:12 PM	THREAD STARTER #9 (permalink)
BillyConnite Join Date: Jul 2005 Location: Coffs H, Australia Posts: 3,456	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. ????: NamePros.com http://www.namepros.com/showthread.php?t=704428 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 , 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. __________________ Free Forums / GoDaddy Coupon Codes (NEW DOMAIN!) / Free Arcade Script / <?='Your computer is '.(1?fine:broken).'.'?>

03-09-2011, 08:48 AM	#10 (permalink)
sourcez NamePros Regular Join Date: Nov 2007 Location: UK Posts: 403	Would be interested in seeing it __________________ 3cc Internet

03-10-2011, 11:52 AM	#12 (permalink)
Peter NamePros Expert Join Date: Nov 2003 Location: Scotland Posts: 5,074	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. __________________ Manage your portfolio using my new Domain Portfolio Management script. Securing Your Domain Name From Theft

03-18-2011, 06:16 AM	#13 (permalink)
jacobatwood NamePros Member Join Date: Mar 2011 Posts: 47	In this case it should be better to use multiple databases instead of increasing the no. of table. __________________ █ A home to Virtual Machines! █ Onshore/Offshore Managed VPS █ Managed HardCore Ultimate VPS Offshore/Onshore

03-29-2011, 06:48 PM	THREAD STARTER #14 (permalink)
BillyConnite Join Date: Jul 2005 Location: Coffs H, Australia Posts: 3,456	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. ????: NamePros.com http://www.namepros.com/showthread.php?t=704428 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 __________________ Free Forums / GoDaddy Coupon Codes (NEW DOMAIN!) / Free Arcade Script / <?='Your computer is '.(1?fine:broken).'.'?>

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
32 Original MySQL Niche Content Databases On Sale (Industrial Data & Facts)	immediate	Content For Sale	0	12-22-2009 06:19 PM
Emshost.com Offer Free Cpanel11 Hosting For All Type Of site	emsplanet	Web Hosting Offers	2	03-08-2008 03:24 AM
$8.95 for 50 Databases with Resale Rights	jdk	Content For Sale	0	09-03-2007 06:33 PM
Css help indow resizing problem	Phat-Cat	Web Design Discussion	25	06-02-2006 11:09 PM
HOWTO: Backup and Restore your MySQL databases with phpMyAdmin	deadserious	Webmaster Tutorials	2	04-05-2004 02:15 PM