Multiple autoincrement ids based on table column

Question

I need help in database design.

I have following tables.

Pseudo code:

Table order_status {
  id int[pk, increment]
  name varchar
}

Table order_status_update {
  id int[pk, increment]
  order_id int[ref: > order.id]
  order_status_id int[ref: > order_status.id]
  updated_at datetime
}

Table order_category {
  id int[pk, increment]
  name varchar
}

Table file {
  id int[pk, increment]
  order_id int[ref: > order.id]
  key varchar
  name varchar
  path varchar 
}

Table order {
  id int [pk] // primary key
  order_status_id int [ref: > order_status.id]
  order_category_id int [ref: > order_category.id]
  notes varchar
  attributes json  // no of attributes is not fixed, hence needed a json column
}

Everything was okay, but now I need an auto-increment id for each type of order_category_id column.

For example, if I have 2 categories electronics and toys , then I would need electronics-1, toy-1, toy-2, electronics-2, electronics-3, toy-3, toy-4, toy-5 values associated with rows of order table. But it's not possible as auto-increment increments based on each new row, not column type.

In other words, for table order instead of

  id  order_category_id
---------------------
  1       1       
  2       1       
  3       1     
  4       2       
  5       1      
  6       2
  7       1

I need following,

 id  order_category_id pretty_ids
----------------------------
  1       1       toy-1
  2       1       toy-2
  3       1       toy-3
  4       2       electronics-1
  5       1       toy-4
  6       2       electronics-2
  7       1       toy-5

What I tried:

I created separate table for each order category (not an ideal solution but currently I have 6 order categories, so it works for now )

Now, I have table for electronics_order and toys_order. Columns are repetitive, but it works. But now I have another problem, my every relationship with other tables got ruined. Since, both electronics_order and toys_orders can have same id, I cannot use id column to reference order_status_update, order_status, file tables. I can create another column order_category in each of these tables, but will it be the right way? I am not experienced in database design, so I would like to know how others do it.

I also have a side question.

Do I need tables for order_category and order_status just to store names? Because these values will not change much and I can store them in code and save in columns of order table.

I know separate tables are good for flexibility, but I had to query database 2 times to fetch order_status and order_category by name before inserting new row to order table. And later it will be multiple join for querying order table.

--

If it helps, I am using flask-sqlalchemy in backend and postgresql as database server.

Answer 1

In order to track the increment id which is based on the order_category, we can keep track of this value on another table. Let us call this table: order_category_sequence. To show my solution, I just created simplified version of order table with order_category.

CREATE TABLE order_category (
  id SERIAL PRIMARY KEY,
  name  VARCHAR(100) NULL
); 


CREATE TABLE order_category_sequence (
  id SERIAL PRIMARY KEY,
  order_category_id int NOT NULL,
  current_key  int not null
);

Alter Table order_category_sequence Add Constraint "fk_order_category_id" FOREIGN KEY (order_category_id) REFERENCES order_category (id);
Alter Table order_category_sequence Add Constraint "uc_order_category_id" UNIQUE (order_category_id);


CREATE TABLE "order" (
  id SERIAL PRIMARY KEY,
  order_category_id int NOT NULL,
  pretty_id  VARCHAR(100)  null
);

Alter Table "order" Add Constraint "fk_order_category_id" FOREIGN KEY (order_category_id) REFERENCES order_category (id);

The order_category_id column in order_category_sequence table refers the order_category. The current_key column holds the last value in order.

When a new order row is added, we can use a trigger to read the last value from order_category_sequence and update pretty_id. The following trigger definition can be used to achieve this.

--function called everytime a new order is added
CREATE OR REPLACE FUNCTION on_order_created()
  RETURNS trigger AS
$BODY$

DECLARE 
current_pretty_id varchar(100);

BEGIN

-- increment last value of the corresponding order_category_id in the sequence table
Update order_category_sequence
set current_key = (current_key + 1)
where order_category_id = NEW.order_category_id;

--prepare the pretty_id
Select 
oc.name || '-' || s.current_key AS   current_pretty_id 
FROM    order_category_sequence AS s
JOIN order_category AS oc on s.order_category_id = oc.id
WHERE s.order_category_id = NEW.order_category_id
INTO current_pretty_id;

--update order table
Update "order"
set pretty_id = current_pretty_id
where id = NEW.id;


RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;


CREATE TRIGGER order_created
  AFTER INSERT
  ON "order"
  FOR EACH ROW
  EXECUTE PROCEDURE on_order_created();

