CODEWITHSUNDEEP
database-design
Rahul Prasad
Rahul Prasad

Reputation: 8222

Database design problem

I am designing a database for an application where a user can comment on pictures & videos.

Should I keep separate tables for comments like (Picture_comments & videos_comments)
OR
should I keep single table for comment and other mapping tables like (picture_comment_mapping & video_comment_mapping).

Which one will be better approach and why?

Detail
Approach 1:

user
     id
     name

Picture
     id
     link
video
     id
     link

comment_video
     id
     user_id
     video_id
     comment
comment_picture
     id
     user_id
     picture_id
     comment

Approach 2:

user
     id
     name

picture
     id
     link
video
     id
     link

comment
     id
     user_id
     comment

comment_picture_mapping
     id
     comment_id
     picture_id
comment_video_mapping
     id
     comment_id
     video_id

Which one is better approach and why?

Upvotes: 3

Views: 118

Answers (4)

user212102
user212102

Reputation: 81

You only need about three entities (or two if you don't include the User entity).

Your approach 2 implies that a particular comment can be about more than one picture or video. Is that what you want?

User {one-to-many} Comment {many-to-one} media (media type)

Upvotes: 0

Matt
Matt

Reputation: 3848

I would use your approach number 2 because its much easier to then do things like "Search all comments"

Upvotes: 2

Jahid
Jahid

Reputation: 66

I would make an entity with name "Comment", and add relation mapping on on that comment entity with user. The comment entity will also contain enumerated value on one column saying if its for picture or its for video. Something like -

enum MimeType {
    PICTURE, VIDEO;
}

class Comment {
    @ManyToONe
    private User user;
    @Enumerated
    private MimeType mimeType;
}

Why I like this approach? Because its cleaner, and resulting entities will be less, and I do not have to write different queries to search for different types of mime type comments. And since comments are on diff table, i can load them lazily.

Upvotes: 1

Ravi Vyas
Ravi Vyas

Reputation: 12375

You can have just one table comments where one field will specific the type of the comment. This is basically Option 2 with no mapping , a single field is enough to know what type of comment it is. As for the video or picture ID you can consider them as generic objects and the consumer can decide what to do with them based on the type or comment or object mime type. This should allow you to add more types of comments in the future.

Comment
 id
 user_id
 comment_text
 comment_type
 linked_object_id

CommentObect
  id
  type
  object

Upvotes: 1

Related Questions