Hi i am beginner on mysql, I am blur on when do we have to use different databases when we can just store all things with many tables on one database.
I build a user login, so I create the database 'users_data', with table 'users' that store the very general of user's information (id, username, password) that's all. Next, I want to store user's preference so I create another table called 'users_preference' . But I feel like it's more robust to separate the 'users' table to another database called 'login', because when I want to develop many more other applications other than user's preference, how true or false am I ? I also grant the privilege for SELECT and MODIFY only on 'users' table but all privileges on 'users_preference' table, I some sort of knowing that we can grant privileges for a specific tables, but is it better to separate them into different database ? but how can I make combination for result retrieved from different database ? Please evaluate and give me advice, and tell me what I am wrong ? Thank you.
I think the above situation can be thought if I want to build something like Google account, that you only sign up one account, but you can use the same account on many Google website such as Gmail, Blogger, Google personalized page, Google adwords. How does this database architecture look like, I just want to know how does the database work on associating one user account to many application ? Thank you
My recommendation is to keep everything in one database, else you will be making more than 1 concurrent connection to different databases to reference data. You also have to reference them by database.tablename for select statements, which will start to get large. It's easier just to use tablename.
e.g. select * worlddb.countriestable;
better : select * from countriestable;
assuming of course your user logs into a particular database.