If we want to synchronize the two table, order_category and order_category_sequence, we can use another trigger to have a row in the latter table every time a new order category is added.

//function called everytime a new order_category is added
CREATE OR REPLACE FUNCTION on_order_category_created()
  RETURNS trigger AS
$BODY$

BEGIN
--insert a new row for the newly inserted order_category
Insert into order_category_sequence(order_category_id, current_key)
values (NEW.id, 0);

RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;


CREATE TRIGGER order_category_created
  AFTER INSERT
  ON order_category
  FOR EACH ROW
  EXECUTE PROCEDURE on_order_category_created();

Testing query and result:

Insert into order_category(name)
values ('electronics'),('toys');

Insert into "order"(order_category_id)
values (1),(2),(2);


select * from "order";

Regarding your side question, I prefer to store the lookup values like order_status and order_category in separate tables. Doing this allows to have the above flexibility and it is easy when we have changes.

Answer 2

To answer your side question: yes, you should keep tables with names in them, for a number of reasons. First of all, such tables are small and generally kept in memory by the database, so there is negligible performance benefit to not using the tables. Second, you want to be able to use external tools to query the database and generate reports, and you want these kind of labels available to those tools. Third, you want to minimize the coupling of your software to the actual data so that they can evolve independently. Adding a new category should not require modifying your software.

Now, to the main question, there is no built-in facility for the kind of auto-increment you want. You have to build it yourself.

I suggest you keep the sequence number for each category as a column in the category table. Then you can update it and use the updated sequence number in the order table, like this (which is specific to PostgreSQL):

-- set up the tables

create table orders (
  id SERIAL PRIMARY KEY,
  order_category_id int,
  pretty_id VARCHAR
);
create unique index order_category_pretty_id_idx 
  on orders (pretty_id);

create table order_category (
  id SERIAL PRIMARY KEY,
  name varchar NOT NULL,
  seq int NOT NULL default 0
);

-- create the categories
insert into order_category
(name) VALUES
('toy'), ('electronics');


-- create orders, specifying the category ID and generating the pretty ID

WITH 
  new_category_id (id) AS (VALUES (1)), -- 1 here is the category ID for the new order
  pretty AS (
    UPDATE order_category 
    SET seq = seq + 1
    WHERE id = (SELECT id FROM new_category_id)
    RETURNING *
  )
INSERT into orders (order_category_id, pretty_id)
SELECT new_category_id.id, concat(pretty.name, '-', pretty.seq) 
FROM new_category_id, pretty;

You just plug in your category ID where I have 1 in the example and it will create the new pretty_id for that category. The first category will be toy-1, the next toy-2, etc.

| id  | order_category_id | pretty_id     |
| --- | ----------------- | ------------- |
| 1   | 1                 | toy-1         |
| 2   | 1                 | toy-2         |
| 3   | 2                 | electronics-1 |
| 4   | 1                 | toy-3         |
| 5   | 2                 | electronics-2 |

Answer 3

In order to do toys-1 toys-2 toys-3 you should repeat the logic of order_status update, There is no difference between track some status by time or by count. Just in the order_status update it is simpler you just put now() into updated_at for lets say order_category_track you would take last value + 1 or have different sequences respectively category (would not recommend to do like this because it binds database objects with data in the DB). I would change a schema to:

In this schema might be in inconsistent state. But in my opinion in your application there are three different entities "order","order_status","order category track" which live their own lives.

And still it is almost impossible to achieve consistent state for this task with out locks for example. This task is complicated by condition that next rows depends on previous what contradicts with SQL.

Answer 4

I would suggest to split category into 2-level hierarchy: category (toy, electronic) and subcategory (toy-1, toy-2, electronic-1, etc.):

So you can use column order_subcategory.full_name contain compiled "toy-1" value, or you can create view to make this field on the fly:

select oc.name || "-" || os.number
from order_category as oc 
join order_subcategory as os on oc.id = os.category_id

https://dbdiagram.io/d/5dd6a132edf08a25543e34f8

Regarding your questions "Do I need tables for order_category and order_status just to store names?": It is best practice to store this kind of data as a separate dictionary table. It gives you consistency and reliability. Querying those tables is very fast and easy for RDBMS, so feel free to use it.

Answer 5

I'll focus on only 3 tables you showed: order, order_status and order_category. Creating a new table for a new record is not the right way. As your explanation, I think you trying to use order and order_category tables as many to many relationship. If it's so, the thing you need is a pivot table like this:

