Kevin Powell - Community•12mo ago

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.

1 Reply

theoriginalandrew•12mo ago

Assuming you have a table which contains a list of each word/pronunciation/meanings/etc. and a unique ID for that called like dictionary_words_table , you could also have a last_updated_at field so that you know you're always displaying the latest updated value. Based on that assumption, you could have a history table which stores all updates for everything and each history item takes in the same values for the dictionary_words_table and some sort of unique id for each which matches up to the ID in the dictionary_words_table . Then on the BE side, you'd just need to take note of the timestamp every time a request is made to change data, insert the now "old" data into the history table and insert the new data into the dictionary_words_table. The history approach also means that you have the ability to pull out previous entries if something goes wrong or if you want to undo something, or if you want to show a brief history of a specific dictionary entry.

Gaming

Programming

Need Help for Database Structure