Y
Y
yohate2021-02-28 12:02:30
Database design
yohate, 2021-02-28 12:02:30

How to properly model order table/tables?

Now there is an order_request table . This is a request for an order, 5 out of 15 fields cannot be changed.

First question: there are specific fields for each status. For example, if the status has changed to pending payment, then a certain timestamp is written to the need_to_payed column , and before this status it has the value NULL , but I'm not sure if this is correct. Maybe it's worth making a separate table, a copy of the order, for each status, which introduces / removes new fields with its logic, or for each status add separate tables with specific fields that will be linked one to one to the order_request table ?

The second question: it is necessary to fix each change of the table. Is it ok to use a table like:

create table order_update_log
(
  id serial
    constraint order_update_log_pk
      primary key,
       changed_by int not null
    constraint table_name_app_users_id_fk
      references app_users
        on update restrict on delete restrict,
  "from" json not null,
  "to" json not null,

  date timestamp default now() not null
);

In from and to insert JSON_BUILD_OBJECT order tables (DBMS - PostgreSQL 12).

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question