Accessing two databases with one username

[Jason C]

I have an existing website that uses a MySQL database with 60 tables.

I'm working on a new website that will also use a MySQL database, with about 40 tables. However, one of these tables (`users`) will need to be identical for both sites, and inserts/updates come from the public so it will change often.

I edit quite a bit through phpMyAdmin, so I don't really want to have 40 new tables in the same database. Plus, if the new site works out then I'll probably create more sites like it, and definitely don't want to add 40 tables to the same database for each new site! That would make editing content unnecessarily complicated.

Instead, I would much rather have a new database for each site, but have the one table shared across all accounts.

Can you guys suggest the best way to accomplish this? I know of 2 ways to do it, I just don't know if either are a good design decision, or if there's a better option that I haven't considered.

1. I can create a second database, create a user that has access to both databases, then access `users` like:

"SELECT * FROM database2.users"

This seems like the smarter decision to me, am I correct that I wouldn't be using "mysql_select_db", and would have to specify the database in every query? That's a lot of room for error.

2. I can connect to both databases at once, like:

$dbh1 = mysql_connect($hostname, $username, $password);
$dbh2 = mysql_connect($hostname, $username, $password, true);

mysql_select_db('database1', $dbh1);
mysql_select_db('database2', $dbh2);

mysql_query("SELECT * FROM users", $dbh1);

I'm not sure how efficient this is, though. Does it slow everything down if I connect to multiple databases at once?


Is there a third option that's better?

If it matters, I access through both PHP and Perl (DBI module).

[Erick T. Barkhuis]

Jason C:

>I have an existing website that uses a MySQL database with 60 tables.
>
>I'm working on a new website that will also use a MySQL database,
>with about 40 tables. However, one of these tables (`users`) will
>need to be identical for both sites, and inserts/updates come from
>the public so it will change often.

[two options, same or different database]

>Is there a third option that's better?

To me, this sounds like crying for a 'users management application',
separately from the content databases.
The thought would be:
- create one spot, maybe a separate database, for the users management
- there, create one users table
- probably, depending on your requirement to be able to link users to
databases/sites: create one DB/site-table with ID and characteristics
of each site
- if there's a n:m relationship between users and sites, create a
relationship table between them
- create a general script that supplies information, perhaps in XML or
JSON format
- create a script that processes update requests

Once you have this in place, you'd have a system to store and manage
user information across sites. Your API (the scripts that access that
users database) will handle requests from your websites. All you need
to do is define interface functions and XML/JSON data sets with which
your sites will interact with the users application.

To get the users data for all users of one website, in PHP you'd do:
$userXml = file_get_contents("https://../api.php?site=3");

To get the sites data for one user, in PHP you'd do:
$userXml = file_get_contents("https://../api.php?usr=JohnDoe");

etc.

Updates and inserts would go through the API as well. Just create a
script that websites can post data to. Have this script perform the
update and return a status code or error message.

Such architecture allows you to keep users and their sites
relationships centrally in one place (easy backup) and have the data
accessible from everywhere. Big advantage is the scalability: you'd be
adding sites and users quickly and easily.
Of course, you'd need some security measures to make sure that not
anyone can access your API scripts.

--
Erick

[Thomas 'PointedEars' Lahn]

Jason C wrote:

> I have an existing website that uses a MySQL database with 60 tables.
>
> I'm working on a new website that will also use a MySQL database, with
> about 40 tables. However, one of these tables (`users`) will need to be
> identical for both sites, and inserts/updates come from the public so it
> will change often.
>
> I edit quite a bit through phpMyAdmin, so I don't really want to have 40
> new tables in the same database. Plus, if the new site works out then I'll
> probably create more sites like it, and definitely don't want to add 40
> tables to the same database for each new site! That would make editing
> content unnecessarily complicated.
>
> Instead, I would much rather have a new database for each site, but have
> the one table shared across all accounts.

Understandable.

> Can you guys suggest the best way to accomplish this? I know of 2 ways to
> do it, I just don't know if either are a good design decision, or if
> there's a better option that I haven't considered.
>
> 1. I can create a second database, create a user that has access to both
> databases, then access `users` like:
>
> "SELECT * FROM database2.users"

Exactly, but quote names.

> This seems like the smarter decision to me, am I correct that I wouldn't
> be using "mysql_select_db", and would have to specify the database in
> every query? That's a lot of room for error.

Yes, yes, no.

> 2. I can connect to both databases at once, like:
>
> $dbh1 = mysql_connect($hostname, $username, $password);
> $dbh2 = mysql_connect($hostname, $username, $password, true);

You should not use the deprecated unsafe “mysql” PHP extension.

<http://php.net/mysql_connect>

> mysql_select_db('database1', $dbh1);
> mysql_select_db('database2', $dbh2);

This is not only deprecated but also pointless, equivalent to “USE”
statements on separate connections. There can only be one primary database
for a connection, and you can only use one connection in a query.

IMHO this approach only makes sense if the databases are located on
different hosts whose MySQL servers cannot talk to each other but all of
them can talk to your PHP host. But then you will have to do the
correlation between the data yourself, with the programming language used to
establish the connections.

> mysql_query("SELECT * FROM users", $dbh1);

Therefore, this would be equivalent to attempt to query `database1`.`users`.
The connection identified by $dbh2 is not used here.

> I'm not sure how efficient this is, though. Does it slow everything down
> if I connect to multiple databases at once?

Probably yes.

> Is there a third option that's better?

