2 replies

Need Help for Database Structure

Hi folks!

I'm building an Online Turkish Dictionary where I want users can contribute to the dictionary by adding, editing and maybe removing the words, meanings, pronunciations and so on. (using t3-create-app, app dir with drizzle and postgres)

You can see the initial ER Diagram in the first picture.

What I actually want is that users can request changes for words, meaning, and all other tables related to a dictionary. Imagine user sees a typo or misinformation in a word, meaning, or partOfSpeech of a meaning, they can request editing by sending the edited data, removing data or adding a new data to the dictionary. As you can see in the first pic, I have user roles which means these requests can be in pending state and accepted or rejected by authorized people.

I thought that extra tables suffixed with Requests like Word_Requests, Meaning_Requests would solve it. However, I realized that it almost doubles my table count since I need this logic for all tables. See the second diagram. I couldn't even complete the diagram here :D

I asked chatGPT what I want and it suggested me the idea of creating Requests table as below:

CREATE TABLE Requests (
    request_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES Users(user_id),
    entity_type VARCHAR(50) NOT NULL,  -- Entity type (e.g., Word, Meaning)
    entity_id INT,  -- ID of the specific entity (word_id, meaning_id, etc.)
    action VARCHAR(20) NOT NULL,  -- Action (add, edit, delete)
    new_data JSONB,  -- JSON or JSONB column to store new data for the entity
    request_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending'  -- Status of the request (pending, approved, rejected)
);

CREATE TABLE Requests (
    request_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES Users(user_id),
    entity_type VARCHAR(50) NOT NULL,  -- Entity type (e.g., Word, Meaning)
    entity_id INT,  -- ID of the specific entity (word_id, meaning_id, etc.)
    action VARCHAR(20) NOT NULL,  -- Action (add, edit, delete)
    new_data JSONB,  -- JSON or JSONB column to store new data for the entity
    request_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending'  -- Status of the request (pending, approved, rejected)
);

It makes sense to me at first glance but I'm not sure about it. This approach seems flexible and scalable. I guess I can even create dynamic pages to show what's requested to change ? What do you guys think ?
I'll be grateful if I can have your opinions. You can ask me anything especially if something is not clear.

KP-C

Kevin Powell - Community•3y ago•

2 replies

Furki4_4

Need Help for Database Structure

CREATE TABLE Requests (
    request_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES Users(user_id),
    entity_type VARCHAR(50) NOT NULL,  -- Entity type (e.g., Word, Meaning)
    entity_id INT,  -- ID of the specific entity (word_id, meaning_id, etc.)
    action VARCHAR(20) NOT NULL,  -- Action (add, edit, delete)
    new_data JSONB,  -- JSON or JSONB column to store new data for the entity
    request_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending'  -- Status of the request (pending, approved, rejected)
);

CREATE TABLE Requests (
    request_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES Users(user_id),
    entity_type VARCHAR(50) NOT NULL,  -- Entity type (e.g., Word, Meaning)
    entity_id INT,  -- ID of the specific entity (word_id, meaning_id, etc.)
    action VARCHAR(20) NOT NULL,  -- Action (add, edit, delete)
    new_data JSONB,  -- JSON or JSONB column to store new data for the entity
    request_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending'  -- Status of the request (pending, approved, rejected)
);

Need Help for Database Structure

Need Help for Database Structure

Similar Threads

Similar Threads

Similar Threads