Relational Databases (Designing Schema & Writing a Query)

Hi folks !! In this post, i will talk about how to design a quick relational database schema and how to write a raw sql query in relational database . So, without wasting time lets get started.

Before delving into designing a schema, lets look at the properties that makes a good Database . ACID Properties of a transaction in a db .

Atomic : Each transaction is treated as a single unit, which either succeeds completely, or fails completely.
Consistent : DB is consistent before and after the transaction.
Isolated : Transactions occur independently without interference. means reading and writing to multiple tables at the same time.
Durability : Durable in crashes and is fault tolerant . means once a transaction has been committed, it will remain committed even in the case of a system failure.

Designing DB Schema

Note : I am already assuming that you have choose to opt for a relational DB like (sql , postgres etc ..) as your primary source of information for your application . You can mix and match too (which is a typical case for almost every application) but that’s beyond the scope of this post . I am also assuming that you have fair knowledge of keys and indexes in databases.( If not, please don’t write anything about it in the comments section).

A database schema is the skeleton structure that represents the logical view of the entire database. It can simply be called as a metadata that describes the relationships between objects and information in a database.

In order to develop a good Relational DB schema, you only need to know 2 things .

1. Type of relationship between entities

2 . Normalisation in DB (1NF , 2NF , 3NF)

Types of relationship :

Type of relationship between entities will tell you about the number of tables you are required to make in your DB . A DB can have multiple tables . There are three types of relationship in general . Lets look at it closely —

One to One

A. In a one-to-one relationship, one record/row in a table is associated with one and only one record/row in another table.

B. Both tables will have same Primary Key that will be used to identify the record/row . We will get the complete data from both the tables by performing a join on this primary key .

C. Example : User table (split it into primary details and secondary details of a user )

Table 1: (Primary Info): User_ID (primary key), First name , Last name

Table 2: (Secondary Info): User_ID(primary key), address,email,contact etc..

Note: You can argue about putting all the data in same table with just 1 primary key but Trust me, it will not be an efficient and a good db design with million values.

One to Many

A. In one-to-many relationship, each record in Table A may have many linked records/row in Table B, but each record/row in Table B may have only one corresponding record/row in Table A.

B. In order to get data, simply put the primary key of table 1 as the foreign key of the 2nd table. You need minimum 2 table in order to design a good 1 to many relationship.

C. Example : Storing information of all Political Party Candidates. In this, Many Candidates belong to the same party . They cannot represent multiple parties at same time .

Table 1: (Political Party Info): Political_Party_ID (Primary key) , Party_Name , Political_Party_Head

Table 2: (Party Candidates Info): Candidate_ID (Primary key), Candidate_Name , Political_Party_ID(Foreign key as reference from Table 1)

Many To Many

A. A many to many relationship occurs when multiple records in a table are associated with multiple records in another table.

B. Relational database systems usually don’t allow you to implement a direct many-to-many relationship between two tables. So, you are required to create a third table for this relationship . This is the most common relationship that you will see in almost every application . The 3rd table is called a link table or a junction table. You can query on the third table directly in order to get data for analysis purposes .

C. Example: Students enrolled in different courses in High School. Many/Multiple students can enrol in many/Multiple courses and many/Multiple courses can be opted by multiple/Many Students.

Table 1 : (Students Info ) : Student_ID (Primary Key) , Student_Name etc.

Table 2 : (Courses Table): Course_ID (Primary key) , Course_Name, Cost etc.

Table 3 : (Enrollments Table): [ Student_ID (Foreign Key as Reference from Students Info ),Course_ID (Foreign Key as reference from Courses Table) ] —{ Composite Key } , Enrollment_ID etc ..

Note: Here, Table 3 will have a composite key which is a mix of foreign keys from table 1 and table 2 and this composite key will act as a primary key of this junction / Link table .

Normalization

It is the process of minimizing redundancy from a relation or set of relations in DB .

1 NF — There has to be a key that uniquely identifies a record/ row . This is generally a primary key or composite key . (concept of primary key )

2 NF — should be in 1 NF and every non-key column should be fully dependent on the primary key. ( concept of foreign key )

3 NF — it should be in 2-NF and Non key columns are independent of each other . if they do depend, then you would probabily have to split the table.

Writing Raw SQL Query

Lets look at some basic patterns to query a database. Remember — Result of a query is always a table .

Pattern 1

SELECT * FROM TABLE_NAME WHERE CONDITION/RESTRICTION : Here, * means all columns and condition can be used to match the value in a database row . * can be change into the column values required to get from table .

Ex: SELECT FIRST_NAME , LAST_NAME FROM USERS WHERE USER_ID =1

Ex: SELECT COUNT(*) FROM USERS_TABLE WHERE PURCHASE_DATE > 12–12–2018 AND < 12–1–2019

Ex: SELECT NAME FROM USERS_TABLE WHERE NOT( AGE = 20 ) AND NOT(FIRST_NAME = ‘KARAN’)

Ex: INSERT INTO USERS_TABLE VALUES (‘KARAN ’,’JAGOTA’ )

There are some other functions/clauses that you can use in this pattern. It includes — limit value offset value or group by col_name or order by column_name desc or count(*) as total or having col_name = value (having happens after the ‘WHERE’ whereas group_by or order_by are done before ‘WHERE’) .

Pattern 2

SELECT OBJECT_OF_TABLE_1.COL , OBJECT_OF_TABLE_2.COL FROM TABLE_1 JOIN TABLE_2 ON OBJECT_OF_TABLE_1.COMMON_KEY = OBJECT_OF_TABLE_2.COMMON_KEY

Here, you create an object(use any variable name. like — i am using U and P in the example for users and orders table) for both the tables , select the column name that you want and join them(inner join / left join/right join) by table name on their common key (primary key of one table with foreign key of the other table)

Ex: SELECT U.FIRST_NAME , P.PURCHASE FROM USERS_TABLE JOIN ORDERS_TABLE ON U.CUSTOMER_ID = P.CUSTOMER_ID

Pattern 3

SELECT AVG(*) FROM (SUBQUERY) AS ALIAS

Here, you are simply writing a query within query .As we know that the result of a query is a table itself , it can be used to further query and find the result/data that is needed .

I hope you liked my post. Please, give it a clap !! and if you are new to programming, you can also check out my post on pythonic way of doing things and JavaScript Essentials by clicking on the link below .

Don’t forget to clap, share and follow.