UML to Relations

UML class Relationships to Relation tables explanation

Translating directly from UML we've got:

Relation	Attribute
Post	post_id post_content timestamp filter_content
User	user_id
ChatRoom	chatroom_id
ChatRequest	chatrequest_id action status
ChatRole	chatrole_id
ChatRank	chatrank_id chatrank_name allowed_actions
Message	message_id message_content timestamp
Comment	comment_id comment_content timestamp
Tag	tag_id tag_content
Like	like_id like_type
Share	share_id share_type
Report	report_id report_content is_justified
ReportType	report type_id reporttype_name reporttype_content
Notification	notification_id is_read timestamp

Image from UML can be merged into PostImage and will be explained in the following translation

1. Post have likes

Explaination: A Post can get many to no Likes, but a Like belongs to a Post

Updated changes:

Relation	Attribute	Foreign keys
Like	... post_id	post_id : Post(post_id)

2. User posts content

Explaination: A User posts a Post, but a Post can have only one User.

Updated changes:

Relation	Attribute	Foreign keys
Post	... user_id	user_id : User(user_id)

3. Posts have tags

Explaination: A Post can have many to no Tags, while a Tag always atleast have 1 post

Updated changes:

Relation	Attribute	Foreign keys
PostTag	tag_id post_id	tag_id : Tag(tag_id) post_id : Post(post_id)

4. Post have comments

Explaination: A Post may have many to no Comments, but a Comment belongs to one Post.

Updated changes:

Relation	Attribute	Foreign keys
Comment	... post_id	post_id : Post(post_id)

5. Post have shares

Explaination: A Post may have many to no Shares, but a Share belongs to one Post.

Updated changes:

Relation	Attribute	Foreign keys
Share	... post_id	post_id : Post(post_id)

6. Post may or may not have many images

Explaination: A Post may have many to no Images, but a Image belongs to one Post.

Updated changes:

Relation	Attribute	Foreign keys
PostImage	post_id	post_id : Post(post_id)

7. Post creation sends notifications to followers with notify on

Explaination: A Post creation sends a notification to Post owner's followers, whereas the relationship of Post to Notification is a one-to-one.

Updated changes:

Relation	Attribute	Foreign keys
Notification	... notification_of	notification_of : Class object Post

8. Report post

Explaination: A Post may never or have been reported many times, but a Report belongs to one Post.

Updated changes:

Relation	Attribute	Foreign keys
Report	... report_of	report_of : Class object Post

9. Like sends notification to post owner *REPLACE

Explaination: A Like sends a notification to Post owner, whereas the relationship of Like to Notification is a one-to-one.

Updated changes:

Relation	Attribute	Foreign keys
Notification	... notification_of	notification_of : Class object Like

10. Like by a user

Explaination: A Like can only be created by a User, but a User can create many likes.

Updated changes:

Relation	Attribute	Foreign keys
Like	... user_id	user_id : User(user_id)

11. Share sends notifications to post owner *REPLACE

Explaination: A Share sends a notification to Post owner, whereas the relationship of Share to Notification is a one-to-one.

Updated changes:

Relation	Attribute	Foreign keys
Notification	... notification_of	notification_of : Class object Share

12. Share by a user

Explaination: A Share can only be created by a User, but a User can create many shares.

Updated changes:

Relation	Attribute	Foreign keys
Share	... user_id	user_id : User(user_id)

13. User have interests

Explaination: A User may have many to no interests, which can be represented by Tags, and a Tag many have many to no Users who's interested.

Updated changes:

Relation	Attribute	Foreign keys
UserInterest	user_id tag_id	user_id: User(user_id) tag_id : Tag(tag_id)

14. User have disinterests

Explaination: A User may have many to no disinterests, which can be represented by Tags, and a Tag many have many to no Users who's disinterested.

Updated changes:

Relation	Attribute	Foreign keys
UserDisinterest	user_id tag_id	user_id: User(user_id) tag_id : Tag(tag_id)

15. Comment creation sends notifications to post owner

Explaination: Comment creation sends a notification to Post owner, whereas the relationship of Comment to Notification is a one-to-one.

