Modifying PostgreSQL Table for Handling Multiple Delivery Modes

Question

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;

Answer 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'))

• Maine: How would this look like if I tried to add data on the table for seller where it supports both sea and air freight?
• Vérace: @Maine - not sure if I understand what you mean here - I've added a bit to my answer.

Answer 2

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

Answer 3

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.