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.
