hv2-schema/initial/schema.sql

create sequence account_s
    start with 1
    increment by 1;

create table account_t (
    id integer not null default nextval('account_s'),
    description varchar(128) not null,
    constraint account_t_pk primary key (id)
);

create sequence tenant_s
    start with 1
    increment by 1;

create table tenant_t (
    id integer not null default nextval('tenant_s'),
    salutation varchar(128) default null,
    firstname varchar(128) default null,
    lastname varchar(128) not null,
    address1 varchar(128) default null,
    address2 varchar(128) default null,
    address3 varchar(128) default null,
    zip varchar(10) default null,
    city varchar(128) default null,
    phone1 varchar(64) default null,
    phone2 varchar(64) default null,
    iban varchar(34) default null,
    account integer not null,
    constraint tenant_t_pk primary key (id),
    constraint tenant_t_account_uk unique (id),
    constraint tenant_t_account_fk foreign key (account) references account_t (id)
);

create sequence premise_s
    start with 1
    increment by 1;

create table premise_t (
    id integer not null default nextval('premise_s'),
    street varchar(128) not null,
    zip varchar(10) not null,
    city varchar(128) not null,
    constraint premise_t_pk primary key (id)
);

create sequence flat_s
    start with 1
    increment by 1;

create table flat_t (
    id integer not null default nextval('flat_s'),
    description varchar(32) default null,
    premise integer,
    area numeric(10,2) not null,
    flat_no integer default null,
    constraint flat_t_pk primary key (id),
    constraint flat_t_premise_fk foreign key (premise) references premise_t (id)
);

create sequence overhead_advance_s
    start with 1
    increment by 1;

create table overhead_advance_t (
    id integer not null default nextval('overhead_advance_s'),
    description varchar(128) default null,
    amount numeric(10, 4) not null,
    startdate date default null,
    enddate date default null,
    constraint overhead_advance_t_pk primary key (id)
);

create sequence overhead_advance_flat_mapping_s
    start with 1
    increment by 1;

create table overhead_advance_flat_mapping_t (
    id integer not null default nextval('overhead_advance_flat_mapping_s'),
    overhead_advance integer not null,
    flat integer not null,
    constraint overhead_advance_flat_mapping_t_pk primary key (id),
    constraint overhead_advance_flat_mapping_t_overhead_advance_fk foreign key (overhead_advance) references overhead_advance_t (id),
    constraint overhead_advance_flat_mapping_t_flat_fk foreign key (flat) references flat_t (id)
);

create sequence parking_s
    start with 1
    increment by 1;

create table parking_t (
    id integer not null default nextval('parking_s'),
    description varchar(32) default null,
    premise integer,
    constraint parking_t_pk primary key (id),
    constraint parking_t_premise_fk foreign key (premise) references premise_t (id)
);

create sequence commercial_premise_s
    start with 1
    increment by 1;

create table commercial_premise_t (
    id integer not null default nextval('commercial_premise_s'),
    description varchar(32) default null,
    premise integer,
    constraint commercial_premise_t_pk primary key (id),
    constraint commercial_premise_t_premise_fk foreign key (premise) references premise_t (id)
);

create sequence tenancy_s 
    start with 1
    increment by 1;

create table tenancy_t (
    id integer not null default nextval('tenancy_s'),
    description varchar(128) default null,
    tenant integer not null,
    flat integer default null,
    parking integer default null,
    commercial_premise integer default null,
    startdate date not null,
    enddate date default null,
    constraint tenancy_t_pk primary key (id),
    constraint tenancy_t_tenant_fk foreign key (tenant) references tenant_t (id),
    constraint tenancy_t_flat_fk foreign key (flat) references flat_t (id) match simple,
    constraint tenancy_t_parking_fk foreign key (parking) references parking_t (id) match simple,
    constraint tenancy_t_commercial_premise_fk foreign key (commercial_premise) references commercial_premise_t (id) match simple,
    constraint tenancy_t_only_one_object check (
        (flat is not null and parking is null and commercial_premise is null) or
        (flat is null and parking is not null and commercial_premise is null) or
        (flat is null and parking is null and commercial_premise is not null)
    )
);

create type fee_type_e as ENUM ('per_area', 'total');

create sequence fee_s 
    start with 1
    increment by 1;

create table fee_t (
    id integer not null default nextval('fee_s'),
    description varchar(128) default null,
    amount numeric(10, 2) not null,
    fee_type fee_type_e not null,
    startdate date default null,
    enddate date default null,
    constraint fee_t_pk primary key (id)
);

create sequence tenancy_fee_mapping_s
    start with 1
    increment by 1;

create table tenancy_fee_mapping_t (
    id integer not null default nextval('tenancy_fee_mapping_s'),
    tenancy integer not null,
    fee integer not null,
    constraint tenancy_fee_mapping_t_pk primary key (id),
    constraint tenancy_fee_mapping_t_tenancy_fk foreign key (tenancy) references tenancy_t (id),
    constraint tenancy_fee_mapping_t_fee_fk foreign key (fee) references fee_t (id)
);

create sequence account_entry_s
    start with 1
    increment by 1;

create table account_entry_t (
    id integer not null default nextval('account_entry_s'),
    account integer not null,
    created_at date not null default now(),
    amount numeric(10, 2) not null,
    description varchar(32) not null,
    constraint account_entry_t_pk primary key (id),
    constraint account_entry_t_account_fk foreign key (account) references account_t (id)
);