All,
We are evaluating MySql as a lower cost alternative to SQL server for our smaller clients in a shared hosting environment. I am still foggy on how we would migrate some of the particulars such as table/scalar value functions, sql jobs, etc, but I will post specifics in another thread. I do, however, have a fundamental question about databases vs. schemas in MySql.
Currently, our SQL server instance has multiple databases with multiple schemas in each. For example:
Database (schemas...)
====================
Client1Database (dbo, web)
Client2Database (dbo, web, schema3)
Client3Database (dbo, schema2)
In my sample MySql instance, it appears as though the top level objects are schemas, not databases. But the "schemas" have database icons next to them and, in our case, a "schema" is listed as part of the connection string in our .NET apps.
So I guess I just have a few clarification questions as to how to perceive the schemas/databases in MySql...
- Can the top level objects in a MySql instance be considered schemas if a database consists of multiple schemas, or a single schema "database".
- In the scenario above, would I create a separate instance for each client, flatten the individual client databases to a single schema and keep the three clients in the same instance, or some other method that is escaping me? Is there a benefit, security, clarity, or otherwise to one approach over another?
Thanks in advance for your replies.