Page 1 of 1

When do we use different databases ?

Posted: Thu Mar 22, 2007 10:19 am
by keenlearner
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

Posted: Thu Mar 22, 2007 11:46 am
by RobertGonzalez
Database servers hold many databases. Databases store many tables. Tables hold many rows of columnar data.

You don't have to have a lot of databases to store your data. In fact, it bloats the server and makes readability a little odd when there are a lot of databases. if all of your data is related in some capacity, they should be in a single database with as many table as you need.

Thank you

Posted: Thu Mar 22, 2007 12:00 pm
by keenlearner
Now I am not doubt in combining tables in one database when they are related. In fact, separating tables into databases make my codes so complicated

Posted: Sat Mar 24, 2007 7:51 pm
by califdon
I agree with Everah. The main reason for separating tables into different databases should be what general applications they implement. If you have completely unrelated applications (perhaps for different domains or different customers), they should each have a separate database. Otherwise, for a single application, you should include all the tables that are needed for that application in the same database.

Posted: Sat Mar 24, 2007 8:03 pm
by John Cartwright
I will usually give each project it's own database.

Posted: Sat Mar 24, 2007 11:40 pm
by anjanesh
Unless your date is more than huge, you dont require separate databases.

But take Amazon for example. They have some 22 categories like Apparel, Baby, Tools etc. Each of these categories is a SearchIndex value which is required when searching for amazon's products. The SearchIndex specifies which database or schema to search.