Changes between Version 1 and Version 2 of RelationalDesign


Ignore:
Timestamp:
09/30/25 11:30:05 (2 weeks ago)
Author:
211561
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • RelationalDesign

    v1 v2  
    1 = Логички и физички дизајн
     1= DDL скрипта
    22
    3 == DDL скрипта
     3{{{
     4DROP TABLE IF EXISTS ORDER_BATCHES;
     5DROP TABLE IF EXISTS PAYMENTS;
     6DROP TABLE IF EXISTS ORDERS;
     7DROP TABLE IF EXISTS BATCHES;
     8DROP TABLE IF EXISTS PRODUCTS;
     9DROP TABLE IF EXISTS PRODUCERS;
     10DROP TABLE IF EXISTS CLIENTS;
     11DROP TABLE IF EXISTS USERS;
     12DROP TYPE IF EXISTS currency_enum;
     13DROP TYPE IF EXISTS payment_status_enum;
     14DROP TYPE IF EXISTS boolean_enum;
    415
    5 {{{#!sql
    6 -- DDL for Deleting Tables
    7 DROP TABLE IF EXISTS ORDER_PRODUCT;
    8 DROP TABLE IF EXISTS INVOICE;
    9 DROP TABLE IF EXISTS PAYMENT;
    10 DROP TABLE IF EXISTS ORDER;
    11 DROP TABLE IF EXISTS TRANSPORT;
    12 DROP TABLE IF EXISTS RECEIVER;
    13 DROP TABLE IF EXISTS BUYER;
    14 DROP TABLE IF EXISTS PRODUCT;
    15 DROP TABLE IF EXISTS PRODUCER;
    16 
    17 -- DDL for Creating Tables
    18 
    19 -- Creating the PRODUCER table
    20 CREATE TABLE PRODUCER (
    21     producer_id SERIAL PRIMARY KEY,
    22     company_name VARCHAR(100),
    23     country_of_origin VARCHAR(30),
    24     tax_id VARCHAR(20),
    25     registration_number VARCHAR(30),
    26     bank_account_number VARCHAR(20),
    27     bank_name VARCHAR(100),
    28     swift_number VARCHAR(20),
    29     iban VARCHAR(34),
    30     export_license_number VARCHAR(20)
     16CREATE TABLE USERS (
     17  id UUID PRIMARY KEY,
     18  name VARCHAR(200) NOT NULL,
     19  email VARCHAR(255) UNIQUE NOT NULL,
     20  password TEXT NOT NULL,
     21  is_admin BOOLEAN NOT NULL DEFAULT FALSE
    3122);
    3223
    33 -- Creating the PRODUCT table
    34 CREATE TABLE PRODUCT (
    35     product_id SERIAL PRIMARY KEY,
    36     name VARCHAR(50),
    37     hs_code VARCHAR(10),
    38     description TEXT,
    39     unit_of_measure VARCHAR(10),
    40     price DECIMAL(10, 2),
    41     producer_id INT REFERENCES PRODUCER(producer_id)
     24CREATE TABLE CLIENTS (
     25  id UUID PRIMARY KEY,
     26  name VARCHAR(255) NOT NULL,
     27  country VARCHAR(100),
     28  registration_number VARCHAR(100),
     29  tax_code VARCHAR(100),
     30  contact_person VARCHAR(200),
     31  phone_number VARCHAR(50),
     32  billing_address TEXT,
     33  shipping_address TEXT
    4234);
    4335
    44 -- Creating the BUYER table
    45 CREATE TABLE BUYER (
    46     buyer_id SERIAL PRIMARY KEY,
    47     company_name VARCHAR(100),
    48     billing_address VARCHAR(200),
    49     country VARCHAR(50),
    50     tax_id VARCHAR(20),
    51     registration_number VARCHAR(30)
     36CREATE TABLE PRODUCERS (
     37  id UUID PRIMARY KEY,
     38  name VARCHAR(255) NOT NULL,
     39  address TEXT,
     40  country VARCHAR(100),
     41  phone_number VARCHAR(50),
     42  email VARCHAR(255)
    5243);
    5344
    54 -- Creating the RECEIVER table
    55 CREATE TABLE RECEIVER (
    56     receiver_id SERIAL PRIMARY KEY,
    57     company_name VARCHAR(100),
    58     shipping_address VARCHAR(200),
    59     country VARCHAR(50),
    60     phone_number VARCHAR(15),
    61     contact_person VARCHAR(100)
     45CREATE TABLE PRODUCTS (
     46  id UUID PRIMARY KEY,
     47  name VARCHAR(255) NOT NULL,
     48  description TEXT,
     49  hs_code VARCHAR(50),
     50  price NUMERIC(14,4) NOT NULL DEFAULT 0,
     51  producer_id UUID REFERENCES PRODUCERS(id) ON DELETE SET NULL,
     52  unit_of_measure VARCHAR(50)
    6253);
    6354
    64 -- Creating the TRANSPORT table
    65 CREATE TABLE TRANSPORT (
    66     transport_id SERIAL PRIMARY KEY,
    67     carrier_name VARCHAR(100),
    68     departure_point VARCHAR(50),
    69     arrival_point VARCHAR(50),
    70     estimated_departure DATE,
    71     estimated_arrival DATE,
    72     total_distance DECIMAL(10, 2),
    73     special_requirements TEXT,
    74     insurance_conditions VARCHAR(200),
    75     incoterm VARCHAR(20)
     55CREATE TABLE BATCHES (
     56  id UUID PRIMARY KEY,
     57  product_id UUID REFERENCES PRODUCTS(id) ON DELETE CASCADE,
     58  batch_code VARCHAR(100) NOT NULL,
     59  production_date DATE,
     60  expiration_date DATE,
     61  net_weight NUMERIC(12,4),
     62  gross_weight NUMERIC(12,4),
     63  units_per_batch INTEGER
    7664);
    7765
    78 -- Creating the ORDER table
    79 CREATE TABLE ORDER (
    80     order_id SERIAL PRIMARY KEY,
    81     order_date DATE,
    82     estimated_delivery_date DATE,
    83     status VARCHAR(20),
    84     employee_id INT,
    85     buyer_id INT REFERENCES BUYER(buyer_id),
    86     receiver_id INT REFERENCES RECEIVER(receiver_id),
    87     transport_id INT REFERENCES TRANSPORT(transport_id)
     66CREATE TABLE TRANSPORTS (
     67  id UUID PRIMARY KEY,
     68  name VARCHAR(255) NOT NULL,
     69  departure_point VARCHAR(255),
     70  arrival_point VARCHAR(255),
     71  estimated_departure_date DATE,
     72  estimated_arrival_date DATE,
     73  incoterm VARCHAR(50),
     74  insurance_conditions TEXT
    8875);
    8976
    90 -- Creating the PAYMENT table
    91 CREATE TABLE PAYMENT (
    92     payment_id SERIAL PRIMARY KEY,
    93     amount DECIMAL(10, 2),
    94     exchange_rate DECIMAL(10, 6),
    95     currency VARCHAR(3),
    96     payment_status VARCHAR(20),
    97     payment_method VARCHAR(20),
    98     due_date DATE,
    99     payment_date DATE,
    100     order_id INT REFERENCES ORDER(order_id)
     77CREATE TABLE ORDERS (
     78  id UUID PRIMARY KEY,
     79  date DATE NOT NULL,
     80  status VARCHAR(50),
     81  estimated_delivery_date DATE,
     82  buyer_id UUID REFERENCES CLIENTS(id) ON DELETE SET NULL,
     83  receiver_id UUID REFERENCES CLIENTS(id) ON DELETE SET NULL,
     84  transport_id UUID REFERENCES TRANSPORTS(id) ON DELETE SET NULL
    10185);
    10286
    103 -- Creating the INVOICE table
    104 CREATE TABLE INVOICE (
    105     invoice_key SERIAL PRIMARY KEY,
    106     invoice_date DATE,
    107     status VARCHAR(20),
    108     total_amount DECIMAL(10, 2),
    109     order_id INT REFERENCES ORDER(order_id)
     87CREATE TABLE PAYMENTS (
     88  id UUID PRIMARY KEY,
     89  order_id UUID UNIQUE REFERENCES ORDERS(id) ON DELETE CASCADE,
     90  amount NUMERIC(18,4) NOT NULL,
     91  currency VARCHAR(10) DEFAULT 'USD',
     92  due_date DATE,
     93  exchange_rate NUMERIC(18,8) DEFAULT 1.0,
     94  payment_date DATE,
     95  payment_method VARCHAR(100),
     96  payment_status VARCHAR(50)
    11097);
    11198
    112 -- Creating the ORDER_PRODUCT table (Many-to-Many relationship between ORDER and PRODUCT)
    113 CREATE TABLE ORDER_PRODUCT (
    114     order_id INT REFERENCES ORDER(order_id),
    115     product_id INT REFERENCES PRODUCT(product_id),
    116     PRIMARY KEY (order_id, product_id)
     99CREATE TABLE ORDER_BATCHES (
     100  id UUID PRIMARY KEY,
     101  order_id UUID REFERENCES ORDERS(id) ON DELETE CASCADE,
     102  batch_id UUID REFERENCES BATCHES(id) ON DELETE RESTRICT,
     103  quantity INTEGER NOT NULL,
     104  price_per_unit NUMERIC(14,4) NOT NULL,
     105  total_price NUMERIC(18,4) NOT NULL,
     106  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
     107  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
    117108);
    118109}}}
    119110
    120 == DML скрипта
     111= DML скрипта
    121112
    122 {{{#!sql
    123 INSERT INTO PRODUCER (company_name, country_of_origin, tax_id, registration_number, bank_account_number, bank_name, swift_number, iban, export_license_number) VALUES
    124 ('Producer A', 'Country X', 'TX123', 'RN123', 'BAN123', 'Bank A', 'SW123', 'IBAN123', 'EL123');
     113{{{
     114INSERT INTO USERS (id, name, email, password, is_admin) VALUES
     115('11111111-1111-1111-1111-111111111111', 'Alice Admin', 'alice@example.com', 'hashed_password_1', TRUE),
     116('22222222-2222-2222-2222-222222222222', 'Bob Buyer', 'bob@example.com', 'hashed_password_2', FALSE);
    125117
    126 INSERT INTO PRODUCT (name, hs_code, description, unit_of_measure, price, producer_id) VALUES
    127 ('Product 1', 'HS101', 'Description 1', 'Unit 1', 100.00, 1),
    128 ('Product 2', 'HS102', 'Description 2', 'Unit 2', 150.00, 1);
     118INSERT INTO CLIENTS (id, name, country, registration_number, tax_code, contact_person, phone_number, billing_address, shipping_address) VALUES
     119('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'Acme Imports', 'Italy', 'REG-IT-0001', 'TAXIT0001', 'Giulia Rossi', '+39 06 1234567', 'Via Roma 1, 00100 Roma, Italy', 'Port of Naples, Dock 5'),
     120('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'Global Retailer', 'Germany', 'DE-REG-2020', 'DE-TAX-2020', 'Hans Müller', '+49 30 7654321', 'Hauptstrasse 10, 10115 Berlin, Germany', 'Warehouse 3, Berlin');
    129121
    130 INSERT INTO BUYER (company_name, billing_address, country, tax_id, registration_number) VALUES
    131 ('Buyer A', 'Address 1', 'Country Y', 'TX456', 'RN456');
     122INSERT INTO PRODUCERS (id, name, address, country, phone_number, email) VALUES
     123('33333333-3333-3333-3333-333333333333', 'Producer Co.', 'Industrial Park 5, Zona A', 'Spain', '+34 91 1234567', 'sales@producerco.example'),
     124('44444444-4444-4444-4444-444444444444', 'Farm Fresh Ltd.', 'Countryside Road 12', 'France', '+33 1 23456789', 'contact@farmfresh.example');
    132125
    133 INSERT INTO RECEIVER (company_name, shipping_address, country, phone_number, contact_person) VALUES
    134 ('Receiver A', 'Shipping Address 1', 'Country Z', '1234567890', 'Contact A');
     126INSERT INTO PRODUCTS (id, name, description, hs_code, price, producer_id, unit_of_measure) VALUES
     127('55555555-5555-5555-5555-555555555555', 'Organic Olive Oil', 'Extra virgin olive oil, cold pressed', '150900', 12.5000, '33333333-3333-3333-3333-333333333333', 'L'),
     128('66666666-6666-6666-6666-666666666666', 'Canned Tomatoes', 'San Marzano peeled tomatoes', '200210', 1.2000, '44444444-4444-4444-4444-444444444444', 'kg');
    135129
    136 INSERT INTO TRANSPORT (carrier_name, departure_point, arrival_point, estimated_departure, estimated_arrival, total_distance, special_requirements, insurance_conditions, incoterm) VALUES
    137 ('Transport A', 'Point A', 'Point B', '2022-01-01', '2022-01-02', 1000.00, 'None', 'Condition A', 'FOB');
     130INSERT INTO BATCHES (id, product_id, batch_code, production_date, expiration_date, net_weight, gross_weight, units_per_batch) VALUES
     131('77777777-7777-7777-7777-777777777777', '55555555-5555-5555-5555-555555555555', 'OO-202509-A', '2025-06-01', '2027-06-01', 1000.0000, 1020.0000, 1000),
     132('88888888-8888-8888-8888-888888888888', '66666666-6666-6666-6666-666666666666', 'CT-202509-01', '2025-07-15', '2028-07-15', 500.0000, 510.0000, 500);
    138133
    139 INSERT INTO ORDER (order_date, estimated_delivery_date, status, employee_id, buyer_id, receiver_id, transport_id) VALUES
    140 ('2022-01-01', '2022-02-01', 'Pending', 1, 1, 1, 1);
     134INSERT INTO TRANSPORTS (id, name, departure_point, arrival_point, estimated_departure_date, estimated_arrival_date, incoterm, insurance_conditions) VALUES
     135('99999999-9999-9999-9999-999999999999', 'Mediterranean Sea Freight', 'Port of Valencia', 'Port of Naples', '2025-09-20', '2025-09-25', 'FOB', 'Standard cargo insurance'),
     136('aaaaaaaa-0000-aaaa-0000-aaaaaaaa0000', 'Road Carrier EU', 'Berlin Warehouse', 'Acme Imports HQ', '2025-10-01', '2025-10-03', 'DAP', 'Carrier liability');
    141137
    142 INSERT INTO PAYMENT (amount, exchange_rate, currency, payment_status, payment_method, due_date, payment_date, order_id) VALUES
    143 (1000.00, 1.000000, 'USD', 'Completed', 'Credit Card', '2022-01-15', '2022-01-10', 1);
     138INSERT INTO ORDERS (id, date, status, estimated_delivery_date, buyer_id, receiver_id, transport_id) VALUES
     139('dddddddd-dddd-dddd-dddd-dddddddddddd', '2025-09-01', 'Pending', '2025-10-05', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', '99999999-9999-9999-9999-999999999999'),
     140('eeeeeeee-eeee-eeee-eeee-eeeeeeeeeeee', '2025-09-10', 'Confirmed', '2025-10-10', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'aaaaaaaa-0000-aaaa-0000-aaaaaaaa0000');
    144141
    145 INSERT INTO INVOICE (invoice_date, status, total_amount, order_id) VALUES
    146 ('2022-01-10', 'Issued', 1000.00, 1);
     142INSERT INTO PAYMENTS (id, order_id, amount, currency, due_date, exchange_rate, payment_date, payment_method, payment_status) VALUES
     143('ffffffff-ffff-ffff-ffff-ffffffffffff', 'dddddddd-dddd-dddd-dddd-dddddddddddd', 12500.0000, 'EUR', '2025-10-01', 1.00000000, NULL, 'Wire Transfer', 'Pending'),
     144('11111111-aaaa-1111-aaaa-11111111aaaa', 'eeeeeeee-eeee-eeee-eeee-eeeeeeeeeeee', 600.0000, 'EUR', '2025-10-05', 1.00000000, '2025-09-15', 'Credit Card', 'Completed');
    147145
    148 INSERT INTO ORDER_PRODUCT (order_id, product_id) VALUES
    149 (1, 1),
    150 (1, 2);
     146INSERT INTO ORDER_BATCHES (id, order_id, batch_id, quantity, price_per_unit, total_price, created_at, updated_at) VALUES
     147('22222222-2222-2222-2222-222222222222', 'dddddddd-dddd-dddd-dddd-dddddddddddd', '77777777-7777-7777-7777-777777777777', 200, 12.5000, 2500.0000, now(), now()),
     148('33333333-3333-3333-3333-333333333333', 'eeeeeeee-eeee-eeee-eeee-eeeeeeeeeeee', '88888888-8888-8888-8888-888888888888', 500, 1.2000, 600.0000, now(), now());
    151149}}}
    152 
    153 == DBeaver релациски дијаграм
    154 
    155 [[Image(DBeaver_Relational_Diagram.png)]]