Difference between database name and schema name in SQLAlchemy?

Question

When connecting to a particular table in a database I might supply the following information

Database: protocol://user:pass@host:port/dbname
Table:    myschema.mytable

What is the difference here between dbname and myschema? Their use seems redundant to me.

Answer 1

A schema is a grouping of database objects (tables, views, etc) within a database. It is a way of logically partitioning objects within a database.

Assuming the user had the proper permissions, they could access tables in multiple schemas with one database connection. Joining tables (even from multiple schemas) is also trivial.

When objects are stored in multiple databases, you'd need a connection per database to access these objects, and joins are generally more difficult (your RDBMS or ORM may hide some of these difficulties). Each database would also have separate logins.

Answer 2

After a bit more reading, I believe schemas are just ways or partition up a DB. This can be useful for both managing permission and providing uniqueness to similarly named tables. So can collect a set of tables, view, triggers, into a schema and set permissions there. I can then set perms for that schema and hand that to users.

Additionally, I can also overload a table name but provide uniqueness through the schema. I believe this happens more often in transaction DBs where users maybe have a similarly named table but the schema is different per user.