When do we use different databases ?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
keenlearner
Forum Commoner
Posts: 50
Joined: Sun Dec 03, 2006 7:19 am

When do we use different databases ?

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
keenlearner
Forum Commoner
Posts: 50
Joined: Sun Dec 03, 2006 7:19 am

Thank you

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I will usually give each project it's own database.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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.
Post Reply