SupabaseS
Supabase•4y ago
drewbie

Adding one to many records when creating one record

Trying to figure out the best way to model this and wanted to see if anyone had any advice.

I am aiming to create an 
orders
 table that has a buyer_id and a seller_id, both referencing/associated with the accounts table. That part so far is fine
CREATE TABLE orders
(
  id uuid default uuid_generate_v4() primary key,
  created_at timestamp default now() not null,
  updated_at timestamp default now() not null,
  state "order_state" default 'IN_PROGRESS' not null
);

ALTER TABLE "orders" ADD COLUMN "buyer_id" UUID NOT NULL;
ALTER TABLE "orders" ADD CONSTRAINT "orders_buyer_id_fkey" FOREIGN KEY ("buyer_id") REFERENCES "accounts"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE "orders" ADD COLUMN "seller_id" UUID NOT NULL;
ALTER TABLE "orders" ADD CONSTRAINT "orders_seller_id_fkey" FOREIGN KEY ("seller_id") REFERENCES "accounts"("id") ON DELETE RESTRICT ON UPDATE CASCADE;


The next part is where I am finding it tricky. I want to be able to add many products to the order, via an order_products table which would look like this --
CREATE TABLE order_products
(
  id uuid default uuid_generate_v4() primary key,
  created_at timestamp default now() not null,
  updated_at timestamp default now() not null
);

ALTER TABLE "order_products" ADD COLUMN "order_id" UUID NOT NULL;
ALTER TABLE "order_products" ADD CONSTRAINT "order_products_order_id_fkey" FOREIGN KEY ("order_id") REFERENCES "orders"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE "order_products" ADD COLUMN "product_id" UUID NOT NULL;
ALTER TABLE "order_products" ADD CONSTRAINT "order_products_product_id_fkey" FOREIGN KEY ("product_id") REFERENCES "products"("id") ON DELETE RESTRICT ON UPDATE CASCADE;


However, I want to make the order_products / products for the order record required, and eventually I will create constraints or policies around making sure that the product belongs to the seller/account within the order. How would I go about setting up the requirement of the order_products, or products for the order when creating the order record, specifically with the js client?

I imagine it looking something like this ->
client.from("orders").insert({buyer_id: buyer.id, seller_id: seller.id, order_products([{product_id: productOne.id}, {product_id: productTwo.id}]), but this obv doesn't work, but something Im after?

Any help on this would be appreciated, and I'm also all ears for any suggestions on a better schema what I'm after.

Thanks!
Was this page helpful?