jaeyun0503 opened a new issue, #3709: URL: https://github.com/apache/texera/issues/3709
## Issue The current schema of the table `user_activity` needs to be refactored in terms of its table name, column names and foreign key constraint. ## Context `user_activity` is a table that records users' interactions with entities like dataset or workflow. There are four types of actions: Like, Unlike, Clone and View. The table records every time when a user does one of the four actions. As we had internal discussions for Issue #3624 to decide if we should put user's last active time into `user_activity`, we found some improvements to be done on this table. As shown below (under "Current Schema"), there are few problems with the schema: 1. There is no foreign key constraint to `user` table: There should be a N-to-1 relation with the `user` table, and since the uid comes from the `user` table, there should be a constraint. 2. The order of the attributes/columns does not make logical sense. 3. The table name `user_activity` needs to be renamed as it is more about actions. 4. Some column names need to be renamed: Column names are too vague, and there is a spelling error on `activate`. ## Proposed Change To conform with naming conventions, we proposed these changes: 1. Change the table name `user_activity` to `user_action`. 2. Add a foreign key constraint from `user_action` to `user`, and set to `ON DELETE SET NULL`. 3. Change the column name `type` to `resource_type`. 4. Change the column name `id` to `resource_id`. 5. Change the column name `activate` to `action`. 6. Change the column name `activity_time` to `time`. 7. Change the order of the columns to `uid, ip, time, resource_type, resource_id, action`. The proposed database diagram is attached below under "Proposed Schema". ### Current Schema <img width="692" height="335" alt="Image" src="https://github.com/user-attachments/assets/5be74160-78a4-423a-9494-098cd6c3e126"; /> ### Proposed Schema <img width="669" height="344" alt="Image" src="https://github.com/user-attachments/assets/c7aedef2-5b2a-464c-96de-b24a9b82f1a8"; /> -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: dev-unsubscr...@texera.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
