2 separate databases for the same site

I know it's possible for a single PHP site to have 2 separate MySQL databases but is it a good idea?  Are there any issues with doing it?  And how would I go about it?
davideo7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ArgentiCommented:
I suppose this could be useful when you want to have one database as READ-ONLY and the other to accept adding/modifying/deleting of data by the users. However it can also be achieved by using rights on different levels in a single database too.
Issues? Maybe, as it will be a little bit more complicated for you to extract combined data from the two separate databases, as opposed to just running a query over multiple tables located in the same db.
I myself wouldn't go with such an option.
0
Ray PaseurCommented:
First of all, I would not use MySQL, since it is begin removed from PHP.  This article will explain why and what you must do to choose a different data base extension.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

That aside, it's perfectly OK to use multiple data bases.  I've seen applications that used a separate data base for each client, with hundreds of data bases in use.  I don't think I would design an application that way, but there is nothing wrong with doing it.  The specific query syntax is dbName.tableName.columnName and the "default" data base dbName if omitted will be the data base selected by http://php.net/manual/en/mysqli.select-db.php or similar function, depending on your extension.
0
ArgentiCommented:
Ray, I cannot but to incline to your wisdom.

However, I cannot agree with your statement
I would not use MySQL, since it is begin removed from PHP.

It's not the MySQL database removed from PHP! The MySQL Data Engine is actually doing just fine. It's the extension (I would call it "driver") that allows programmers to access their mySql database(s) from their php source code. And I'm really sorry to tell you, your article (even it's really well-documented) is missing to clarify that aspect. For the beginner it's very important not to get confused between the MySQL (as a database) to be used in PHP and MySQL (as an extension in PHP).

Best wishes
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Ray PaseurCommented:
@Argenti: That is exactly why I wrote the article!  I think that you have an excellent suggestion, and I will clarify it in the article.  Obviously this paragraph didn't come through well enough.

Do I Have to Change my Data Base Tables?
No.  We are changing the API extensions, but we are not changing the underlying data base tables or the essential nature of the query language.


Best regards, ~Ray
0
ArgentiCommented:
It's not about the Data Base Tables, Ray.
It's about the database engine, which remains the same, not being affected by the PHP change.

I know I can be very annoying with that, but some novice developers won't make a difference between the exact same tables inside two different database engines (say MySQL and PostgreSQL).

Nevertheless, at this point I'm afraid we are about to get off-topic...
0
Ray PaseurCommented:
It's not about the Data Base Tables, Ray.
Hopefully that message came through where I wrote
Do I Have to Change my Data Base Tables?
No. We are changing the API extensions,...
The article is only about MySQL API extensions, not PostgreSQL.  If you find any inaccuracies in the article please let me know, thanks. ~Ray
0
Tomas Helgi JohannssonCommented:
Hi!

>I know it's possible for a single PHP site to have 2 separate MySQL databases but is it a good idea?  Are there any issues with doing it?  And how would I go about it?

Well, for the first thing. Yes you can have 2 or more separate MySQL databases that a single Web site/application  is accessing (regardless what programming language you have).
This is all a design issue on the database level. A MySQL Instance (on a host) can have 1 to many databases that are running under many database engines (MYISAM, InnoDB, Memory, NDB  etc).
You can have one database holding sales tables other database holding product related tables. Other scenario would be one database that has all ecommerce tables in it while another database (either on same instance/host or different) is a replicated version of the ecommerce database in case of disaster recovery or load balancing.

So it comes down to what the websites/application requirements you have in regards of data integrity, backup and disaster recovery, load balancing, calculated and predicted database size as well as database performance.

Regards,
    Tomas Helgi
0
Chris StanyonWebDevCommented:
Is it possible? Yes

Is it a good idea? That depends! If you have 2 databases, ask yourself why? If it's a genuine requirement (performance / backup / security / different servers ), then go for it.

If on the other hand it's just 'evolved that way over time' then there's a fair chance that you'd be better of re-designing one database from the ground up.
0
Dave BaldwinFixer of ProblemsCommented:
I have a site that has two separate databases so that the 'everyday' users that have to upload data don't get into tables that they shouldn't.  But of course, today they managed to screw the table they were supposed to update.  But they didn't touch the other database.
0
davideo7Author Commented:
Ray_Paseur: What database engine would I use for PHP if I didn't use MySQL?  If I only have knowledge of MySQL, how would the transition be to another database engine?  And what would be the benefits of switching to the database engine that you're suggesting?
0
Tomas Helgi JohannssonCommented:
Hi!
I want to apologize and correct some confusing sentence in my previous comment

This is all a design issue on the database level. A MySQL Instance (on a host) can have 1 to many databases that are running under many storage engines (MYISAM, InnoDB, Memory, NDB  etc).

Different database engines (DBMS ) than MySQL are like Oracle, DB2, SQL Server, PostgresSQL server etc.
If you have solid SQL knowledge (knowledge of DDL, DML and TCL) see this link for more info
http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands

then your transition between DBMS will be fairly easy and your focus will be on learning the control commands and the OS environment rather than SQL. And you will learn how DBMS behave differently and are optimal for certain solutions rather than other DBMS systems.

But if you want a DBMS that are free and robust then I recommend MySQL as it is easy to use and learn. And you can setup simple to very complex DBMS system with replication, load balancing etc.

Regards,
     Tomas Helgi
0
Chris StanyonWebDevCommented:
@davideo7 - I think some clarity on the terminology here is in order. On your server you will have a database - it's probably a mySQL Database. That's fine.

Now, in your PHP code, you need to use a PHP Database Library to access that database - the main 3 libraries in PHP are called MySQL, MySQLi and PDO.

Traditionally you would have used the MySQL PHP Library to access your MySQL database.

It is the MySQL Library in PHP that is deprecated and should not be used anymore, which leaves you with 2 choices for database access in PHP instead of 3 - MySQLi and PDO. They all do the same thing - access your database - so you wouldn't need to change the database - only the library you use.

Which one you choose will depend on several factors, but you will have to choose one. If you're already comfortable with the MySQL Library, then the easiest transition would probably be to go for MySQLi : mysql_query() = mysqli_query() - see the similarity

PDO on the other hand has the advantage of not being tied directly to a MySQL Database = it can access all sorts of database engines - interesting if your 2 databases are ever likely to be running off of different engines: MySQL & MSSQL for example. PDO could access them both seemlessly
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
davideo7Author Commented:
ChrisStanyon: I'll make the switch from MySQL to MySQLi right away but what could happen if someone uses MySQL?
0
Dave BaldwinFixer of ProblemsCommented:
The SQL is the same whether you use the old 'php_mysql' driver or the newer 'php_mysqli' driver.  There are some differences in the syntax of some commands between the two drivers but that affects your code, not the data in the database.  I have several PHP pages that I have converted to the 'mysqli' driver and I did not change any of the SQL to do that.

If someone else is still using the older driver, even on the same database, it doesn't make any difference (until the driver is not supported anymore).
0
Chris StanyonWebDevCommented:
If someone continues to use the MySQL Library then their code will eventually fail. If the ISP that hosts the site decides to upgrade their version of PHP, then the MySQL Library will no longer be available, so the code won't run.

Usually these deprecation notices give you plenty of time to get things in order before dropping support completely, but this one has been around for a while, so make the switch now to prevent any problems in the future.
0
Dave BaldwinFixer of ProblemsCommented:
According to php.net, 'mysqli' was introduced in 2004, about 9 years ago...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.