Monday - November 14, 2011language: Englishposted by: Blackmore
Database schema objects are often neglected when designing systems and applications. One of the main reasons may be the developers' poor understanding of the advantages that come along when using schemas.
A database contains one or more named schemas, which in turn contain tables.
Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database he is connected to, if he has privileges to do so.
Imagine that you are building a system that consists of two logical parts: a book archive and a back office authentication.
If both of them require table named 'users', there is a potential problem with name-collision.
Instead of using "smart" / namespaced table names "ba_users, bo_users", a more elegant solution is to isolate the two logical parts of the system - each in its own schema within the same database.
Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.
The obvious advantages are:
- avoiding name collisions between the two logical parts
- better management of the logical parts (one can perform dump / restore on a specific schema)
- using one single connection at the time (compared to using multiple databases)
Working with schemas in SQLAlchemy is easy. In the following example, we create two tables within the same schema.
For more information about creating and manipulating schemas, refer to the PostgreSQL documentation.
If your (0)RDBMS does not support schema-objects, you may consider using a proper one.
Happy hacking!
A database contains one or more named schemas, which in turn contain tables.
Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database he is connected to, if he has privileges to do so.
Imagine that you are building a system that consists of two logical parts: a book archive and a back office authentication.
If both of them require table named 'users', there is a potential problem with name-collision.
Instead of using "smart" / namespaced table names "ba_users, bo_users", a more elegant solution is to isolate the two logical parts of the system - each in its own schema within the same database.
Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.
The obvious advantages are:
- avoiding name collisions between the two logical parts
- better management of the logical parts (one can perform dump / restore on a specific schema)
- using one single connection at the time (compared to using multiple databases)
-- in PostgreSQL --
CREATE SCHEMA my_books;
CREATE TABLE my_books.books (
...
);
-- CREATE TABLE books ... will create the table books in the schema "public"
Working with schemas in SQLAlchemy is easy. In the following example, we create two tables within the same schema.
For more information about creating and manipulating schemas, refer to the PostgreSQL documentation.
# SQLAlchemy
## using the "classical" method with mapper()
metadata = MetaData()
authors_table = Table('authors', metadata,
Column('author_id', Integer,
Sequence("custom_seq_author_id",
schema='my_books'),
primary_key=True),
Column('name', String(128)),
schema='my_books')
books_table = Table('books', metadata,
Column('book_id', Integer,
Sequence("custom_seq_book_id", schema='my_books'),
primary_key=True),
Column('title', String(256)),
Column('author_id',
ForeignKey("my_books.author_id")), #N.B.
schema='my_books')
# ...
# mapper(Book, books_table)
# mapper(Author, authors_table)...
## using the declarative method in SQLAlchemy
Base = declarative_base()
class Book(Base):
__tablename__ = "books"
__table_args__ = {'schema':'my_books'}
book_id = Column(Integer,
Sequence("custom_seq_book_id", schema='my_books'),
primary_key=True),
title = Column(String(256))
author_id = Column(ForeignKey("my_books.author_id")) #N.B.
If your (0)RDBMS does not support schema-objects, you may consider using a proper one.
Happy hacking!
