Notable points on SQL database

I. What is SQL

SQL is a standard language for accessing and manipulating databases.
Src: https://www.w3schools.com/

In general, with SQL, we can:

• execute queries against a database to retrieve data from a database, insert/update/delete records in a database
• create new databases
• create new tables in a database
• create stored procedures in a database
• create views in a database
• set permissions on tables, procedures, and views

SQL database includes tables which are related/non-related to each others. I’m not here to talk about how to query these datas. Instead, I’ll just mention some main concepts used in a real project.

II. Unidirectional, Bidirectional, Cascade

The direction of a relationship can be either bidirectional or unidirectional. A bidirectional relationship has both an owning side and an inverse side. A unidirectional relationship has only an owning side.
Src: https://docs.oracle.com/

Assume that i have a database of members and clubs like this, also assume that each member can only belong to 1 club.

1) Unidirectional

In a unidirectional relationship, only one entity has a relationship field or property that refers to the other.

public class Member {
    private int     id;
    private int     cardNo;
    private String  name;
    @ManyToOne
    @JoinColumn(name = "clubId", referencedColumnName="id")
    private Group   club;
}

public class Club {
    private int     id;
    private String  name;
}

public class Member {
    private int     id;
    private int     cardNo;
    private String  name;
}

public class Club {
    private int     id;
    private String  name;
    @OneToMany
    private Set<Member> members;
}

As you can see from the first code, member knows which club it belongs to while club doesn’t know what members it has. On the contrary, the second one means that the club knows it’s member but member doesn’t know what club it belongs to. If we see this one-way reference, it’s unidirectional relationship.

2) Bidirectional

In a bidirectional relationship, each entity has a relationship field or property that refers to the other entity. Through the relationship field or property, an entity class’s code can access its related object. If an entity has a related field, the entity is said to “know” about its related object.

public class Member {
    private int     id;
    private int     cardNo;
    private String  name;
    @ManyToOne
    @JoinColumn(name = "clubId", referencedColumnName="id")
    private Group   club;
}

public class Club {
    private int     id;
    private String  name;
    @OneToMany(mappedBy="club")
    private List<Member> members;
}

In bidirectional relationship, the reference happens both ways.

3) Cascade

Entities that use relationships often have dependencies on the existence of the other entity in the relationship. For example, a member is part of a club; if the club is deleted, the member also should be deleted. This is called a cascade delete relationship.

There are other cascade operations:

In general, the main difference is that bidirectional relationship provides navigational access in both directions, so that you can access the other side without explicit queries. Also it allows you to apply cascading options to both directions.

III. ERD (Entity Relationship Diagram)

So, how can we decide whether use unidirectional or bidirectional, one to many, one to one, many to one etc. We can draw!

https://www.lucidchart.com/pages/er-diagrams

Let’s say we are trying to build a simple app for hotel booking. Here are the questions that i would likely to ask myself:

• What are the entities that i need?
  Ex: I would need Room, Guest, Booking, maybe Rating.
• What are the attribute of each object?
  Ex: Each will definitely has an id, “Room” might need name, number of people, rating, available status,…
• What is the relationship between these object?
  Ex: Each guest will probably has 1 ore more bookings, booking will defenitely has a relation to room, and room can have many ratings.
• When i retrieve object info, what information i need?
  Ex: If i get info about a room, i would want to get the rating as well. How would i want to do that, whether i want to call 1 api to get the room info that return the rating as well and is there anychance i need to get the rating that need reference to room. In the case i dont need to get the rating separately, there is no need to reference the room in rating entity, therefore, unidirectional relationship would be fine.

The more complicated the system is, the more questions we need to make.
This may take some times, but definitely give us a clear view on what and how we’re going to write our codes.

With my experience so far, this is what i look into everytime check the entity of any backend project to get my overview of the database. Ofcourse, the insight of each point mentioned above requires a lot more writtings: how implement the code, how to query, the problems we might face, etc. I’ll dive into this in another blogs. But for now, i hope this helps.