Developing a database model

We will use JPA, and one of the most noticeable points is that creating a database is not recommended, because as we know, JPA will automatically create database tables with fields using the entity class of the project. But still, we need to create a demo database and draw an EER diagram. You can create your EER on paper or you can create one digitally using MySQL Workbench. Here, we will use MySQL Workbench, which has a free version. This is one of the best tools for developing a database or creating a model for a database:

1. You need to download this software from https://dev.mysql.com/downloads/workbench/, if you don't have it. Then install and run it. As we mentioned before, we have some default values:

Host -- localhost // our hosting URL
Port -- 3306 // our hosting port
Username -- root // username of the MySQL
Password -- 12345678 // password of the MySQL

2. Open this application and select the Models option, as shown in the following screenshot:

3. Click the plus (+) sign to create a new model for our application. In the new window, you will find all the necessary features to create a model. Save this model as my_app:

4. Create a table called Profile. The columns will be id (primary key), username, password, email, first_name, last_name, acc_created_time, contact_number, dob, city, and country.
5. A table called Post. The columns will be id (primary key) and text.
6. Create another table called Comment. The columns will be id (primary key) and text.
7. Lastly, create a table called Like. The column will be id (primary key).

But there are some relationships between the tables:

• Between Profile and Post: There is a many-to-one relation for Post because a user can post multiple statuses, and each post has only one user.
• Between  Profile and Comment: There is a many-to-one relation for Comment because a user can post multiple comments, and each comment has only one user.
• Between  Profile and Like: There is a many-to-one relation for Like because a user can like multiple  posts, and each like has only one user.
• Between  Post and Comment: There is a one-to-many relation for Post because a post may have multiple comments, but a comment is for only one specific post.
• Between  Post and Like: There is a one-to-many relation for Post because a post may have multiple likes, but each like is for only one specific post:

So after all the relations, we can see the table names of the database, as shown in the following screenshot:

Finally, you can create the EER diagram by clicking on the EER Diagram icon, as shown in the following screenshot:

This is the EER diagram model of our project. You may find two extra tables, named post_likes and post_comments. It generates these using JPA and Hibernate. We will discuss this later.

So, our database modeling is done. Now you can export it as SQL and create the database for the project. But we recommend you not to do this because we need to do some modification. 

Now create the project.