Basic Database Design for Story Sharing

Tables: Users User_profile -------- ------------- user_id(PK) user_profile_id(PK) username user_id(FK) password display_name date_created birth_date last_login gender Story story_chapter chapters ------ ----------------- ------------- story_id(PK) story_chapter_id(PK) chapter_id(PK) image_id(FK) story_id(FK) chapter_title story_title chapter_id(FK) chapter_content synopsis date_added last_updated story_genre story_tag story_content_warning ----------- --------- ---------------------- story_genre_id(PK) story_tag_id(PK) story_content_warning_id(PK) story_id(FK) story_id(FK) story_id(FK) genre_id(FK) tag_id(FK) content_warning_id(FK) genre tags content_warning ------- ---------- ---------------- genre_id(PK) tag_id(PK) content_warning_id(PK) genre_name tag_name content_warning Published_story ----------------- Profile_image image_type published_id(PK) -------------- ------------ story_id(FK) profile_image_id(PK) image_type_id(PK) user_id(FK) image_type_id(FK) type_name published_date file_name last_updated file_path Bookmark Follow_author ---------- -------------- bookmark_id(PK) Follow_id(PK) published_id(FK) published_id(FK)//get the user_id in published table user_id(FK) user_id(FK)//user_id of the user who followed bookmark_date follow_date review review_type chapter_review/rate ------------ ----------- -------------------- review_id(PK) review_type_id(PK) chapter_rate_id(PK) published_id(FK) type_name chapter_id(FK) review_type_id(FK) user_id(FK) rating rating content/summary rate_date review_date image_type table is nothing special. The row type_name refers to what page this image is used for since each page has different display dimension. For example: 'story' type name is used to display story image cover(200x200). 'user' is used for user profile(100x100). I used image_lib in codeigniter for this. $this->image_lib->resize(); So in my folder where i upload images uploaded. I store 3 types, the original,200x200 version and 100x100 version. **User can:** - browse story - publish story - review story/author/chapter(other users who have published their story) - bookmark story - follow author - get notification Let me elaborate on the notification part. **Users:**
receives notification if a new chapter is added to a story(bookmarked)
receives notification if a new story is published by the author(followed)
receives notification changes made to story/chapter(edit)
**Authors:**
receives a notification if a user/reader reviews story and rates chapter.
receives a notification if a user/reader followed author or bookmarked story.
receives a notification if a user/reader reviews author(profile).
Based on the above specifications, is my database design good enough? Also on notification. I understand that i need to make a log/events table that will record every action. For example, if a user published a story then i'll have to insert it into log/events table. Problem is, idk how to design the event table. There's different kind of events(publish,rate,review etc.). So i need a 'type' column to distinguish. But where do i put the id foreign key to point to the actual record of event. Say published_id(FK) for a publish event. or review_id(FK) for a review event. It would be wrong to just put one column as action_id(FK) but point it to multiple tables(published_story,review,bookmark etc.) I also read online that its bad to have multiple foreign key columns that are null. Since for 1 row i only need 1 foreign key to point which table so the rest of the columns will be null. Event ---------- event_id(PK) publish_id(FK) NULL bookmark_id(FK) NULL review_id(FK) NULL Follow_id(FK) NULL event_date Update: I really have no idea how to design my notification system. If i do not want the above table with multiple null foreign keys, i have to separate them into different tables. event_publish event_review event_follow ------------- ------------- ------------- event_publish_id(PK) event_review_id(PK) event_follow_id(PK) publish_id(FK) review_id(FK) follow_id(FK) event_bookmark event_chapter_rate --------------- ------------------- event_bookmark_id(PK) event_chapter_rate_id(PK) bookmark_id(FK) chapter_rate_id(FK) How do i incorporate the above tables to my notification system then? What i've done with a similar problem was with the profile_image table. There are 2 types of image, 1 for user and 1 for story. If i were to Foreign key the user_id and story_id in the profile_image table, then i would have to set 2 foreign keys and both being NULL. As for each row, either column would have to be null to determine which table the row belongs to. For the notification, i'm using this as the reference but i just cant wrap my head around to implementing it. I just need a simple notification. Nothing fancy. https://dba.stackexchange.com/questions/99401/understanding-a-notification-system?newreg=367be62ed3ee40339f71f4a1ddac22c1