Hello everyone,
I'm developing a "community system", that has things like members, roles, events, etc. Currently everything is in multiple tables of a single MySQL database. It looks something like this...
-------------------------------------------
Communities - community_id PK, other_info Members - member_id PK, other info Roles - member_id FK, community_id FK Events - event_id PK, community_id FK
-------------------------------------------
Etc. There are about 25 tables total.
It's been recommended to me that putting each community in it's own databases would be better. Something like this:
-------------------------------------------
Communities - community_id
-----
Community_id_database
Members - member_id PK, other info Roles - member_id FK Events - event_id PK
-------------------------------------------
So.... Now I'm trying to figure out if it really would be better or not, and that's what I'm asking for your help with. Here are some of the pros and cons, as I see them:
PROS: ------------------------------- Less chance of database corruption bringing down all communities Possibility of more security Possibility of more customization
CONS: ------------------------------- More difficult to administer Difficult to upgrade the system More difficult to inter-relate the communities/members More server load (??? - Using PEAR, I think each separate database would be a separate connection)
Would the answers to this question be any different if I were using PostgreSQL, MSSQL or DB2?
Just look at the pros and cons that you've listed and the answer is obvious. You're going to create a more complicated system with multiple databases that'll be harder to work with for the _possibility_ of some other features? It's not worth it and security and customization can be done just as easily with one database instead of many.
Bottom line: use a single database.
--
John Holmes
php|architect - The magazine for PHP professionals - http://www.phparch.com
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
