TL;DR: Attention to detail is the hallmark of efficient database design. To realize the full potential of the tables that comprise your database, devise a single association table that joins many-to-many tables without creating empty fields or other unnecessary, performance-sapping elements.
Database design is simple in theory and complex in practice. In essence, databases are collections of related tables: you build the tables, you specify the relationships between the tables, and you query the tables to glean business intelligence. As any database administrator knows, things become complicated almost immediately.
To illustrate this point, imagine a SQL database comprised of only a product table, a categories table, and a table for product-category associations. As explained in a post on WebmasterWorld.com, a query with the association table would look like this:
SELECT product_id, product_associate, category_id, associate_id FROM products, categories, associate WHERE product_id = associate_id AND category_id = associate_id
You could simply do away with the association table and use a query like this:
SELECT product_id, product_category, category_id FROM products, categories WHERE product_category = category_id
But that would be a bad idea. The reason for adding the association table becomes obvious as you add products that fall into more than one category. If you add separate fields to the product table for each category, you'll have empty fields for the products that fall into only one or a handful of categories. Also, any changes you make to the category table will have to be reflected in the product table's fields. As the database ages, this can entail quite a bit of work.
Alternatively, you could make the category_id field a list of category IDs, but then you'll be modifying the list constantly to add new IDs, and you'll be comparing a character list against an integer field when you join tables. By using an association table, you can add categories without having to modify the database.
Make sure your association tables are as efficient as possible
The association table added for many-to-many relationships between classes uses the "secondary" argument to "relationship()". To allow "ForeignKey" directives to find the remote tables they're linking to, the "Table" likely uses the "Metadata" object associated with the declarative base class.
In bidirectional relationships, the "backref" keyword uses the same "secondary" argument for the reverse relationship automatically.
Because the "secondary" argument of a "relationship()" accepts a callable that returns the ultimate argument that isn't evaluated until the mappers are used, you can define the "association_table" later. It has to be available to the callable once all modules are initialized.
When the declarative extension is in use, the traditional "string name of the table" is also accepted to match the name of the table as it appears in "Base.metadata.tables".
The same kind of care you take in constructing efficient database tables carries over to your selection of a cloud storage service. BitCan lets you set backups in just seconds using a simple point-and-click interface that requires no client-side installs or plug-ins. Your backups of heterogeneous MySQL and MongoDB databases, and Unix/Linux systems and files are encrypted at the communication and storage layers.
BitCan alerts you on the status of your backups and lets you recover and restore data quickly and simply, whether they're stored behind a firewall or on the public cloud. Visit the BitCan to create a free 30-day trial account.