Clever Geek Handbook

Difference between database name and schema name in SQLAlchemy?

When connecting to a specific table in the database, I can provide the following information

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

What is the difference between dbname

and myschema

? Using them seems overkill to me.

+3


source to share


2 answers


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

a database. It is a way to logically split objects in a database.

Assuming the user has the appropriate permissions, they can access tables in multiple schemas with a single database connection. Concatenating tables (even from multiple schemas) is also trivial.



When objects are stored across multiple databases, accessing those objects requires a connection to each database, and joins are usually more complex (your RDBMS or ORM may hide some of these difficulties). Each database will also have separate logins.

+4


source


After a bit more reading, I believe that schemas are just ways or ways to partition the DB. This can be useful for both managing permission and ensuring that tables with the same name are unique. Thus, you can collect a set of tables, views, triggers into a schema and set permissions there. Then I can install perms for this schema and push it to users.



Alternatively, I can also overload the table name, but enforce uniqueness across the schema. I find this happens more often in transactional databases where users may have a similar table, but the schema is different for each user.

+1


source






More articles:


All Articles