Structure
create table account
(
id int unsigned auto_increment
primary key,
creation_date date null,
name text null,
export_account varchar(255) null,
activated tinyint(1) unsigned default 1 not null,
category tinyint(1) unsigned default 0 null,
account_type tinyint(1) unsigned default 0 null,
owner_id int unsigned default 0 not null,
payment_allowed tinyint(1) default 0 null,
budget_id int unsigned null,
order_num int null,
accounting_id int unsigned null,
deactivated_date datetime null,
group_sales tinyint(1) default 0 not null
)
comment 'List of account';
create index idx_owner_id
on account (owner_id);
create table account_entry
(
id int auto_increment
primary key,
flow_id int null,
account_date datetime null,
account_id int null,
credit decimal(15, 5) default 0.00000 null,
debit decimal(15, 5) default 0.00000 null,
payment_type int unsigned null,
payment_description text null,
person_delivery_id int unsigned null,
comments text null,
validated int(1) default 0 null,
exported int(1) default 0 null,
registration_date datetime null,
budget_id int unsigned null,
product_id int unsigned null,
signature varchar(56) null,
signature_date bigint(13) unsigned null,
lettering int null,
lettering_date datetime null
)
comment 'List of account entries';
create index idx_account_date
on account_entry (account_date);
create index idx_flow_id
on account_entry (flow_id);
create index idx_signature_date
on account_entry (signature_date);
create table account_link
(
account_id int unsigned default 0 not null,
linked_account_id int unsigned default 0 not null,
primary key (account_id, linked_account_id)
);
create table account_type
(
id int unsigned auto_increment
primary key,
name varchar(255) null,
category tinyint(1) unsigned default 0 null,
activated tinyint(1) default 1 null,
order_num int null,
accounting_id int unsigned null,
export_account_pattern varchar(255) null
);
create table account_type_profile
(
account_type_id int unsigned default 0 not null,
profile_id bigint unsigned default 0 not null,
primary key (account_type_id, profile_id)
)
comment 'Linked account type and profile';
create table accounting
(
id int unsigned auto_increment
primary key,
name varchar(255) null,
unit_name varchar(255) null,
symbol varchar(255) null,
format tinyint(1) null,
decimal_precision int unsigned default 2 null
);
create table accounting_profile
(
accounting_id int unsigned default 0 not null,
profile_id bigint unsigned default 0 not null,
primary key (accounting_id, profile_id)
);
create table activity_training
(
activity_id int unsigned default 0 not null,
theme_id int unsigned default 0 not null,
student_id int(10) null,
level_reached int unsigned null,
comment varchar(255) null,
validated tinyint(1) default 0 not null,
checker_person_id int(10) null,
checking_date datetime null,
checking_sentence varchar(255) null,
primary key (activity_id, theme_id)
);
create table activity_type
(
id bigint unsigned default 0 not null
primary key,
name varchar(255) null,
order_num int null,
activated tinyint(1) default 1 not null,
color int unsigned null,
training tinyint(1) default 0 not null
)
comment 'list of flight type';
create table aircraft
(
id int unsigned default 0 not null
primary key,
ref_date datetime null,
ref_hours int null,
inspection_date datetime null,
inspection_time int null,
tolerance_time int(11) unsigned default 6000 null,
last_counter int null,
interval_visit int(11) unsigned default 30000 null,
time_alert1 int default 6000 null,
time_alert2 int default 0 null,
time_alert3 int default -3000 null,
day_alert1 smallint(3) default 15 null,
day_alert2 smallint(3) default 0 null,
day_alert3 smallint(3) default -8 null
);
create table aircraft_type
(
id int unsigned auto_increment
primary key,
flight_time_formula varchar(255) default '%DURATION' null,
counter_state tinyint(2) default -1 not null,
tolerance int default 0 null,
autonomy int default 5990 null,
true_air_speed int unsigned default 0 not null,
digit_counter_number tinyint(1) unsigned default 4 not null
)
comment 'Types of aircraft';
create table aircraft_type_allowed_status
(
aircraft_type_id int unsigned null,
place_num int unsigned null,
status_id int unsigned null
)
comment 'List of allowed functions for each aircraft type';
create table aircraft_type_mandatory_flight_type
(
aircraft_type_id int unsigned null,
activity_type_id bigint unsigned null
)
comment 'List of compulsory flight type for each aircraft type';
create table aircraft_type_uncomp_flight_type
(
aircraft_type_id int unsigned null,
activity_type_id bigint unsigned null
)
comment 'List of uncompatible flight type for each aircraft type';
create table aircraft_type_validity_type
(
aircraft_type_id int unsigned default 0 not null,
validity_type_id int unsigned default 0 not null,
check_num int unsigned default 0 not null,
primary key (aircraft_type_id, validity_type_id, check_num)
)
comment 'Types of qualif required for each aircraft type';
create table allocation_rule
(
id int unsigned auto_increment
primary key,
title varchar(255) not null,
account_id int(10) not null,
keyword varchar(255) null,
order_num int(10) not null,
amount tinyint(1) not null,
mandatory tinyint(1) default 0 not null
);
create table balance
(
account_id int unsigned not null,
balance_date_id int unsigned not null,
debit decimal(15, 5) default 0.00000 null,
credit decimal(15, 5) default 0.00000 null,
primary key (account_id, balance_date_id)
);
create table balance_date
(
id int unsigned auto_increment
primary key,
balance_date datetime null
);
create table booking
(
id int unsigned auto_increment
primary key,
booking_group_id int unsigned null,
start_date datetime null,
end_date datetime null
);
create table booking_account_entry
(
booking_id int unsigned default 0 not null,
account_entry_flow_id int unsigned default 0 not null,
primary key (booking_id, account_entry_flow_id)
);
create table booking_activity_type
(
booking_id int unsigned default 0 not null,
activity_type_id int unsigned default 0 not null,
primary key (booking_id, activity_type_id)
);
create table booking_changelog
(
id int unsigned auto_increment
primary key,
booking_id int unsigned not null,
person_id int unsigned not null,
changelog_date datetime not null,
booking_state text null
);
create table booking_person
(
booking_id int unsigned default 0 not null,
person_id int unsigned default 0 not null,
place_num int unsigned null,
status_id int unsigned null,
primary key (booking_id, person_id)
);
create table booking_resource
(
booking_id int unsigned default 0 not null,
resource_id int unsigned default 0 not null,
primary key (booking_id, resource_id)
);
create table budget
(
id int unsigned auto_increment
primary key,
group_id int unsigned default 0 not null,
name varchar(255) null,
order_num int null,
export_code varchar(255) null
);
create table budget_group
(
id int unsigned auto_increment
primary key,
name varchar(255) null,
order_num int null
);
create table business_field
(
id int unsigned auto_increment
primary key,
variable varchar(255) null,
label varchar(255) not null,
value_type varchar(40) not null,
category varchar(255) not null,
order_num int not null,
dsn varchar(20) default 'customer' null,
compulsory_fill tinyint(1) default 0 null,
linked_category varchar(255) null,
linked_business_field_id int unsigned null,
linked_field_name varchar(255) null,
max_display int(10) default -1 null,
formula text null,
default_value text null,
placeholder text null,
access_level_required tinyint default 0 not null,
constraint variable
unique (variable)
)
comment 'List of extra form field';
create table business_field_activity_type
(
business_field_id int unsigned default 0 not null,
activity_type_id bigint unsigned default 0 not null,
business_field_group_id int unsigned default 0 not null,
visibility_type tinyint(1) unsigned null,
primary key (business_field_id, activity_type_id)
);
create table business_field_content
(
category_id int unsigned not null,
business_field_id int unsigned default 0 not null,
person_id int unsigned default 0 not null,
content text null,
placeholder text null,
primary key (category_id, business_field_id, person_id)
)
comment 'Content of extra form field';
create table business_field_group
(
id int unsigned auto_increment
primary key,
label varchar(255) null,
order_num int null
);
create table criteria
(
id int auto_increment
primary key,
label varchar(255) not null,
predicate text not null,
order_num int null,
dsn varchar(20) default 'customer' null
);
create table criteria_profile
(
criteria_id int unsigned not null,
profile_id bigint unsigned not null,
primary key (criteria_id, profile_id)
);
create table customer_bill_entry
(
id int unsigned auto_increment
primary key,
flow_id int unsigned null,
account_entry_id int unsigned null,
account_entry_flow_id int unsigned null,
owner_category int unsigned null,
owner_id int unsigned null,
customer_bill_id int unsigned null,
product_id int unsigned null,
qty decimal(15, 2) default 0.00 null,
unit_price decimal(15, 5) default 0.00000 null,
debit decimal(15, 5) default 0.00000 null,
credit decimal(15, 5) default 0.00000 null,
bill_date datetime null
);
create index idx_account_entry_flow_id
on customer_bill_entry (account_entry_flow_id);
create index idx_account_entry_id
on customer_bill_entry (account_entry_id);
create table customer_bill_file
(
id int unsigned auto_increment
primary key,
file_type varchar(255) null,
content longblob null
)
comment 'List of customer bill stored file';
create table customer_receipt_entry
(
id int unsigned auto_increment
primary key,
flow_id int unsigned null,
account_entry_id int unsigned null,
account_entry_flow_id int unsigned null,
owner_category int unsigned null,
owner_id int unsigned null,
payment_type_id int unsigned null,
customer_receipt_id int unsigned null,
debit decimal(15, 5) default 0.00000 null,
credit decimal(15, 5) default 0.00000 null,
receipt_date datetime null,
owner_email varchar(255) null
);
create index idx_account_entry_flow_id
on customer_receipt_entry (account_entry_flow_id);
create index idx_account_entry_id
on customer_receipt_entry (account_entry_id);
create table customer_receipt_file
(
id int unsigned auto_increment
primary key,
file_type varchar(255) null,
content longblob null
);
create table default_display
(
person_id int unsigned not null,
display_key varchar(255) default '' not null,
display_value text null,
primary key (person_id, display_key)
);
create table default_multi_display
(
resource_type char(30) not null,
person_id int unsigned not null,
resource_id varchar(100) not null,
display_value text null,
primary key (resource_type, person_id, resource_id)
);
create table email_sent
(
id bigint unsigned auto_increment
primary key,
message_id varchar(150) not null,
sender varchar(255) not null,
title varchar(255) not null,
category tinyint unsigned not null,
category_id int unsigned not null,
constraint message_id_UNIQUE
unique (message_id)
);
create table email_sent_recipient
(
id bigint unsigned auto_increment
primary key,
email_sent_id bigint unsigned not null,
person_id int unsigned not null,
email varchar(255) null,
recipient_type tinyint unsigned null,
smtp_status_category varchar(30) null,
email_status tinyint unsigned null,
update_date datetime not null
);
create index email_sent_id_idx
on email_sent_recipient &