I am learning Ruby on Rails, and I am going deeper into database models. I got stuck at the associations.

Usecase: I have a business in the port, where (small) ships come to bring in products, and then trucks to remove products. So a classical warehouse. I want to be able to have a view on my warehouse ("how many amounts of product prodref are in any of the warehouses"?).

I have three tables: Products, Warehouses and Orders. I have it working between Products and Orders and want to integrate Warehouse.

create_table "orders", force: :cascade do |t|
    t.datetime "order_date"
    t.integer "amount"
    t.boolean "item_is_buy"
    t.string "fk_prodref"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

create_table "products", force: :cascade do |t|
    t.string "brand"
    t.string "product_reference"
    t.string "description"
    t.string "category"
    t.string "content_type"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["product_reference"], name: "index_products_on_product_reference", unique: true
  end

create_table "warehouses", force: :cascade do |t|
    t.string "wh_name"
    t.string "fk_prodref"
    t.integer "amount"
    t.bigint "product_id", null: false
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["product_id"], name: "index_warehouses_on_product_id"
  end

add_foreign_key "orders", "products", column: "fk_prodref", primary_key: "product_reference"
add_foreign_key "warehouses", "products", column: "fk_prodref", primary_key: "product_reference"

a) The field product_reference in Product is the relevant search criteria, so whenever a ship brings in, or a truck brings out, items, it is referenced by this (string) field.

b) A product can be in many warehouses (1:n association).

c) For each product going in/out, a single order is required. There is no "5 products in one order". (This would be a 1:1 association). When a ship brings 5 products, 5 orders are created.

d) each order shall now update the warehouse amount column.

I want to update Warehouse's amount column for every order in the order form. There is only one warehouse. A warehouse has no orders, and orders don't belong to warehouses. So I have no relationship between Orders and Warehouse. Continuing using only the fk_prodref, I have the fk_prodref in my order (which was captured from the product table). So my order controller (or a helper controller) could be warehouse.amount = warehouse.amount order.amount_in, and simply fill the warehouse field fk_prodref with the fk_prodref string from the order.

I set up models Products and Orders with the foreign key on product_reference (without FK on id), and it works. I integrated Warehouse, and updates work.

Some Stack Overflow questions on 3-table associations deal with "has many : through" (a car has a motor, a motor has pistons, using car.pistons ...) but that is not the case here.

Is this a bad design, using only the foreign key, and no id related foreign keys? Am I violating Rails principles?

CodePudding user response：

If I understand correctly, what you need is a many to many relationship between Products and Warehouses, not a one to many relationship. A Warehouse has many Products and a Product has many Warehouses.

In rails, this can be set up via a has_many :through association. See The Rails Guide for more details on how this works. The physicians, patients and appointments example is similar to what you are try for.

It also looks like you are missing a warehouse_id field on your Orders table. Without it, it's unclear which warehouse an order is shipping to/from given that products can be stored in many different warehouses at the same time.

To address the above, your associations should look something like the following:

class Warehouse
    has_many :products, through: :inventories
    has_many :orders
end

#new table required here to join Warehouses to Products
class Inventory
    belongs_to :warehouse
    belongs_to :product 
end

class Product
   has_many :orders
   has_many :warehouses, through: :inventories
end

class Order
   belongs_to :product
   belongs_to :warehouse
end

To make the above work, you'll need to add warehouse_id and product_id fields to the new Inventories table described above and a warehouse_id field to your Orders table.

You should also remove the product_id and fk_prodref fields from your Warehouses table because you don't want a belongs_to :product association there.

In terms of other questions you asked:

1. It's not a violation of Rails principals to use productref instead of product_id as your foreign key. You can set up your associations using the foreign_key and primary_key options

   class Order
       belongs_to :product, foreign_key: :fk_productref, primary_key: 
       :productref 
   end
   

2. You can create products upfront without first creating a warehouse. The reason for the error in your current setup is that you are using a belongs_to which in recent versions of rails is required by default. If you want to make a belongs_to association optional you need to add optional: true.