Postgres and Ruby - Is there a better way to structure my database?

Question

I am planning my db right now. And in the process of putting my ERD together I quickly noticed that all tables are related to BOTH my shops table and my amazon_credentials table. Is there a better/more efficient design or relationship that I can put in place to make my db more efficient and easy to use?

Here is a link to my current ERD in Whimsical

One more caveat, I don't want to have to go thru my amazon_credentials table for all queries. For example.. I would want to be able to do both this Shop.find(1).packing_slips and get all the packing_slips for a shop as well as be able to do this Shop.find(1).amazon_credentials.packing_slips to get the packing_slips for a specific marketplace.

In thinking about this, I'm almost using my amazon_credentials table as a stand in for marketplace as the credentials are inherently scoped to a marketplace

Is this a good design or is there a better way?

Answer 1

The duplicated relationships to Shops and AmazonCredentials scattered around the database can be replaced with indirect relationships. Fortunately Rails provides methods to make indirect relationships seem direct: has_many :through.

And you don't need to get it perfect the first time, nor predict every possible use. Migrations help you safely change your design to match changing requirements.

Let's build the basic relationships. I've made some assumptions, like shipping methods are attached to a marketplace.

• A Product has information about itself.

• A Shop has Products, ways to market them (a Marketplace), credentials for those Marketplaces, and Orders.

• An Order has who made the order, from which Shop, through what Marketplace, what Products were ordered and how many at what price, how it's being shipped, and how to track it.

• A Marketplace has ShippingMethods.

Right away we run into a problem: credentials. A shop needs credentials for each Marketplace. This is solved with an intermediate model: MarketplaceAccount which marries a Shop and Marketplace with the Shop's credentials. Credentials can be made into rows of key/value pairs.

An Order needs to remember the details about which Products were ordered, how many, and for how much. This needs another intermediate model: ProductOrder.

The basic models would look something like this:

class Product < ApplicationRecord
  belongs_to :shop

  has_many :product_orders
  has_many :orders, through: :product_orders
  has_many :customers, through: :orders

  # name
  # description
  # sku
end

class Shop < ApplicationRecord
  has_many :products

  has_many :marketplace_accounts
  has_many :marketplaces, through: :marketplace_accounts
  
  has_many :orders
  has_many :customers, through: :orders

  # name
  # country
end

class Order < ApplicationRecord
  belongs_to :customer

  # Remember which MarketplaceAccount this was done with
  belongs_to :marketplace_account
  has_one :shop, through: :marketplace_account
  has_one :marketplace, through: :marketplace_account

  # How is it being shipped?
  belongs_to :shipping_method
  
  has_many :product_orders
  has_many :products, through: :product_orders

  # tracking number
  # status
end

class ProductOrder < ApplicationRecord
  belongs_to :order
  belongs_to :product

  # price
  # quantity
end

# Holds the Shop's credentials for a Marketplace.
class MarketplaceAccount < ApplicationRecord
  belongs_to :shop
  belongs_to :marketplace
  has_many :credentials
end

# Each row is simply a key and value.
# Consider encrypting this table.
class Credentials < ApplicationRecord
  belongs_to :marketplace_account

  # key
  # value
end

class Marketplace < ApplicationRecord
  has_many :shipping_methods

  has_many :marketplace_accounts
  has_many :shops, through: :marketplace_accounts

  # name
end

class ShippingMethod < ApplicationRecord
  belongs_to :marketplace
  has_many :shops, through: :marketplace

  # speed
  # rate
  # name
end

class Customer < ApplicationRecord
  has_many :orders
  has_many :product_orders, through: :orders
  has_many :products, through: :product_orders
end

By using has_many :through and has_one :through we can avoid duplication while making indirect relationships seem direct. A Shop has_many MarketplaceAccounts, a MarketplaceAccount belongs to a Marketplace; with has_many :marketplaces, through: :marketplace_accounts a Shop can directly access its Marketplaces. shop.marketplaces.

The key/value Credentials table avoids a proliferation of company-specific credentials tables.

---

More relationships can be fleshed out. A Product can be sold by many Shops, so we can have a ShopProduct which relates a generic Product with how a Shop sells it. This avoids duplicating product information and allows you to see how a single product is sold by many shops.

class Product < ApplicationRecord
  has_many :shop_products

  # sku
  # generic name
  # generic description
  # manufacturer's recommended price
end

class ShopProduct < ApplicationRecord
  belongs_to :shop
  belongs_to :product

  delegate :sku, to: :product

  # shop specific name
  # shop specific description
  # shop price
end

class Shop
  has_many :shop_products
  has_many :products, through: :shop_products
end

And most things would relate to a ShopProduct instead of a Product.

---

You have a Shop working with a single currency. The design can be expanded to allow a Shop to work with many Currencies.

class Currency < ApplicationRecord
  # symbol
  # conversion rate
end

class ShopCurrencies < ApplicationRecord
  belongs_to :shop
  belongs_to :currency
end

class Shop < ApplicationRecord
  has_many :shop_currencies
  has_many :currencies, through: :shop_currencies
end

has_many :currencies, through: :shop_currencies lets shop.currencies work.

---

That's the basics. Armed with that you can hammer that basic layout to your needs.