When would you use a separate database?
Good reasons to create separate databases would be to support different availability requirements or simplify administration. For example if your databases require very different backup schedules or different recovery models. Another reason would be if you may want to run them on different instances.
Why do we need multiple databases?
In practice many systems use multiple databases. They are introduced for many reasons, for example, to scale up the system, to separate data belonging to different applications, to simplify debugging and maintenance, or to add an application that requires a different data manager product.
What should I do if I have two schemas?
If you use two schemas then definitely create separate DATA and TEMP tablespaces. (You can not have separate UNDO). If you use two databases then you can not share RAM between them – for buffer cache. You can partially bypass this limitation by using OS level buffer cache. Simply disable direct file access.
How are schemas and databases the same in MySQL?
A schema can be owned by any user, and its ownership is transferable. In MySQL, databases and schemas are exactly the same thing, you can even interchange the word in the commands, i.e. CREATE DATABASE has the synonym CREATE SCHEMA. MySQL supports multiple databases (schemas) and MS SQL supports multiple databases and multiple schemas.
Are there objects like tables in a schema?
Generally, there would be no objects like tables in that schema. This also helps to enforce a design pattern where the interaction between the application and the database is clear and defined.
What should be included in a schema for a website?
For simplicity, let’s call the schema Website. In the Website schema, I’d place just the objects that the website is permitted to use. Specifically, that will be a set of views, functions, and/or stored procedures. Generally, there would be no objects like tables in that schema.