1

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.

Understanding a notification system

  • Side note: can a chapter be a part of more than one story? If not, I'd merge story_chapters and chapter into one table. – RDFozz Mar 19 '18 at 18:02
  • As i understand my table. 1 story - story_chapter(story_id, chapter_id) - 1 chapter. so 1 story row in story table but multiple rows in chapter table for the same story. so in my story_chapter table, rows would look like this: 0 - 1 - 1 1 - 1 - 2 2 - 1 - 3 First col is the ID(PK, AI) second is the story_id, 3rd is the chapter_id. – Dween Mar 19 '18 at 18:04
  • That structure is most commonly seen when there's a many-to-many relationship between two tables. It sounds like there would be a one-to-one relationship between story_chapter and chapter. Any time there's a 1:1 relationship between two tables, you should ask yourself why you have two separate tables. I'm not saying never do it, just that it rarely makes sense in the long run. – RDFozz Mar 19 '18 at 18:23
  • Idk, I read up recently on normalization and i understand them but i'm really bad at implementing them or seeing them on my design. Tbh, when i designed my database, i just based it off the design i made years ago where i had someone normalize my table and that left a significant impression on me that i just separate tables subconsciously. I think i view that design as the final output and i just strive to make my db more like that design regardless if its needed/required. – Dween Mar 19 '18 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.