Database Design for basic story sharing site
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:
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?
链接地址: http://www.djcxy.com/p/70628.html下一篇: 基础故事分享网站的数据库设计