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.
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
Each table can have only one AUTO_INCREMENT column. It must defined as a key (not necessarily the PRIMARY KEY or UNIQUE key).
A unique key does not supports auto increment value. We cannot change or delete values stored in primary keys. We can change unique key values.
To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT. Creating a table, with “id” as auto-increment.
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.
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 |
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With