Three databases, and a query that accesses two of them, with one being the
primary one (so that you would not have to write its name in column
references in the query). One that holds the data for one application, one
that holds it for another, and one that holds the common user data. This is
especially recommended if you know that it is only the common user data that
would be updated frequently, as then you can run a specially designed
optimization on the user database.

If I am not mistaken, this would also allow for using foreign keys across
databases with InnoDb, allowing you to keep referential integrity easily.

Or you could have a view in each of the application databases querying the
table(s) in the user database, and then access this view in application-
related queries. The disadvantage of MySQL views in this regard is that
they are not static (their content is built when they are used, not when it
is created or the tables they query are updated), therefore potentially less
efficient than a table in another database. (Again, untested.)

> If it matters, I access through both PHP and Perl (DBI module).

For PHP, use the “mysqli” or “PDO_Mysql” extension instead. At least use
something other than “mysql”, and something that supports Prepared
Statements.

And either do not post using Google Groups, or work around its many flaws.
Your lines are way too long (the convention is a maximum of 80 characters
per line, a special case being logfile extracts, for example). A full name
would be polite, too, “Jason C” #4711.

--
PointedEars

Twitter: @PointedEars2
Please do not Cc: me. / Bitte keine Kopien per E-Mail.

[Gordon Burditt]

> I have an existing website that uses a MySQL database with 60 tables.
>
> I'm working on a new website that will also use a MySQL database,
with about 40 tables. However, one of these tables (`users`) will
need to be identical for both sites, and inserts/updates come from
the public so it will change often.

How often do you use the user table in joins with other tables? Is
the user table ever *modified* based on joins with other tables?

You may have a situation (hopefully) where application #1 (User
Maintenance / Creation / Deletion) is the only one to *WRITE* the
users table, and application #2 (Your existing site) and application
#3 (Your new site) only read it. Does the User Maintenance application
need to read the other tables?

If User Maintenance is too tightly tied to the other applications,
this could get messy (this does not prohibit the other applications
having links to the "Change Your Password" or "Create An Account"
pages which are part of the User Maintenance application).

It might be possible (but I'm not sure how well-supported it is)
to put the users table in its own database and use replication to
replicate just that database. You can definitely put the users
table in a database on a different host and replicate that. Here
I presume that User Maintenance needs *only* the users table.
Replication of a single database and cross-database queries in
general don't mix well, but if all of the cross-database queries
involving the users table are read-only on the users table, I think
it will work.

> I edit quite a bit through phpMyAdmin, so I don't really want to
have 40 new tables in the same database. Plus, if the new site works
out then I'll probably create more sites like it, and definitely
don't want to add 40 tables to the same database for each new site!
That would make editing content unnecessarily complicated.

> Instead, I would much rather have a new database for each site,
but have the one table shared across all accounts.

I don't think that's practical. You might have that one table
in a different (possibly replicated) database. Replication avoids
the problem of all your sites going down (can't validate users)
if the user database goes down, but you still have the issue that
user *updates* go down if the user database master goes down.

> Can you guys suggest the best way to accomplish this? I know of
2 ways to do it, I just don't know if either are a good design
decision, or if there's a better option that I haven't considered.

> 1. I can create a second database, create a user that has access
to both databases, then access `users` like:

> "SELECT * FROM database2.users"

Are most accesses like that, or are they more like:

SELECT * FROM database2.users
WHERE username = '$user_name_input' AND
encrypted_password = pw_encrypt_function('$user_pw_input');
(and if you don't get a record back, the login failed)

where $user_name_input and $user_pw_input are properly escaped first
(or better, use prepared statements) and the password is encrypted
(if not in SQL, then in PHP or Perl). 'username' presumably has a
unique index on it.

Scanning the whole user list may be way slower than selecting
the single relevant user record, depending on how many users
you have.

> This seems like the smarter decision to me, am I correct that I
wouldn't be using "mysql_select_db", and would have to specify the
database in every query? That's a lot of room for error.

If you make sure NOT to have a users table in any other database,
it's an error that will be caught quickly when no one can log in.

Use of mysqli_select_db() can be a lot harder to debug when one
section of code *USUALLY* (but not always, perhaps only in an error
situation) puts the database back to the way it was, and occasionally
you execute a query elsewhere in the code (unrelated to where the
problem is) in the wrong database and have trouble figuring out why.
Using mysqli_select_db() before every query can be slow.

> 2. I can connect to both databases at once, like:
>
> $dbh1 = mysql_connect($hostname, $username, $password);
> $dbh2 = mysql_connect($hostname, $username, $password, true);
>
> mysql_select_db('database1', $dbh1);
> mysql_select_db('database2', $dbh2);

Use $dbh1 = mysqli_connect($hostname, $username, $password, $database1);
and $dbh2 = mysqli_connect($hostname, $username, $password, $database2);

where the main reason for using a common username and password is to ensure
that both databases are compromised at the same time :-( There is no necessary
reason to put the databases on the same host, either.

If you set up two connections, you will need to use the correct connection for
every query.

The User Maintenance app connects $dbh1 to the database with the users table
*READ/WRITE* and doesn't use $dbh2.

The other apps connect $dbh1 to the database with the users table *READ ONLY*
and uses $dbh2 for the other tables (probably READ/WRITE).

> mysql_query("SELECT * FROM users", $dbh1);

Use mysqli_query().

>
> I'm not sure how efficient this is, though. Does it slow everything
down if I connect to multiple databases at once?

Maybe a little. Probably not as much as a "SELECT * FROM users"
query. It depends on how much other work each page hit does.