Polymorphic vs Shared Table: Is speed a valid concern?
Tags: webdev, rails, database
Published: 2024-06-29 14:39:35

What do you do when you find a model that can belong to multiple models? 

In my case, I'm building the inventory tracking functionality for an MRP. An Inventory Item can be either related to a Product, or a Material - in summary, I need to find a way to relate the two tables on the left, to the table on the right:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3q69i9yycpxkngcy6ptg.png)

What usually comes to mind is using a polymorphic relationship. Let's call this poly_inventory_item: 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pvosluey1onn3nmvbq3k.png)

This way, the `item_id` field is a foreign key and the `item_type` will let us know if it is a material or a product. This has some strong points: 

- Clean solution with only two fields
- Flexibility to add more relations in the future

However, since there are only two options (either a material or a product), a table with two foreign keys is also a viable option. Let's call this shared_inventory_item:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qtene4g62l3pgllilcla.png)

This case is a bit unusual since it has nullable foreign keys, but it comes with some advantages:

- Clearer relations, as you can see the foreign keys directly
- Faster speeds, due to the indexes on the foreign keys

This second assumption, is what made me question my decision. How much faster can it be? 

## Testing the speed of both relations

I'm using Rails and SQLite for this test, and tested the speed in some common operations: creating records, getting the item, and querying the table.

### Database setup

Standard indexes expected for the shared table and the polymorphic table:

```ruby
# db/schema.rb
create_table "poly_inventory_items", force: :cascade do |t|
  t.string "item_type", null: false
  t.integer "item_id", null: false
  t.index ["item_type", "item_id"], name: "index_poly_inventory_items_on_item"
end
create_table "shared_inventory_items", force: :cascade do |t|
  t.integer "product_id"
  t.integer "material_id"
  t.index ["material_id"], name: "index_shared_inventory_items_on_material_id"
  t.index ["product_id"], name: "index_shared_inventory_items_on_product_id"
end
add_foreign_key "shared_inventory_items", "materials"
add_foreign_key "shared_inventory_items", "products"
```

### Models setup

Very simple definition and validations for the polymorphic table:

```ruby
class PolyInventoryItem < ApplicationRecord
  belongs_to :item, polymorphic: true
  validates :item_type, inclusion: {in: %w[Product Material]}
end
```

The shared table is a bit more complex, as it needs to validate the presence of one of the foreign keys, and the absence of the other:
```ruby
class SharedInventoryItem < ApplicationRecord
  belongs_to :product, optional: true
  belongs_to :material, optional: true

  validates :product_id, presence: true, unless: :material_id?
  validates :material_id, presence: true, unless: :product_id?

  validates :product_id, absence: true, if: :material_id?
  validates :material_id, absence: true, if: :product_id?
end
```

On the other side, the Material and Product can be very straightforward:
```ruby
class Material < ApplicationRecord
  has_one :shared_inventory_item
  has_one :poly_inventory_item, as: :item
end 
```
```ruby
class Product < ApplicationRecord
  has_one :shared_inventory_item
  has_one :poly_inventory_item, as: :item
end
```


### Benchmarking 

Since indexes matter more on a large database, I did all tests in a situation with only a thousand records, and again with 100K records. The tests were done using the `benchmark-ips` gem.
I tested the most important operations for my use case: creating records, reading from the association, and querying the table.

```ruby 
# --- Creating records
Benchmark.ips do |x|
  x.report("PolyInventoryItem") do
    material = Material.create!(name: "Material")
    product = Product.create!(name: "Product", sku: "SKU")
    material.create_poly_inventory_item!
    product.create_poly_inventory_item!
  end
  x.report("SharedInventoryItem") do
    material = Material.create!(name: "Material")
    product = Product.create!(name: "Product", sku: "SKU")
    material.create_shared_inventory_item!
    product.create_shared_inventory_item!
  end
  x.compare!
end
# --- Reading from association
Benchmark.ips do |x|
  x.report("PolyInventoryItem") do
    Product.first.poly_inventory_item
    Material.first.poly_inventory_item
  end
  x.report("SharedInventoryItem") do
    Product.first.shared_inventory_item
    Material.first.shared_inventory_item
  end

  x.compare!
end
# --- Querying
product = Product.first
material = Material.first
Benchmark.ips do |x|
  x.report("PolyInventoryItem") do
    PolyInventoryItem.find_by(item: product)
    PolyInventoryItem.find_by(item: material)
  end
  x.report("SharedInventoryItem") do
    SharedInventoryItem.find_by(product: product)
    SharedInventoryItem.find_by(material: material)
  end

  x.compare!
end
```

### Results

Creating records
```
 --- 1K records
 SharedInventoryItem:    409.4 i/s
 PolyInventoryItem:      394.5 i/s - same-ish: difference falls within error
 --- 100K records
 SharedInventoryItem:    378.4 i/s
 PolyInventoryItem:      377.4 i/s - same-ish: difference falls within error
```

Reading from association
```
 --- 1K records
  SharedInventoryItem:     1982.0 i/s
    PolyInventoryItem:     1863.5 i/s - 1.06x  slower
 --- 100K records
  SharedInventoryItem:     1915.8 i/s
    PolyInventoryItem:     1761.8 i/s - 1.09x  slower
```
Querying
```
 --- 1K records
  SharedInventoryItem:     7471.5 i/s
    PolyInventoryItem:     4476.7 i/s - 1.67x  slower
 --- 100K records
  SharedInventoryItem:     6686.9 i/s
    PolyInventoryItem:     3862.5 i/s - 1.73x  slower
```

The query with find_by is the one that makes most use of the indexes, and it is the one that has the most significant difference. However, this would only by useful if you are querying the table instead of the association.

## Conclusion

I was surprised to see that the speed difference was not as significant as I thought. For the most part, the polymorphic relation is as fast as the shared table, it is also cleaner and easier to maintain. It all comes down to the trade-offs you are willing to make.

I will stick with the polymorphic relation. Hope this helps you make a decision in the future! 🙌