1

I'm working on a project where I need to modify the PostgreSQL table structure to handle multiple delivery modes for an ordering system.

I have a table named seller with columns like id, user_id, store_name, etc.

Current State: I currently have a delivery_mode column, but I need to allow for scenarios where a station can support both air freight, sea fright or only one of those.

For example, 1st scenario: seller1 sells clothes, the only delivery mode available for this store is air freight. So, when the customer orders, the only delivery mode available is air freight.

2nd scenario: seller2 sells phones, the delivery mode available for this store are air freight and sea freight. So, when the customer orders, the customer may choose using a select with values of either air freight and sea freight

Objective: I want to modify the table structure to accommodate multiple delivery modes (air freight and sea freight) while adhering to the relational model.

Options Considered: I've considered using an array or JSONB column for delivery_mode.

Question: What is the best way to modify the table to handle multiple delivery modes in a relational database?

SQL Schema

create table
  public.seller (
    id uuid not null default uuid_generate_v4 (),
    store_name text not null,
    address text not null,
    user_id uuid not null,
    delivery_mode text null,
    //rest of the fields
    constraint seller pkey primary key (id),
  ) tablespace pg_default;
0

3 Answers 3

1

Use this:

delivery_method TEXT NOT NULL CHECK (delivery_method IN ('Sea Freight', 'Air Freight')),

Great for a small number of possibilties. Otherwise use a lookup table. JSON is overkill for this!

In response to the comment by the OP "where it supports both sea and air freight?" - you could do this (not sure if this is what you want - could you expand - possibly with an example on dbfiddle?):

delivery_method TEXT NOT NULL 
  CHECK (delivery_method IN ('Sea Freight', 'Air Freight', 'Sea or Air Freight'))
2
  • How would this look like if I tried to add data on the table for seller where it supports both sea and air freight?
    – Maine
    Commented Dec 2, 2023 at 13:45
  • @Maine - not sure if I understand what you mean here - I've added a bit to my answer.
    – Vérace
    Commented Dec 3, 2023 at 20:25
0

You can use two boolean columns, one for each delivery mode. This method is efficient for storage and querying.

0

Here is a possibility using ARRAY data type (using PostgreSQL 14). The possible values for delivery_mode are ["air"] ["sea"], or ["air", "sea"].

You can create a DOMAIN to validate the delivery_mode values.

CREATE DOMAIN modes_arr AS VARCHAR[]
  CHECK(VALUE = '{"air"}' OR VALUE = '{"sea"}' OR VALUE = '{"air", "sea"}');

Table column using the modes_arr domain:

CREATE TABLE t (
  id serial NOT NULL PRIMARY KEY,
  delivery_mode modes_arr
);

Add some data:

INSERT INTO t VALUES (DEFAULT, '{"air"}'), (DEFAULT, '{"air", "sea"}');

Query data:

SELECT * FROM t;

  1 | {air}
  2 | {air,sea}

The domain modes_arr will make sure that inserting values like this will fail: '{"air", "road"}', '{}', etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.