Can somebody plz tell me which is better. Creating different database for different sections of my site or creating all the tables in a single database? I am developing a new website and it will have many subsections like directory, yellowpages, userlogin etc. All of which will be using mysql database. Is there any advantage / disadvantage if I create different database for each sections? Thanks
If some modules on my projects are independent and usable for different projects, at that time I prefer creating different databases for them. So deployment of that modules becomes easy. Otherwise I am using one database but I am using prefixes for my tables like; directory_data, directory_lookup, users_data, yp_pagedata etc..
Nope I never see that but If you have more than 300+ tables, some frontend GUI's opens slow because they check every tables status (row counts etc).
I will recommend a single database, even if you have a big application. Single database is easy to maintain and all your data layer is on a single place. Expose modules on webservices if you want to use a single db for multiple applications. Making multiple databases is no good thing.
You will also want to consider how active each area of the site will be and how many calls to the database will be made in a short period of time with taking into account the speed of the database host to return the requested details. With the above you want to ensure your visitors are receiving the information quickly without lag. If you think each area of the site will be quite busy consistantly it may be a better idea to assign each component its own database to avoid latency issues.
Well, if you create multiple databases, you'll get it easy to backup them. (because they are separated in small files) If you have a single database, you site loads faster but you will have to wait longer to create a backup.
You don't have to open many connections. Suppose you have 3 different databases, db1, db2, db3. When you connect one of them you can access 3 of them (if you gave access or you are using root account). Sample: Select t1.field1, t2.field1, t3.field1 from db1.table1 t1 left join db2.table1 t2 on t2.field1=t1.field1 left join db3.table1 t3 on t3.field1=t2.field1