177 lines
5.7 KiB
SQL
177 lines
5.7 KiB
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)
|
|
);
|