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

orders

table that has a

buyer_id

buyer_id

and a

seller_id

seller_id

, both referencing/associated with the

accounts

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;

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

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;

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}])

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!

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

orders

table that has a

buyer_id

buyer_id

and a

seller_id

seller_id

, both referencing/associated with the

accounts

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;

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

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;

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;

client.from("orders").insert({buyer_id: buyer.id, seller_id: seller.id, order_products([{product_id: productOne.id}, {product_id: productTwo.id}])

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!

Adding one to many records when creating one record

Adding one to many records when creating one record

Similar Threads

Similar Threads

Similar Threads