Fast & Reliable Cloud Backups

For MySQL, MongoDB, Linux, Unix

Get Started

Nov 14, 2014

Improve Database Performance by Using a Single Association Table for Many-to-Many Tables

Categories howto

Posted by Gen

feature photo

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.

An association table uses the "secondary" argument to "relationship()" and the declarative base class's "MetaData" object to allow "ForeignKey" directives to find the remote tables they link to. Source: SQLAlchemy

In bidirectional relationships, the "backref" keyword uses the same "secondary" argument for the reverse relationship automatically.

The same "secondary" argument is used by the "backref" keyword for the reverse relationship in bidirectional relationships. Source: SQLAlchemy

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.

The "association_table" need not be defined until initialization of all modules when callables are used to return the ultimate argument. Source: SQLAlchemy

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 traditional "string name of the table" can be used to match the table name in "Base.metadata.tables" when the declarative extension is used. Source: SQLAlchemy

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.

Fast & Reliable Cloud Backups

For MySQL, MongoDB, Linux, Unix

Get Started

Categories howto

Leave a Comment