Updated changes:

Relation	Attribute	Foreign keys
Notification	... notification_of	notification_of : Class object Comment

16. Report comment

Explaination: A Comment may never or have been reported many times, but a Report belongs to one Comment.

Updated changes:

Relation	Attribute	Foreign keys
Report	... report_of	report_of : Class object Comment

17. Comment by a user

Explaination: A Comment can only be created by a User, but a User can create many comments.

Updated changes:

Relation	Attribute	Foreign keys
Comment	... user_id	user_id : User(user_id)

18. Report user

Explaination: A User may never or have been reported many times, but a Report belongs to one User.

Updated changes:

Relation	Attribute	Foreign keys
Report	... report_of	report_of : Class object User

19. User submits Report

Explaination: A User can submit many Reports, but a Report can only belong to one User.

Updated changes:

Relation	Attribute	Foreign keys
Report	... reporter_id	reporter_id : User(user_id)

20. Notification is sent to a User

Explaination: A User will receive a notification per each Notification created for them.

Updated changes:

Relation	Attribute	Foreign keys
Notification	... receiver_id	receiver_id : User(user_id)

21. Users can block eachother

Explaination: A User can block many to no Users and a User themselves can be blocked by many Users.

Updated changes:

Relation	Attribute	Foreign keys
Block	blocker_id blocked_id	blocker_id: User(user_id) blocked_id: User(user_id)

22. Users can follow eachother

Explaination: A User can follow many to no Users and a User themselves can be followed by many Users.

Followers may choose whether they want to be notified whenever a Post is created by the User they followed as well.

Updated changes:

Relation	Attribute	Foreign keys
Follow	user_id follower_id notify	user_id: User(user_id) follower_id: User(user_id)

23. User is notified when they get a follow

Explaination: A User receives a notification when they are followed by other users, whereas the relationship of Follow action to Notification is a one-to-one.

Updated changes:

Relation	Attribute	Foreign keys
Notification	... notification_of	notification_of : Class object Follow

24. Report have types based on community guidelines

ReportType Justification: In contrast to other types attributes, Reports can have overlapping issues across themselves, thus it would be useful to create ReportType for Report to categorize problems so we can set up a solution protocol/pipeline for each problem to automate the solving process, as more Users use the platform, more problems arise, thus it necessary to make ReportType more flexible and adaptive to changes.

Explaination: A Report can only have one ReportType, while a ReportType may have many to no Reports.

Updated changes:

Relation	Attribute	Foreign keys
Report	... reporttype_id	reporttype_id : ReportType(reporttype_id)

25. Users in a chat room

Explaination: Users may be in many to no ChatRooms, but a ChatRoom requires at least one user.

Updated changes:

Relation	Attribute	Foreign keys
ChatRole	... user_id chatroom_id	user_id : User(user_id) chatroom_id :ChatRoom(chatroom_id)

26. Chat role references a Rank

Explaination: Each User have a Ranking within a ChatRoom (admin, moderator, vip, member, etc.)

Updated changes:

Relation	Attribute	Foreign keys
ChatRole	... chatrank_id	chatrank_id : ChatRank(chatrank_id)

27. Report ChatRoom

Explaination: A ChatRoom may never or have been reported many times, but a Report belongs to one ChatRoom.

Updated changes:

Relation	Attribute	Foreign keys
Report	... report_of	report_of : Class object ChatRoom

28. User get a notification when their Chat Role is changed

Explaination: ChatRole changes sends a notification to User of the ChatRole, whereas the relationship of ChatRole to Notification is a many-to-one.

Updated changes:

Relation	Attribute	Foreign keys
Notification	... notification_of	notification_of : Class object ChatRole

29. Chat Request ( invite / kick )

Explaination: A ChatRoom may have many to no ChatRequest, but a ChatRequest can only belong to one ChatRoom

Updated changes:

Relation	Attribute	Foreign keys
ChatRequest	chat_id	chat_id : ChatRoom(chat_id)

30. Sender and Receiver Users get notification to chat request creation/changes

