Is it because of size? By the way, what is the limit of the size?
+1
A:
suppose you wanted to publish or reuse some external database, and keep it separate from your primary database. This would be a good reason to use 2 databases... You can drop and reimport the external database at any time without affecting your database, and vice versa...
Zak
2009-11-10 02:59:22
+1
A:
You can use two databases the same reason most banks have two ATMs, for reliability. You can swap one in if the other fails, but to do it quickly requires setup, such as a cname and controlling your own DNS server.
You can also do writes on one database, if the writes have complex triggers on them, and use some synching between databases to keep the second one updates, which is used for selects.
You can use two databases for load sharing, for example, use round-robin to split up the load so one isn't overloaded.
James Black
2009-11-10 03:03:49
+7
A:
There are many reasons to use two databases, some that come to mind:
Size (the limit of which is controlled by the operating system, filesystem, and database server)
Separation of types of data. Think of a database like a book -- you wouldn't write a book that spans multiple subjects, and you shouldn't (necessarily) have a database with multiple subjects. Just so all of the data is somehow related, you could keep it together (i.e. all the tables have something to do with one website or application).
Import / Export - it might be easier to import data into your application if you can drop and restore a whole database, rather than import individual rows into a database table.
pixel
2009-11-10 03:04:01
1. if you've hit those limit's you should buy bigger hardware (more space), and/or start sharding. sharding is not another database.2. I don't much like your explanation esp since there are lots of books that span multiple subjects (e.g. encyclopedia's, dictionaries, etc,) all of which are perfect examples of databases. multiple site's might force you to use different database. multiple apps is certainly.3. easier... yes. however if you're doing this then your database is probably not relational (possible).I seriously think this answer is way off target.
xenoterracide
2009-11-10 05:09:12
also #3 is not a good reason to have a separate database.
xenoterracide
2009-11-10 05:11:59
@xeno, the size limitation could be due to software, like sql express which limits you to 4 gb. Separation is done to show that the data contained in the two databases are not coupled to each other. Like two plugins that uses different data backends, and either plugin could work on its own. Encyclopedias contains unrelated data. And there's also several wikis available, all with different target content. Dropping and recreating a database during development is easier than starting to delete everything everywhere, especially when you have [instead of] triggers, foreign keys, etc.
Simon Svensson
2009-11-10 05:39:32
I think if you've reached a size limitation then you need to consider getting a new product. In fact you should have already migrated. that falls under use the right tool for the job.why are you running several wiki's in 1 organization instead of running 1 with categories and maybe differing url's and some web server magic. that's bad design? sure different organizations are going to have separate databases. during development I have scripts that reload my database. and when updating a production db you know I have backups.
xenoterracide
2009-11-10 08:03:55
1. Bigger hardware is not always an option, and if splitting data into two databases gets around that problem, then it is a good solution. The real world is not always ideal.2. It was simply an analogy, maybe not a perfect one, however the basic premise is still true. Data is separated in many ways in a relational database. By row and column, by tables, by databases and by servers. It is an exercise for the developer to determine the best separation.
pixel
2009-11-10 17:06:57
3. As an example, a project I work on has two databases -- one that manages user data, and one that manages other object data. This second database is supplied by an outside company at regular intervals. We have two separate databases for this object data, one live, and one staging. When we receive new data, the current staging database is dumped, and the new one loaded. We then test this data, and then point the application to the staging data (thus the staging becomes live, and the live becomes staging). This makes the process significantly faster, cleaner, and less prone to error.
pixel
2009-11-10 17:09:55
+4
A:
Separate applications, or services. I can't see any reason to use separate databases for a single app/service.
(note: replication, even multimaster, isn't a separate database. Neither is Sharding.)
I believe some on here are confusing Database with a Database Instance.
Example: A phone book is a prime example of a Database.
Replication: having 2 copies of the same phone book does not mean you have 2 databases. It means you have 2 copies of 1 database, and that you can hand 1 to someone else so you can both look up different things at the same time thereby accomplishing more work at once.
Sharding: You could tear those phonebooks apart at the end of the white pages and the beginning of the yellow and hand them to 2 more people. You could further tear them at each letter and when you need susan summers ask the person with that section of the book to look for her.
xenoterracide
2009-11-10 03:04:53
Or two different parts in one application, where one would work independent of another. (And where only one of the parts is reused in other projects.)
Simon Svensson
2009-11-10 05:41:53
the only thing I can think of that you could be referring to is something like authentication. In which case I would consider that another service/application. In fact all cases I think of are another service/application that has been integrated.
xenoterracide
2009-11-10 08:06:58
+2
A:
I sometimes have separate database because they handle different concerns. I.E. a Reporting database or an Authentication Database.
Aaron
2009-11-10 03:05:45
+1
A:
As Ben mentioned, Replication is one reason. Another is load balancing.
For example, Hotmail uses many database servers and customer data is broken up across the databases.
To have all of their customers' data on one server would not only require massive storage requirements, but the response times would be horrible.
In other cases, the data may be separated by function. You may well have two sets of data which are either not connected, or at least very loosely so, and in such cases, it may make sense to separate that data from the rest.
Jonathan Fingland
2009-11-10 03:06:18
A:
Also consider IO needs. Writing to one, reading from the other. One with immediate transactional needs, others where "transactions" can be queued, one instance at high priority, the other at "idle" priority, &c. It is very obvious however with the correct hardware and tablespace/filesystem layouts most of these situations can be achieved in a singular DB.
Xepoch
2009-11-10 05:26:26