SQL concepts to define entities relationship

SQL concepts to define entities relationship

In SQL database is divided into tables and tables maintain relationship with each other. This relationship help us to minimize data duplicity and even help to connect one table to another logically and even help to utilize them efficiently.

What is a ONE to MANY Relationship?

We will try to explain this concept with respect to our current project.
  • A user can have many todos.
  • todos cannot have multiple users.

Relationship diagram:

Users and Todos table structure for this application:


We need to modify these table a little bit to create relationship between these two tables. To make it possible we need to introduce new column to Todos table which will be Primary key of Users table as Foreign Key of Todos table as named owner[FK].

Logical representation through diagram:

Sample data for testing the scenario:



What is a foreign key?

  • A foreign key (FK) is a column within a relational database table that provides a link between two separate tables.
  • A foreign key references a primary key of another table.
  • Most relational databases need foreign keys to be able to link tables together to present data
  • As you can see it in the current scenario creating one to many relation between users and todos tables.

Foreign Keys

It will help to create joints between two or more tables. As you can see that a user id is attached to todos table for information and in authentication we will authorize the user so this user id will be part of JWT(json WEB Token).
  • Each API request, a user will have their ID attached
    • If we have the user ID attached to each request, we can use the ID to find their todos.

Where SQL queries:

select * from todos where owner = 1;
select * from todos where owner = 2;
Will help to filter the data.





Comments

Post a Comment

Popular posts from this blog

CRUD Assignment

Image
 CRUD Assignment 1. Create a new API Endpoint that can fetch all books from a specific author using either Path Parameters or Query Parameters. Code and Result: As async is implemented by FastAPI itself hence ignored it. from fastapi import FastAPI app = FastAPI () BOOKS = [         { "title" : "Book One" , "author" : "Author A" , "category" : "Fiction" },         { "title" : "Book Two" , "author" : "Author B" , "category" : "Non-Fiction" },         { "title" : "Book Three" , "author" : "Author A" , "category" : "Biography" },         { "title" : "Book Four" , "author" : "Author A" , "category" : "Historical" },         { "title" : "Book Five" , "author" : "Author A" , "category" : "Romanc...
Read more

Python Variables

Python Variables   Variables: They are containers for holding data values. It will help you store the value in a memory location. example: cost = 10 print(cost) Now cost variable will direct you to get the information stored over the memory location which is holding the data 10. Variables help you to create formulas or help to reuse the data over different locations in a code. example: cost=10 tax_percent = 0.25 tax = cost * tax_percent price = cost I+ tax print(price) String datatype variable sample: username="Rock!rupesh" first_name="Rahul" print(username) print(first_name) print(username + " " + first_name) first_number=10 second_number=2 print(first_number) print(second_number) first_number=1 So you can modify the variable stored information according to your need. So as a summary variables are memory location which hold relevant information for your application.
Read more

API Request Method - Post Todos (create and save todo)

Image
 API Request Method  - Post Todos (create and save todo) Code to make is properly: TodoValidator.py # Valdator class for Todos table entry for ensuring right data entry. from pydantic import BaseModel , Field # We have added id as part of validation # As it will be done by table and fastAPI by itself # As id is primary key and have incremental nature. # Even sqlalchemy will help us in doing it automatically. class TodoRequest ( BaseModel ):     title : str = Field ( min_length = 3 )     description : str = Field ( min_length = 3 , max_length = 100 )     priority : int = Field ( gt = 0 , lt = 6 )     complete : bool main.py: # This file it to perform all the activity which we required for Todo application. # Depends means dependency injection from fastapi import FastAPI , Depends , HTTPException , Path import models from models import Todos from database import engine , SessionLocal from typing import Annotated from...
Read more