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

Aller à : navigation, rechercher
(→‎Structure)
 
(21 révisions intermédiaires par 3 utilisateurs non affichées)
Ligne 1 : Ligne 1 :
 +
=[[Media:OpenFlyers_database_schema.png|Schéma de la structure de la base de données]]=
 +
 
=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) 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,
+
−
  `group_sales` tinyint(1) NOT NULL DEFAULT '0',
+
−
  PRIMARY KEY (`id`),
+
−
  KEY `idx_owner_id` (`owner_id`)
+
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of account';
+
−
 
+
−
--
+
−
-- Table structure for table `account_entry`
+
−
--
+
  
 
CREATE TABLE `account_entry` (
 
CREATE TABLE `account_entry` (
Ligne 32 : Ligne 30 :
 
   `flow_id` int(11) DEFAULT NULL,
 
   `flow_id` int(11) DEFAULT NULL,
 
   `account_date` datetime DEFAULT NULL,
 
   `account_date` datetime DEFAULT NULL,
−
  `registration_date` datetime DEFAULT NULL,
 
 
   `account_id` int(11) DEFAULT NULL,
 
   `account_id` int(11) DEFAULT NULL,
 
   `credit` decimal(15,5) DEFAULT '0.00000',
 
   `credit` decimal(15,5) DEFAULT '0.00000',
Ligne 42 : Ligne 39 :
 
   `validated` int(1) DEFAULT '0',
 
   `validated` int(1) DEFAULT '0',
 
   `exported` int(1) DEFAULT '0',
 
   `exported` int(1) DEFAULT '0',
 +
  `registration_date` datetime DEFAULT NULL,
 
   `budget_id` int(10) unsigned DEFAULT NULL,
 
   `budget_id` int(10) unsigned DEFAULT NULL,
 
   `product_id` int(10) unsigned DEFAULT NULL,
 
   `product_id` int(10) unsigned DEFAULT NULL,
Ligne 48 : Ligne 46 :
 
   `lettering` int(11) DEFAULT NULL,
 
   `lettering` int(11) DEFAULT NULL,
 
   `lettering_date` datetime DEFAULT NULL,
 
   `lettering_date` datetime DEFAULT NULL,
 +
  `flow_reversal_id` int(10) unsigned DEFAULT NULL,
 +
  `transaction_id` text,
 
   PRIMARY KEY (`id`),
 
   PRIMARY KEY (`id`),
−
  KEY `idx_account_date` (`account_date`),
 
 
   KEY `idx_flow_id` (`flow_id`),
 
   KEY `idx_flow_id` (`flow_id`),
−
   KEY `idx_signature_date` (`signature_date`)
+
  KEY `idx_account_date` (`account_date`),
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of account entries';
+
   KEY `idx_signature_date` (`signature_date`),
 +
  KEY `flow_reversal_id_idx` (`flow_reversal_id`)
 +
);
  
−
--
+
create table account_link
−
-- Table structure for 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_link` (
+
create table account_type
−
  `account_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
−
  `linked_account_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    id                    int unsigned auto_increment
−
  PRIMARY KEY (`account_id`,`linked_account_id`)
+
        primary key,
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
−
-- Table structure for table `account_type`
+
(
−
--
+
    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` (
+
create table accounting
−
  `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) unsigned DEFAULT '1',
+
    name             varchar(255)           null,
−
  `order_num` int(11) DEFAULT NULL,
+
    unit_name        varchar(255)           null,
−
  `accounting_id` int(10) unsigned DEFAULT NULL,
+
    symbol            varchar(255)           null,
−
  `export_account_pattern` varchar(255) DEFAULT NULL,
+
    format            tinyint(1)             null,
−
  PRIMARY KEY (`id`)
+
    decimal_precision int unsigned default 2 null
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
);
  
−
--
+
create table accounting_profile
−
-- Table structure for table `account_type_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 `account_type_profile` (
+
create table activity_type
−
  `account_type_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
−
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
    id        bigint unsigned default 0 not null
−
  PRIMARY KEY (`account_type_id`,`profile_id`)
+
        primary key,
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Linked account type\r\nfield and profile';
+
    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
−
-- Table structure for table `accounting`
+
(
−
--
+
    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 `accounting` (
+
create table aircraft_type
−
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
−
  `name` varchar(255) DEFAULT NULL,
+
    id                   int unsigned auto_increment
−
  `unit_name` varchar(255) DEFAULT NULL,
+
        primary key,
−
  `symbol` varchar(255) DEFAULT NULL,
+
    flight_time_formula  varchar(255)       default '%DURATION' null,
−
  `format` tinyint(1) unsigned DEFAULT NULL,
+
    counter_state        tinyint(2)         default -1          not null,
−
  `decimal_precision` int(10) unsigned DEFAULT '2',
+
    tolerance            int                default 0          null,
−
  PRIMARY KEY (`id`)
+
    autonomy            int                default 5990        null,
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
−
-- Table structure for table `accounting_profile`
+
(
−
--
+
    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 `accounting_profile` (
+
create table aircraft_type_mandatory_flight_type
−
  `accounting_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
−
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
    aircraft_type_id int unsigned   null,
−
  PRIMARY KEY (`accounting_id`,`profile_id`)
+
    activity_type_id bigint unsigned null
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
)
 +
    comment 'List of compulsory flight type for each aircraft type';
  
−
--
+
create table aircraft_type_uncomp_flight_type
−
-- Table structure for table `activity_training`
+
(
−
--
+
    aircraft_type_id int unsigned    null,
 +
    activity_type_id bigint unsigned null
 +
)
 +
    comment 'List of uncompatible flight type for each aircraft type';
  
−
CREATE TABLE `activity_training` (
+
create table aircraft_type_validity_type
−
  `activity_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
−
  `theme_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    aircraft_type_id int unsigned default 0 not null,
−
  `student_id` int(10) DEFAULT NULL,
+
    validity_type_id int unsigned default 0 not null,
−
  `level_reached` int(10) unsigned DEFAULT NULL,
+
    check_num        int unsigned default 0 not null,
−
  `comment` varchar(255) DEFAULT NULL,
+
    primary key (aircraft_type_id, validity_type_id, check_num)
−
  `validated` tinyint(1) NOT NULL DEFAULT '0',
+
)
−
  `checker_person_id` int(10) DEFAULT NULL,
+
    comment 'Types of qualif required for each aircraft type';
−
  `checking_date` datetime DEFAULT NULL,
+
−
  `checking_sentence` varchar(255) DEFAULT NULL,
+
−
  PRIMARY KEY (`activity_id`,`theme_id`)
+
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
−
--
+
create table allocation_rule
−
-- Table structure for table `activity_type`
+
(
−
--
+
    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 `activity_type` (
+
create table balance
−
  `id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
(
−
  `name` varchar(255) DEFAULT NULL,
+
    account_id      int unsigned                   not null,
−
  `order_num` int(11) DEFAULT NULL,
+
    balance_date_id int unsigned                  not null,
−
  `activated` tinyint(1) NOT NULL DEFAULT '1',
+
    debit          decimal(15, 5) default 0.00000 null,
−
  `color` int(10) unsigned DEFAULT NULL,
+
    credit          decimal(15, 5) default 0.00000 null,
−
  `training` tinyint(1) NOT NULL DEFAULT '0',
+
    primary key (account_id, balance_date_id)
−
  PRIMARY KEY (`id`)
+
);
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='list of flight type';
+
  
−
--
+
create table balance_date
−
-- Table structure for table `aircraft`
+
(
−
--
+
    id          int unsigned auto_increment
 +
        primary key,
 +
    balance_date datetime null
 +
);
  
−
CREATE TABLE `aircraft` (
+
create table booking
−
  `id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
−
  `ref_date` datetime DEFAULT NULL,
+
    id               int unsigned auto_increment
−
  `ref_hours` int(11) DEFAULT NULL,
+
        primary key,
−
  `inspection_date` datetime DEFAULT NULL,
+
    booking_group_id int unsigned null,
−
  `inspection_time` int(11) DEFAULT NULL,
+
    start_date      datetime     null,
−
  `tolerance_time` int(11) unsigned DEFAULT '6000',
+
    end_date        datetime    null
−
  `last_counter` int(11) DEFAULT NULL,
+
);
−
  `interval_visit` int(11) unsigned DEFAULT '30000',
+
−
  `time_alert1` int(11) DEFAULT '6000',
+
−
  `time_alert2` int(11) DEFAULT '0',
+
−
  `time_alert3` int(11) DEFAULT '0',
+
−
  `day_alert1` smallint(3) DEFAULT '15',
+
−
  `day_alert2` smallint(3) DEFAULT '0',
+
−
  `day_alert3` smallint(3) DEFAULT '8',
+
−
  PRIMARY KEY (`id`)
+
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='ALL AIRCRAFTS OF ALL AIRCLUBS';
+
  
−
--
+
create table booking_account_entry
−
-- Table structure for table `aircraft_type`
+
(
−
--
+
    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 `aircraft_type` (
+
create table booking_activity_type
−
  `id` int(10) unsigned NOT NULL,
+
(
−
  `flight_time_formula` varchar(255) DEFAULT NULL,
+
    booking_id      int unsigned default 0 not null,
−
  `counter_state` tinyint(2) NOT NULL DEFAULT '-1',
+
    activity_type_id int unsigned default 0 not null,
−
  `tolerance` int(11) DEFAULT '0',
+
    primary key (booking_id, activity_type_id)
−
  `autonomy` int(11) DEFAULT '5990',
+
);
−
  `digit_counter_number` tinyint(1) unsigned NOT NULL DEFAULT '4',
+
−
  `true_air_speed` int(10) unsigned NOT NULL DEFAULT '0',
+
−
  PRIMARY KEY (`id`)
+
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Types of aircraft';
+
  
−
--
+
create table booking_changelog
−
-- Table structure for table `aircraft_type_allowed_status`
+
(
−
--
+
    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 `aircraft_type_allowed_status` (
+
create table booking_person
−
  `aircraft_type_id` int(10) unsigned DEFAULT NULL,
+
(
−
  `place_num` int(10) unsigned DEFAULT NULL,
+
    booking_id int unsigned default 0 not null,
−
  `status_id` int(10) DEFAULT NULL
+
    person_id  int unsigned default 0 not null,
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of allowed functions for each aircraft type';
+
    place_num int unsigned           null,
 +
    status_id int unsigned          null,
 +
    primary key (booking_id, person_id)
 +
);
  
−
--
+
create table booking_resource
−
-- Table structure for table `aircraft_type_mandatory_flight_type`
+
(
−
--
+
    booking_id  int unsigned default 0 not null,
 +
    resource_id int unsigned default 0 not null,
 +
    primary key (booking_id, resource_id)
 +
);
  
−
CREATE TABLE `aircraft_type_mandatory_flight_type` (
+
CREATE TABLE `booking_training_program` (
−
   `aircraft_type_id` int(10) unsigned DEFAULT NULL,
+
   `booking_group_id` int(10) unsigned NOT NULL,
−
   `activity_type_id` bigint(20) unsigned DEFAULT NULL
+
   `training_program_id` int(10) unsigned NOT NULL,
−
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of compulsory flight type for each aircraft type';
+
  PRIMARY KEY (`booking_group_id`,`training_program_id`),
 +
  CONSTRAINT `booking_training_program_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`)
 +
);
  
−
--
+
create table budget
−
-- Table structure for table `aircraft_type_uncomp_flight_type`
+
(
−
--
+
    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
 +
);