How to design database tables efficiently in Mysql

Question

I have the following pojo

   public class Like {
     private Long commentId;
     private Collection<Long> accountIds;
   }

   public class Comment {
private Long personId;
    private Long pageId;
    private Long Id;
    private String text;
    private Like like;
    private LocalDate commentDate;
   }

   public class Page {
     private Long Id;
     private Long textId;
     private Collection<Comment> comments;
     private LocalTime postingDate;
     private ViewType type;
     private String mediaUrl;
     private Collection<Long> openAccountIds;
     private Like like;
   }

    public class Text{
      private Long accountId;
      private Long Id;
      private String name;
      private LocalTime firstPostedTime;
      private LocalTime lastPostedTime;
      private ViewType type;
      private Collection<Page> pages;
      private Like like;
      private String description;
      private Collection<Long> openAccountIds;
     }

Now i have my text repository as follows:

 public interface TextRepository {

Collection<Text> getAllTexts(Long accountId);

Diary getText(Long TextId);

Page getPage(Long pageId);

Comment getComment(Long commentId);

void addPageToText(Long TextId , Page page);

void addCommentToPage(Long pageId , Comment comment);

void updateText(Text text);

void deletePage(Long pageId);

void deleteComment(Long commentId);

void updateLikeToText(Long textIds);

void updateLikeToPage(Long pageId);

void updateLikeToComment(Long commentId);

}

I am a new bie to mysql. I wanted to know how to efficiently create mysql tables so i can retrieve the data in less possible time. Also if my pojo's contains any flaw in structure go ahead to change them or provide suggestions.

Answer 1

Here are some suggestions for the object model to consider (see comments),

// Specifying all the fields as private will not allow
// any other class to use the data!
public class Account
{
    public String name;
    public String location;
}

public class Text
{
    public Collection<Account> likedBy;
    public Collection<Account> openAccounts;
    public Collection<Page> pages;
    public Account postedBy; 
    public String name; // Not sure what this field represents...
    public LocalTime firstPostedTime;
    public LocalTime lastPostedTime;
    public ViewType type;
    public String description;

    // Consider using get/set methods for collections, 
    // so as to expose only minimal required information 
//  public like(Account account)
//  {
//      likedBy.add(account);
//  }
//  
//  public dislike(Account account)
//  {
//      likedBy.remove(account);
//  }
}

public class Page
{
    public Collection<Comment> comments;
    public LocalTime postingDate;
    public ViewType type;
    public String mediaUrl;
    public Collection<Account> openAccounts;
    public Collection<Account> likedBy;

//  public addComment(Comment comment)
//  {
//      ...
//      Update posting date
//  }
//  
//  public addOpenAccount(Account account)
//  {
//      ...
//  }
}

public class Comment 
{
    public Account postedBy;
    public String text;
    public Collection<Account> likedBy;
    public LocalDate commentDate;
}

The next step would be to construct an entity-relationship diagram. The primary keys and foreign keys (xxxId) are introduced while normalizing the schema.

The schema could look like this,

• Account [id, name, location]
• ViewType [id, description]
• Comment [id, posted_by_account_id, text, postedDate]
• CommentLikes [comment_id, account_id]
• Text [id, account_id, name, firstPostedTime, lastPostedTime, type_Id, description]
• TextAccounts [text_id, account_id]
• TextLikes [text_id, account_id]
• TextPages [text_id, page_id]
• Page [id, mediaUrl, type_id, postingDate]
• PageLikes [page_id, account_id]
• PageComments [page_id, comment_id]
• PageAccounts [page_id, account_id]