Structure de la base de données : Différence entre versions

Aller à : navigation, rechercher
(→‎Structure)
(→‎Champs cachés)
(7 révisions intermédiaires par 2 utilisateurs non affichées)
Ligne 1 : Ligne 1 :
 
=Structure=
 
=Structure=
  
−
<sql>--
+
<sql>create table account
−
-- Table structure for 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 TABLE `account` (
+
create index idx_owner_id
−
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
    on account (owner_id);
−
  `creation_date` date DEFAULT NULL,
+
−
  `name` text,
+
−
  `export_account` varchar(255) DEFAULT NULL,
+
−
  `activated` tinyint(1) unsigned NOT NULL DEFAULT '1',
+
−
  `category` tinyint(1) unsigned DEFAULT '0',
+
−
  `account_type` tinyint(1) unsigned DEFAULT '0',
+
−
  `owner_id` int(10) unsigned NOT NULL DEFAULT '0',
+
−
  `payment_allowed` tinyint(1) DEFAULT '0',
+
−
  `budget_id` int(10) unsigned DEFAULT NULL,
+
−
  `order_num` int(11) DEFAULT NULL,
+
−
  `accounting_id` int(10) unsigned DEFAULT NULL,
+
−
  `deactivated_date` datetime DEFAULT NULL,
+
−
  PRIMARY KEY (`id`),
+
−
  KEY `idx_owner_id` (`owner_id`)
+
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of account';
+
  
−
--
+
create table account_entry
−
-- Table structure for 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 TABLE `account_entry` (
+
create index idx_account_date
−
  `id` int(11) NOT NULL AUTO_INCREMENT,
+
    on account_entry (account_date);
−
  `flow_id` int(11) DEFAULT NULL,
+
−
  `account_date` datetime DEFAULT NULL,
+
−
  `registration_date` datetime DEFAULT NULL,
+
−
  `account_id` int(11) DEFAULT NULL,
+
−
  `credit` decimal(15,5) DEFAULT '0.00000',
+
−
  `debit` decimal(15,5) DEFAULT '0.00000',
+
−
  `payment_type` int(10) unsigned DEFAULT NULL,
+
−
  `payment_description` text,
+
−
  `person_delivery_id` int(10) unsigned DEFAULT NULL,
+
−
  `comments` text,
+
−
  `validated` int(1) DEFAULT '0',
+
−
  `exported` int(1) DEFAULT '0',
+
−
  `budget_id` int(10) unsigned DEFAULT NULL,
+
−
  `product_id` int(10) unsigned DEFAULT NULL,
+
−
  `signature` varchar(56) DEFAULT NULL,
+
−
  `signature_date` bigint(13) unsigned DEFAULT NULL,
+
−
  `lettering` int(11) DEFAULT NULL,
+
−
  `lettering_date` datetime DEFAULT NULL,
+
−
  PRIMARY KEY (`id`),
+
−
  KEY `idx_account_date` (`account_date`),
+
−
  KEY `idx_flow_id` (`flow_id`),
+
−
  KEY `idx_signature_date` (`signature_date`)
+
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of account entries';
+
  
−
--
+
create index idx_flow_id
−
-- Table structure for table `account_link`
+
    on account_entry (flow_id);
−
--
+
  
−
CREATE TABLE `account_link` (
+
create index idx_signature_date
−
  `account_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    on account_entry (signature_date);
−
  `linked_account_id` int(10) unsigned NOT NULL DEFAULT '0',
+
−
  PRIMARY KEY (`account_id`,`linked_account_id`)
+
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
−
--
+
create table account_link
−
-- Table structure for table `account_type`
+
(
−
--
+
    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` (
+
create table account_type
−
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
−
  `name` varchar(255) DEFAULT NULL,
+
    id                     int unsigned auto_increment
−
  `category` tinyint(1) unsigned DEFAULT '0',
+
        primary key,
−
  `activated` tinyint(1) DEFAULT '1',
+
    name                   varchar(255)                 null,
−
  `order_num` int(11) DEFAULT NULL,
+
    category               tinyint(1) unsigned default 0 null,
−
  `accounting_id` int(10) unsigned DEFAULT NULL,
+
    activated             tinyint(1)         default 1 null,
−
  `export_account_pattern` varchar(255) DEFAULT NULL,
+
    order_num             int                           null,
−
  PRIMARY KEY (`id`)
+
    accounting_id         int unsigned                 null,
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of account type';
+
    export_account_pattern varchar(255)                 null
 +
);
  
−
--
+
create table account_type_profile
−
-- Table structure for 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 `account_type_profile` (
+
create table accounting
−
  `account_type_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
−
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
    id                int unsigned auto_increment
−
  PRIMARY KEY (`account_type_id`,`profile_id`)
+
        primary key,
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Linked account type and profile';
+
    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
−
-- Table structure for table `accounting`
+
(
−
--
+
    accounting_id int unsigned    default 0 not null,
 +
    profile_id    bigint unsigned default 0 not null,
 +
    primary key (accounting_id, profile_id)
 +
);
  
−
CREATE TABLE `accounting` (
+
create table activity_training
−
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
−
  `name` varchar(255) DEFAULT NULL,
+
    activity_id      int unsigned default 0 not null,
−
  `unit_name` varchar(255) DEFAULT NULL,
+
    theme_id          int unsigned default 0 not null,
−
  `symbol` varchar(255) DEFAULT NULL,
+
    student_id        int(10)               null,
−
  `format` tinyint(1) DEFAULT NULL,
+
    level_reached    int unsigned          null,
−
  `decimal_precision` int(10) unsigned DEFAULT '2',
+
    comment          varchar(255)           null,
−
  PRIMARY KEY (`id`)
+
    validated        tinyint(1)   default 0 not null,
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
−
-- Table structure for table `accounting_profile`
+
(
−
--
+
    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 `accounting_profile` (
+
create table aircraft
−
  `accounting_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
−
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
    id              int unsigned    default 0    not null
−
  PRIMARY KEY (`accounting_id`,`profile_id`)
+
        primary key,
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
−
-- Table structure for table `activity_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 `activity_type` (
+
create table aircraft_type_allowed_status
−
  `id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
(
−
  `name` varchar(255) DEFAULT NULL,
+
    aircraft_type_id int unsigned null,
−
  `order_num` int(11) DEFAULT NULL,
+
    place_num        int unsigned null,
−
  `activated` tinyint(1) NOT NULL DEFAULT '1',
+
    status_id        int unsigned null
−
  `color` int(10) unsigned DEFAULT NULL,
+
)
−
  PRIMARY KEY (`id`)
+
    comment 'List of allowed functions for each aircraft type';
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='list of flight type';
+
  
−
--
+
create table aircraft_type_mandatory_flight_type
−
-- Table structure for table `aircraft`
+
(
−
--
+
    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` (
+
create table aircraft_type_uncomp_flight_type
−
  `id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
−
  `ref_date` datetime DEFAULT NULL,
+
    aircraft_type_id int unsigned   null,
−
  `ref_hours` int(11) DEFAULT NULL,
+
    activity_type_id bigint unsigned null
−
  `inspection_date` datetime DEFAULT NULL,
+
)
−
  `inspection_time` int(11) DEFAULT NULL,
+
    comment 'List of uncompatible flight type for each aircraft type';
−
  `interval_visit` int(11) unsigned DEFAULT '30000',
+
−
  `time_alert1` int(11) DEFAULT '6000',
+
−
  `time_alert2` int(11) DEFAULT '0',
+
−
  `time_alert3` int(11) DEFAULT '-3000',
+
−
  `day_alert1` smallint(3) DEFAULT '15',
+
−
  `day_alert2` smallint(3) DEFAULT '0',
+
−
  `day_alert3` smallint(3) DEFAULT '-8',
+
−
  `last_counter` int(11) DEFAULT NULL,
+
−
  `tolerance_time` int(10) unsigned DEFAULT '6000',
+
−
  PRIMARY KEY (`id`)
+
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Aircraft data linked to resource';
+
  
−
--
+
create table aircraft_type_validity_type
−
-- Table structure for table `aircraft_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 `aircraft_type` (
+
create table allocation_rule
−
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
−
  `flight_time_formula` varchar(255) DEFAULT '%DURATION',
+
    id         int unsigned auto_increment
−
  `counter_state` tinyint(2) NOT NULL DEFAULT '-1',
+
        primary key,
−
  `tolerance` int(11) DEFAULT '0',
+
    title      varchar(255)         not null,
−
  `autonomy` int(11) DEFAULT '5990',
+
    account_id int(10)             not null,
−
  `true_air_speed` int(10) unsigned NOT NULL DEFAULT '0',
+
    keyword    varchar(255)         null,
−
  `digit_counter_number` tinyint(1) unsigned NOT NULL DEFAULT '4',
+
    order_num  int(10)             not null,
−
  PRIMARY KEY (`id`)
+
    amount    tinyint(1)           not null,
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Types of aircraft';
+
    mandatory  tinyint(1) default 0 not null
 +
);
  
−
--
+
create table balance
−
-- Table structure for table `aircraft_type_allowed_status`
+
(
−
--
+
    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 `aircraft_type_allowed_status` (
+
create table balance_date
−
  `aircraft_type_id` int(10) unsigned DEFAULT NULL,
+
(
−
  `place_num` int(10) unsigned DEFAULT NULL,
+
    id          int unsigned auto_increment
−
  `status_id` int(10) unsigned DEFAULT NULL
+
        primary key,
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of allowed functions for each aircraft type';
+
    balance_date datetime null
 +
);
  
−
--
+
create table booking
−
-- Table structure for table `aircraft_type_mandatory_flight_type`
+
(
−
--
+
    id              int unsigned auto_increment
 +
        primary key,
 +
    booking_group_id int unsigned null,
 +
    start_date      datetime    null,
 +
    end_date        datetime    null
 +
);
  
−
CREATE TABLE `aircraft_type_mandatory_flight_type` (
+
create table booking_account_entry
−
  `aircraft_type_id` int(10) unsigned DEFAULT NULL,
+
(
−
  `activity_type_id` bigint(20) unsigned DEFAULT NULL
+
    booking_id            int unsigned default 0 not null,
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of compulsory flight type for each aircraft type';
+
    account_entry_flow_id int unsigned default 0 not null,
 +
    primary key (booking_id, account_entry_flow_id)
 +
);
  
−
--
+
create table booking_activity_type
−
-- Table structure for table `aircraft_type_uncomp_flight_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 `aircraft_type_uncomp_flight_type` (
+
create table booking_changelog
−
  `aircraft_type_id` int(10) unsigned DEFAULT NULL,
+
(
−
  `activity_type_id` bigint(20) unsigned DEFAULT NULL
+
    id            int unsigned auto_increment
−