Sample Header Ad - 728x90

Postgres too Many References to Single Table is good or bad design

1 vote
1 answer
718 views
*I have a Postgres database schema for a web app that needs real-time data and hence a huge volume of select, update, insert queries. Data manipulation is done via Postgres stored procedure mostly* **I have designed each table to have {created, updated, deleted}_by user all points to user table Id. In total, I will have around ~100-150 such references.** In the users table, I see 100+ lines in reference by TABLE "XXX" CONSTRAINT "XXXXX" FOREIGN KEY (updated_user_id) REFERENCES users(id) ON DELETE SET NULL TABLE "XXY" CONSTRAINT "XXY" FOREIGN KEY (created_user_id) REFERENCES users(id) ... ... ...~150+ I wanted to know if it's a good design or not and are there any pros and cons of such a database design. Are there any pros and cons of it I would really love to hear from all? Note my max data size/year will be < ~50GB/year Note I have these indexes on users table Indexes: "id_x" PRIMARY KEY, btree (id) "ABC" btree (id, username, email) "XYZ" UNIQUE, btree (lower(email::text)) "aaa" UNIQUE, btree (lower(username::text)) "bbb" UNIQUE CONSTRAINT, btree (email) "cccc" UNIQUE CONSTRAINT, btree (username)
Asked by Smarhacker (11 rep)
Mar 7, 2022, 06:06 PM
Last activity: Mar 7, 2022, 10:25 PM