I currently add order_status column in order table, you can add this column one of these tables as your need.
side question: for order_status, if order status is fixed,( like only ACTIVE,INACTIVE and it won't be more values in the future) it would be better to use a column with ENUM type.

Answer 6

The easy answer would be to answer directly to your question. But I do not think it is a good thing in this case. So I will do otherwise. I think that maybe the whole conception is wrong.

First things first : clarification of your business needs and assertions.

• One order can have multiple categories

• One category can concern multiple orders

• One order can only have one status at a time but multiple through time

• One status can be used by multiple orders

• One order correspond to a file (probably a billing proof)

• One file concerns only one order

Second : Advices

• There is a little amount of reserved key words that you must not use in production environment. (https://www.postgresql.org/docs/current/sql-keywords-appendix.html). So for example I replace the word 'order' by 'command'.
• Remaining questions that mandatory needs an answer before production : why the attributes attribute in your 'order' table? There is a risk of non respect of normal forms here. (https://www.geeksforgeeks.org/normal-forms-in-dbms/)

Third : conception solution

This normally is enough to give you a good start. But I wanna have fun a little more :) So...

Fourth : interrogation on needed performance

• estimation of load per day/month in order (ten million rows per month?)

Fifth : physical solution proposition

• Archiving in another tablespace (trigger when cancel or terminated => archived)
• Indexes in another tablespace (your dba will thank you for that)
• Possible partitionning of order table (https://pgxn.org/dist/pg_partman/doc/pg_partman.html, https://www.postgresql.org/docs/current/ddl-partitioning.html)
• Hardware and option choosings (high availibility? disaster management? if it is: the elaboration needs further study but few)
• Data transposition (is it really needed? if it is: the elaboration needs further study but few)

The finaaaaaal code-down ! (with the good music)

-- as a postgres user
CREATE DATABASE command_system;
CREATE SCHEMA in_prgoress_command;
CREATE SCHEMA archived_command;
--DROP SCHEMA public;
-- create tablespaces on other location than below
CREATE TABLESPACE command_indexes_tbs location 'c:/Data/indexes';
CREATE TABLESPACE archived_command_tbs location 'c:/Data/archive';
CREATE TABLESPACE in_progress_command_tbs location 'c:/Data/command';

CREATE TABLE in_prgoress_command.command
(
    id bigint /*or bigserial if you use a INSERT RETURNING clause*/ primary key
    , notes varchar(500)
    , fileULink varchar (500)
)
TABLESPACE in_progress_command_tbs;

CREATE TABLE archived_command.command
(
    id bigint /*or bigserial if you use a INSERT RETURNING clause*/ primary key
    , notes varchar(500)
    , fileULink varchar (500)
)
TABLESPACE archived_command_tbs;

CREATE TABLE in_prgoress_command.category
(
    id int primary key
    , designation varchar(45) NOT NULL
)
TABLESPACE in_progress_command_tbs;
INSERT INTO in_prgoress_command.category 
VALUES (1,'Toy'), (2,'Electronic'), (3,'Leather'); --non-exaustive list

CREATE TABLE in_prgoress_command.status
(
    id int primary key
    , designation varchar (45) NOT NULL
)
TABLESPACE in_progress_command_tbs;

INSERT INTO in_prgoress_command.status 
VALUES (1,'Shipping'), (2,'Cancel'), (3,'Terminated'), (4,'Payed'), (5,'Initialised'); --non-exaustive list

CREATE TABLE in_prgoress_command.command_category
(
    id bigserial primary key
    , idCategory int 
    , idCommand bigint
)
TABLESPACE in_progress_command_tbs;

ALTER TABLE in_prgoress_command.command_category
ADD CONSTRAINT fk_command_category_category FOREIGN KEY (idCategory) REFERENCES in_prgoress_command.category(id);

ALTER TABLE in_prgoress_command.command_category
ADD CONSTRAINT fk_command_category_command FOREIGN KEY (idCommand) REFERENCES in_prgoress_command.command(id);

CREATE INDEX idx_command_category_category ON in_prgoress_command.command_category USING BTREE (idCategory) TABLESPACE command_indexes_tbs;
CREATE INDEX idx_command_category_command ON in_prgoress_command.command_category USING BTREE (idCommand) TABLESPACE command_indexes_tbs;

CREATE TABLE archived_command.command_category
(
    id bigserial primary key
    , idCategory int 
    , idCommand bigint
)
TABLESPACE archived_command_tbs;

ALTER TABLE archived_command.command_category
ADD CONSTRAINT fk_command_category_category FOREIGN KEY (idCategory) REFERENCES in_prgoress_command.category(id);

ALTER TABLE archived_command.command_category
ADD CONSTRAINT fk_command_category_command FOREIGN KEY (idCommand) REFERENCES archived_command.command(id);

CREATE INDEX idx_command_category_category ON archived_command.command_category USING BTREE (idCategory) TABLESPACE command_indexes_tbs;
CREATE INDEX idx_command_category_command ON archived_command.command_category USING BTREE (idCommand) TABLESPACE command_indexes_tbs;

CREATE TABLE in_prgoress_command.command_status
(
    id bigserial primary key
    , idStatus int 
    , idCommand bigint
    , change_timestamp timestamp --anticipate if you can the time-zone problematic
)
TABLESPACE in_progress_command_tbs;

ALTER TABLE in_prgoress_command.command_status
ADD CONSTRAINT fk_command_status_status FOREIGN KEY (idStatus) REFERENCES in_prgoress_command.status(id);

ALTER TABLE in_prgoress_command.command_status
ADD CONSTRAINT fk_command_status_command FOREIGN KEY (idCommand) REFERENCES in_prgoress_command.command(id);

CREATE INDEX idx_command_status_status ON in_prgoress_command.command_status USING BTREE (idStatus) TABLESPACE command_indexes_tbs;
CREATE INDEX idx_command_status_command ON in_prgoress_command.command_status USING BTREE (idCommand) TABLESPACE command_indexes_tbs;
CREATE UNIQUE INDEX idxu_command_state ON in_prgoress_command.command_status USING BTREE (change_timestamp, idStatus, idCommand) TABLESPACE command_indexes_tbs;

CREATE OR REPLACE FUNCTION sp_trg_archiving_command ()
    RETURNS TRIGGER
language plpgsql
as $function$
DECLARE
BEGIN
    -- Copy the data
    INSERT INTO archived_command.command
    SELECT *
    FROM in_prgoress_command.command
    WHERE new.idCommand = idCommand;    

    INSERT INTO archived_command.command_status (idStatus, idCommand, change_timestamp)
    SELECT idStatus, idCommand, change_timestamp
    FROM in_prgoress_command.command_status
    WHERE idCommand = new.idCommand;    

    INSERT INTO archived_command.command_category (idCategory, idCommand)
    SELECT idCategory, idCommand
    FROM in_prgoress_command.command_category
    WHERE idCommand = new.idCommand;    

    -- Delete the data
    DELETE FROM in_prgoress_command.command_status
    WHERE idCommand = new.idCommand;    
    DELETE FROM in_prgoress_command.command_category
    WHERE idCommand = new.idCommand;    
    DELETE FROM in_prgoress_command.command
    WHERE idCommand = new.idCommand;    
END;
$function$;

DROP TRIGGER IF EXISTS t_trg_archiving_command ON in_prgoress_command.command_status;
CREATE TRIGGER t_trg_archiving_command
AFTER INSERT
ON in_prgoress_command.command_status
FOR EACH ROW
WHEN (new.idstatus = 2 or new.idStatus = 3)
EXECUTE PROCEDURE sp_trg_archiving_command();

CREATE TABLE archived_command.command_status
(
    id bigserial primary key
    , idStatus int 
    , idCommand bigint
    , change_timestamp timestamp --anticipate if you can the time-zone problematic
)
TABLESPACE archived_command_tbs;

ALTER TABLE archived_command.command_status
ADD CONSTRAINT fk_command_command_status FOREIGN KEY (idStatus) REFERENCES in_prgoress_command.category(id);

ALTER TABLE archived_command.command_status
ADD CONSTRAINT fk_command_command_status FOREIGN KEY (idCommand) REFERENCES archived_command.command(id);

CREATE INDEX idx_command_status_status ON archived_command.command_status USING BTREE (idStatus) TABLESPACE command_indexes_tbs;
CREATE INDEX idx_command_status_command ON archived_command.command_status USING BTREE (idCommand) TABLESPACE command_indexes_tbs;
CREATE UNIQUE INDEX idxu_command_state ON archived_command.command_status USING BTREE (change_timestamp, idStatus, idCommand) TABLESPACE command_indexes_tbs;

Conclusion:

• In many cases, when you are worried by the disposition of your keys it is because they are not in the good place. Same goes for cars ones! :D
• Do not take any solution as prophetic solution : benchmark it.