Explaination: Both Sender and Receiver of ChatRequest may receive a Notification, depending on the context of ChatRequest, but atleast one Notification of ChatRequest is created. While those Notifications is created belonging to one ChatRequest.

Updated changes:

Relation	Attribute	Foreign keys
Notification	... notification_of	notification_of : Class object ChatRequest(receiver/sender is handled differently)

31. User receive Chat Request

Explaination: A User who is not yet in the ChatRoom may receive many to no ChatRequest, but a ChatRequest can only be sent to a User.

Updated changes:

Relation	Attribute	Foreign keys
ChatRequest	... receiver	receiver : User(user_id)

32. User send Chat Request

Explaination: A User who is in the ChatRoom may send many to no ChatRequest, but a ChatRequest can only be sent by a User.

Updated changes:

Relation	Attribute	Foreign keys
ChatRequest	... sender	sender : User(user_id)

33. Report Message

Explaination: A Message may never or have been reported many times, but a Report belongs to one Message.

Updated changes:

Relation	Attribute	Foreign keys
Relation	Attribute	Foreign keys
-	-	-
Report	... report_of	report_of : Class object Message

34. User send message

Explaination: A User may send many to no Messages, but a Message can only be sent by a User.

Updated changes:

Relation	Attribute	Foreign keys
Message	... user_id	user_id : User(user_id)

35. Users in the chatroom receives a notification from a message in chatrooms they are in

Explaination: Message creation sends a notification to every other user in the ChatRoom, whereas the relationship of Message to Notification is a one-to-one.

Updated changes:

Relation	Attribute	Foreign keys
Notification	... notification_of	notification_of : Class object Message

36. Chat room has messages

Explaination: A ChatRoom may have many to no Messages, but a Message can only belong to one ChatRoom.

Updated changes:

Relation	Attribute	Foreign keys
Message	chat_id	chat_id : ChatRoom(chat_id)

Translated Relations Result

Relation	Attribute	Foreign keys
Post	post_id user_id post_content timestamp filter_content	user_id: User(user_id)
Follow	user_id follower_id notify	user_id: User(user_id) follower_id: User(user_id)
Block	blocker_id blocked_id	blocker_id: User(user_id) blocked_id: User(user_id)
User	user_id username email password user_name birthday bio profile header color language filter_content
UserChat	user_id chat_id	user_id: User(user_id) chat_id: ChatRoom(chat_id)
UserInterest	user_id tag_id	user_id: User(user_id) tag_id : Tag(tag_id)
UserDisinterest	user_id tag_id	user_id: User(user_id) tag_id : Tag(tag_id)
ChatRoom	chat_id chat_name profile
ChatRequest	chatrequest_id chat_id sender receiver action status	chat_id : ChatRoom(chat_id) sender : User(user_id) receiver : User(user_id)
ChatRole	chatrole_id user_id chatroom_id chatrank_id	user_id : User(user_id) chatroom_id : ChatRoom(chatroom_id) chatrank_id : ChatRank(chatrank_id)
ChatRank	chatrank_id chatrank_name allowed_actions
Message	message_id user_id chat_id message_content timestamp	user_id: User(user_id) chat_id: ChatRoom(chat_id)
Comment	comment_id user_id post_id comment_content timestamp	user_id: User(user_id) post_id: Post(post_id)
Tag	tag_id tag_content
PostTag	tag_id post_id	tag_id: Tag(tag_id) post_id: Post(post_id)
Like	like_id user_id post_id like_type	user_id: User(user_id) post_id: Post(post_id)
Share	share_id user_id post_id share_type	post_id: Post(post_id) user_id: User(user_id)
ReportType	reporttype_id reporttype_name reporttype_content
Report	report_id report_of reporter_id reporttype_id report_content is_justified	report_of : Class object User, Post, Comment, Chatroom, Message reporter_id : User(user_id) reporttype_id : ReportType(reporttype_id)
PostImage	postimage_id post_id image	post_id : Post(post_id)
Notification	notification_id notification_of receiver_id is_read timestamp	notification_of : Class object Follow, Like, Share, Post, Comment, Message, ChatRole, ChatRequest(receiver/sender is different) receiver_id : User(user_id)