Changes between Version 3 and Version 4 of DatabaseCreation


Ignore:
Timestamp:
04/25/26 19:24:47 (7 days ago)
Author:
231133
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseCreation

    v3 v4  
    1 = DDL =
     1= DDL/Seeder/Views =
    22
    33== 1. Вовед ==
    … …  
    55DDL-от беше генериран од страна на Visual Paradigm, испробан локално и дополнет со constraints. Потоа, направивме pg_dump и го импортиравме на нашата remote база за предметот.
    66
    7 == 2. Поважни делови од кодот ==
     7== 2. DDL ==
     8[[html(<a href="https://develop.finki.ukim.mk/projects/eventflow/attachment/wiki/DatabaseCreation/ddl.sql">ddl.sql</a>)]]
    89
    9 === Create table
     10== 3. Seeder ==
     11[[html(<a href="https://develop.finki.ukim.mk/projects/eventflow/attachment/wiki/DatabaseCreation/seeder.sql">ddl.sql</a>)]]
    1012
    11 {{{
    12 CREATE TABLE user_app (
    13     user_id bigint NOT NULL,
    14     username character varying(20) NOT NULL,
    15     email character varying(50) NOT NULL,
    16     contact_phone character varying(50),
    17     CONSTRAINT chk_email_format CHECK (((email)::text ~~ '%@%.%'::text))
    18 );
    19 }}}
    20 
    21 {{{
    22 CREATE TABLE order_cart (
    23     user_id bigint NOT NULL,
    24     discount_id integer,
    25     status_id integer NOT NULL,
    26     order_id bigint NOT NULL,
    27     created_at timestamp without time zone NOT NULL,
    28     total_price numeric(12,2) NOT NULL,
    29     CONSTRAINT chk_order_total CHECK ((total_price >= (0)::numeric))
    30 );
    31 }}}
    32 
    33 {{{
    34 CREATE TABLE attribute (
    35     name text NOT NULL,
    36     attribute_id integer NOT NULL,
    37     data_type character varying(20) NOT NULL,
    38     description text,
    39     is_required boolean NOT NULL
    40 );
    41 }}}
    42 
    43 {{{
    44 CREATE TABLE payment (
    45     order_id bigint NOT NULL,
    46     method_id integer NOT NULL,
    47     payment_id integer NOT NULL,
    48     amount_paid numeric(12,2),
    49     processed_at timestamp without time zone NOT NULL,
    50     transaction_id character varying(255) NOT NULL,
    51     is_refunded boolean,
    52     CONSTRAINT chk_payment_amount CHECK ((amount_paid >= (0)::numeric))
    53 );
    54 }}}
    55 
    56 === Constraints
    57 
    58 {{{
    59 ALTER TABLE ONLY waitlist_entry
    60     ADD CONSTRAINT fk_waitlist_user FOREIGN KEY (user_id) REFERENCES public.user_app(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
    61 }}}
    62 
    63 {{{
    64 ALTER TABLE ONLY ticket
    65     ADD CONSTRAINT fk_ticket_seat FOREIGN KEY (seat_id) REFERENCES public.seat(seat_id) ON UPDATE CASCADE ON DELETE SET NULL;
    66 }}}
    67 
    68 {{{
    69 ALTER TABLE ONLY payment_method
    70     ADD CONSTRAINT payment_method_method_name_key UNIQUE (method_name);
    71 }}}
    72 
    73 == 3. Резултат ==
    74 Моментално имаме табели со точни типови на атрибути и со сите потребни (за сега) ограничувања.
     13== 4. Views ==
     14[[html(<a href="https://develop.finki.ukim.mk/projects/eventflow/attachment/wiki/DatabaseCreation/views.sql">views.sql</a>)]]