Changes between Version 2 and Version 3 of RelationalDesign


Ignore:
Timestamp:
09/30/25 12:36:41 (2 weeks ago)
Author:
211561
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • RelationalDesign

    v2 v3  
    22
    33{{{
     4DROP TABLE IF EXISTS BATCHES;
     5DROP TABLE IF EXISTS CLIENTS;
     6DROP TABLE IF EXISTS ORDERS;
    47DROP TABLE IF EXISTS ORDER_BATCHES;
    58DROP TABLE IF EXISTS PAYMENTS;
    6 DROP TABLE IF EXISTS ORDERS;
    7 DROP TABLE IF EXISTS BATCHES;
     9DROP TABLE IF EXISTS PRODUCERS;
    810DROP TABLE IF EXISTS PRODUCTS;
    9 DROP TABLE IF EXISTS PRODUCERS;
    10 DROP TABLE IF EXISTS CLIENTS;
     11DROP TABLE IF EXISTS TRANSPORTS;
    1112DROP TABLE IF EXISTS USERS;
    12 DROP TYPE IF EXISTS currency_enum;
    13 DROP TYPE IF EXISTS payment_status_enum;
    14 DROP TYPE IF EXISTS boolean_enum;
    1513
    1614CREATE TABLE USERS (
    17   id UUID PRIMARY KEY,
    18   name VARCHAR(200) NOT NULL,
    19   email VARCHAR(255) UNIQUE NOT NULL,
     15  id UUID PRIMARY KEY NOT NULL,
     16  name TEXT NOT NULL,
     17  email TEXT UNIQUE NOT NULL,
    2018  password TEXT NOT NULL,
    2119  is_admin BOOLEAN NOT NULL DEFAULT FALSE
    … …  
    2321
    2422CREATE 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
     23  id UUID PRIMARY KEY NOT NULL,
     24  name TEXT NOT NULL,
     25  country TEXT NOT NULL,
     26  registration_number TEXT NOT NULL,
     27  tax_code TEXT NOT NULL,
     28  contact_person TEXT NOT NULL,
     29  phone_number TEXT NOT NULL,
     30  billing_address TEXT NOT NULL,
     31  shipping_address TEXT NOT NULL
    3432);
    3533
    3634CREATE 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)
     35  id UUID PRIMARY KEY NOT NULL,
     36  name TEXT NOT NULL,
     37  address TEXT NOT NULL,
     38  country TEXT NOT NULL,
     39  phone_number TEXT NOT NULL,
     40  email TEXT NOT NULL
    4341);
    4442
    4543CREATE 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)
     44  id UUID PRIMARY KEY NOT NULL,
     45  name TEXT NOT NULL,
     46  description TEXT NOT NULL,
     47  hs_code TEXT NOT NULL,
     48  price FLOAT NOT NULL,
     49  unit_of_measure TEXT NOT NULL,
     50  producer_id UUID REFERENCES PRODUCERS(id)
    5351);
    5452
    5553CREATE 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
     54  id UUID PRIMARY KEY NOT NULL,
     55  batch_code TEXT NOT NULL,
     56  production_date DATE NOT NULL,
     57  expiration_date DATE NOT NULL,
     58  net_weight FLOAT NOT NULL,
     59  gross_weight FLOAT NOT NULL,
     60  units_per_batch INTEGER NOT NULL,
     61  product_id UUID REFERENCES PRODUCTS(id)
    6462);
    6563
    6664CREATE 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
     65  id UUID PRIMARY KEY NOT NULL,
     66  name TEXT NOT NULL,
     67  departure_point TEXT NOT NULL,
     68  arrival_point TEXT NOT NULL,
     69  estimated_departure_date DATE NOT NULL,
     70  estimated_arrival_date DATE NOT NULL,
     71  incoterm TEXT NOT NULL,
     72  insurance_conditions TEXT NOT NULL
    7573);
    7674
    7775CREATE TABLE ORDERS (
    78   id UUID PRIMARY KEY,
     76  id UUID PRIMARY KEY NOT NULL,
    7977  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
     78  status TEXT NOT NULL,
     79  estimated_delivery_date DATE NOT NULL,
     80  buyer_id UUID REFERENCES CLIENTS(id),
     81  receiver_id UUID REFERENCES CLIENTS(id),
     82  transport_id UUID REFERENCES TRANSPORTS(id)
    8583);
    8684
    8785CREATE TABLE PAYMENTS (
    88   id UUID PRIMARY KEY,
    89   order_id UUID UNIQUE REFERENCES ORDERS(id) ON DELETE CASCADE,
     86  id UUID PRIMARY KEY NOT NULL,
    9087  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)
     88  currency TEXT NOT NULL,
     89  due_date DATE NOT NULL,
     90  exchange_rate FLOAT NOT NULL,
     91  payment_date DATE NOT NULL,
     92  payment_method TEXT NOT NULL,
     93  payment_status TEXT NOT NULL,
     94  order_id UUID REFERENCES ORDERS(id)
    9795);
    9896
    9997CREATE 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,
     98  id UUID PRIMARY KEY NOT NULL,
    10399  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()
     100  price_per_unit FLOAT NOT NULL,
     101  total_price FLOAT NOT NULL,
     102  created_at TIMESTAMP WITH TIME ZONE,
     103  updated_at TIMESTAMP WITH TIME ZONE,
     104  order_id UUID REFERENCES ORDERS(id),
     105  batch_id UUID REFERENCES BATCHES(id)
    108106);
    109107}}}
    … …  
    112110
    113111{{{
    114 INSERT 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);
     112INSERT INTO users (id, name, email, password, is_admin) VALUES
     113  (gen_random_uuid(), 'Alice Admin', 'alice@example.com', 'password_hash_1', TRUE),
     114  (gen_random_uuid(), 'Bob Buyer', 'bob@example.com', 'password_hash_2', FALSE),
     115  (gen_random_uuid(), 'Eve Employee', 'eve@example.com', 'password_hash_3', FALSE);
    117116
    118 INSERT 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');
     117INSERT INTO producers (id, name, address, country, phone_number, email) VALUES
     118  (gen_random_uuid(), 'Fresh Farms Ltd', '123 Farm Rd', 'US', '+1-555-0100', 'contact@freshfarms.example'),
     119  (gen_random_uuid(), 'Oceanic Produce', '456 Sea Ave', 'ES', '+34-600-123-456', 'sales@oceanic.example');
    121120
    122 INSERT 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');
     121WITH p AS (SELECT id FROM producers ORDER BY name LIMIT 1)
     122INSERT INTO products (id, name, description, hs_code, price, unit_of_measure, producer_id)
     123SELECT gen_random_uuid(), 'Apples', 'Red apples - grade A', '080810', 1.25, 'kg', id FROM p;
    125124
    126 INSERT 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');
     125WITH p2 AS (SELECT id FROM producers ORDER BY name DESC LIMIT 1)
     126INSERT INTO products (id, name, description, hs_code, price, unit_of_measure, producer_id)
     127SELECT gen_random_uuid(), 'Frozen Shrimp', 'IQF shrimp 20/30', '030617', 8.50, 'kg', id FROM p2;
    129128
    130 INSERT 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);
     129INSERT INTO batches (id, batch_code, production_date, expiration_date, net_weight, gross_weight, units_per_batch, product_id)
     130SELECT gen_random_uuid(), 'BATCH-A1', CURRENT_DATE - INTERVAL '30 days', CURRENT_DATE + INTERVAL '335 days', 100.0, 105.0, 1000, id FROM products WHERE name='Apples' LIMIT 1;
    133131
    134 INSERT 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');
     132INSERT INTO batches (id, batch_code, production_date, expiration_date, net_weight, gross_weight, units_per_batch, product_id)
     133SELECT gen_random_uuid(), 'BATCH-S1', CURRENT_DATE - INTERVAL '10 days', CURRENT_DATE + INTERVAL '355 days', 500.0, 510.0, 200, id FROM products WHERE name='Frozen Shrimp' LIMIT 1;
    137134
    138 INSERT 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');
     135INSERT INTO clients (id, name, country, registration_number, tax_code, contact_person, phone_number, billing_address, shipping_address) VALUES
     136  (gen_random_uuid(), 'Global Retailer', 'US', 'REG-1001', 'TAX-1001', 'John Buyer', '+1-555-0200', '100 Market St', '200 Warehouse Rd'),
     137  (gen_random_uuid(), 'Local Bistro', 'ES', 'REG-2002', 'TAX-2002', 'Maria Chef', '+34-600-234-567', 'Calle 12', 'Calle 13');
    141138
    142 INSERT 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');
     139INSERT INTO transports (id, name, departure_point, arrival_point, estimated_departure_date, estimated_arrival_date, incoterm, insurance_conditions) VALUES
     140  (gen_random_uuid(), 'Ocean Freight 1', 'Valencia Port', 'New York Port', CURRENT_DATE + INTERVAL '7 days', CURRENT_DATE + INTERVAL '21 days', 'FOB', 'Standard cargo insurance'),
     141  (gen_random_uuid(), 'Truck Line A', 'Warehouse A', 'Retail Hub', CURRENT_DATE + INTERVAL '1 day', CURRENT_DATE + INTERVAL '2 days', 'DAP', 'No additional insurance');
    145142
    146 INSERT 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());
     143WITH buyer AS (SELECT id FROM clients ORDER BY name LIMIT 1),
     144     receiver AS (SELECT id FROM clients ORDER BY name DESC LIMIT 1),
     145     transport AS (SELECT id FROM transports ORDER BY name LIMIT 1)
     146INSERT INTO orders (id, date, status, estimated_delivery_date, buyer_id, receiver_id, transport_id)
     147SELECT gen_random_uuid(), CURRENT_DATE, 'created', CURRENT_DATE + INTERVAL '14 days', buyer.id, receiver.id, transport.id
     148FROM buyer, receiver, transport;
     149
     150WITH o AS (SELECT id FROM orders LIMIT 1)
     151INSERT INTO payments (id, amount, currency, due_date, exchange_rate, payment_date, payment_method, payment_status, order_id)
     152SELECT gen_random_uuid(), 1250.00, 'USD', CURRENT_DATE + INTERVAL '30 days', 1.0, NULL, 'bank_transfer', 'pending', id FROM o;
     153
     154WITH o AS (SELECT id FROM orders LIMIT 1),
     155     b1 AS (SELECT id FROM batches WHERE batch_code='BATCH-A1' LIMIT 1),
     156     b2 AS (SELECT id FROM batches WHERE batch_code='BATCH-S1' LIMIT 1)
     157INSERT INTO order_batches (id, quantity, price_per_unit, total_price, created_at, updated_at, order_id, batch_id)
     158SELECT gen_random_uuid(), 100, 1.25, 125.00, now(), NULL, o.id, b1.id FROM o, b1
     159UNION ALL
     160SELECT gen_random_uuid(), 50, 8.50, 425.00, now(), NULL, o.id, b2.id FROM o, b2;
    149161}}}
     162
     163= DBeaver ER Дијаграм
     164
     165[[Image(DBeaver_New_Diagram.png)]]