MySQL Share table between two databases

I want to know whether my approach is incorrect here

I have a simple website which you can register for. It has a main database called USERS which has a table with a list of all the users, and then for every new user a new database is generated called DB1,DB2,...,DBn. The reason I did this is because there is a lot of information being stored per user and I thought this would be a better approach. However, now I am running into the problem that the user databases need access to the table in the USERS database (for a foreign key) and I can't figure out how to do that.

I admit that I don't have much experience with MySQL, so it might be a very bad decision making a new databa开发者_StackOverflow中文版se for every user. Is this the case? How efficient is MySQL? Can I, for example, create a list for (hypothetically) 100,000 users where each user has a further 100 entries for, lets say pictures, they have uploaded.

Thanks

I'm also looking into the same issue. And the solution I found: http://drupal.org/node/37075

You can create VIEW representing your common USERS table in each database.

mysql> use DB1;
mysql> CREATE VIEW users AS
mysql> SELECT *
mysql> FROM USERS.users

This is an extremely bad design decision. What you want to do is create a single database for all of your tables in this application. In your example, you would have one database (call it MyProject). You would have a users table, call it User, that has all of your User entries in it--100,000 entries is a small database for modern hardware, so don't worry about it. Then you'll have a table called UserPhoto which contains a reference to the primary key in User as well as whatever other data you need.

You can make queries between databases using syntax

db_name.table_name.table_field