Relational databases for the longest time work best with relationships. The concept of relationships is widely used in almost all big relational DB. It aids in achieving normalization and also efficiency in any relational database. Without proper guidance on the same, relationships can be headache to any beginner on relational database. They are very simple to understand, but then with out good guidance you might take too long to understand what is happening. That's why I have decided to take you through this simple tutorial on database relatonships.

TOOLS REQUIRED

In this guide, I will be using postgresql. Postgres is one of the databases widely used and having a grasp on how it works will so much add you skills which can later on help you in your career. I will give a guide on how to have it installed in you machine:

1. If you don't have postgresql, go to link and choose the set up that will be suitable to your machine.
2. Follow the steps to set it up on you local machine and make sure you have pgadmin installed.
3. After successfully installing everything, open your pgadmin.

DATABASE DESIGN

I will give a guide on creating a simple ecommerce database design. It will have 4 table: products table, customers table, area table and orders table.

Create Database: We will start with creating a database:

CREATE DATABASE SMARTWEAR

The line above will create the database for us. I view a database as a book, and the different tables as pages. Lets now have pages for our book!

Create Tables: I will start with products table:

CREATE TABLE products (
          id SERIAL,
          product_name VARCHAR(255),
          product_cost INT,
          PRIMARY KEY (id)
)

This table will be called products, having 3 fields: id, product_name and product_cost. Each Field, we have given it a data type. On the last line, we've made id field the primary key. Primary key of a table is a unique identifier in a database, once a record is given the a certain id, no other record can be given the same id.

Lets insert record into the table:

INSERT INTO PRODUCTS(product_name, product_cost) VALUES
     ('NIKE Tshirt', 55),
     ('Denim', 75)

Area table:

CREATE TABLE AREA(
   id SERIAL,
   area_name VARCHAR(255),
   PRIMARY KEY(id)
)

In this area table, we have two fields, id and area_name. Lets insert records:

INSERT INTO AREA(area_name) VALUES
     ('New York'),
     ('DUBAI')

Customers table:

Customers table will be having a relationship with area table. A customer belongs to a certain area.

CREATE TABLE CUSTOMERS(
   id SERIAL,
   customer_name VARCHAR(255),
   phone VARCHAR(255),
   age INT,
   area_id INT,

   PRIMARY KEY(id),
   FOREIGN KEY (area_id) REFERENCES AREA(id) ON DELETE CASCADE
)

Above is a script to create customers table, with 5 fields. I wish to insist more on the area_id field, which is a foreign key from AREA table. I have added "ON DELETE CASCADE" to ensure whenever an area has been deleted, the record having that area on customers table will also be deleted.

INSERT INTO customers(customer_name, phone_number, age, area_id) values
     ('Rashid Abdi', '+1 345677', 45, 1)

Orders table:

CREATE TABLE orders(
    id SERIAL,
    product_id INT,
    customer_id INT,
    CREATED_AT DATE,
    STATUS VARCHAR,
    QUALITY INT,
    product_size VARCHAR,

    PRIMARY KEY(id),
    FOREIGN KEY(product_id) REFERENCES Products(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
)

Above script, create a simple orders table. The interesting part is on relationships, this table have relationship with customers table and products table.

Assignment: Create insert for orders table.

Thank you!