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;

Paul White · Accepted Answer · 2023-12-04 08:27:36Z

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

edited Dec 4, 2023 at 8:27

Paul White♦

96.1k30 gold badges442 silver badges691 bronze badges

answered Dec 2, 2023 at 12:49

Vérace

31k9 gold badges73 silver badges86 bronze badges

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
– Maine

2023-12-02 13:45:51 +00:00
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
– Vérace

2023-12-03 20:25:51 +00:00
Commented Dec 3, 2023 at 20:25

Add a comment |

Mykola Danyliuk · Accepted Answer · 2023-12-03 11:17:07Z

0

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

answered Dec 3, 2023 at 11:17

Mykola Danyliuk

1

Add a comment |

prasad_ · Accepted Answer · 2023-12-04 05:51:42Z

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.

Stack Exchange Network

Modifying PostgreSQL Table for Handling Multiple Delivery Modes

3 Answers 3

Your Answer

Hot Network Questions

Modifying PostgreSQL Table for Handling Multiple Delivery Modes

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions