Subversion Repositories Sites.obs-saisons.fr

CREATE TABLE ods_triples (
  ot_id_triple INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,
  ot_cle VARCHAR(255)  NULL  ,
  ot_ce_parent INTEGER UNSIGNED  NULL  ,
  ot_valeur VARCHAR(255)  NULL  ,
  ot_description TEXT  NULL    ,
PRIMARY KEY(ot_id_triple),
  FOREIGN KEY(ot_ce_parent)
    REFERENCES ods_triples(ot_id_triple)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION);







CREATE INDEX IFK_fk_ot_id_triple_ot_ce_pare ON ods_triples (ot_ce_parent);


CREATE TABLE ods_communes (
  oc_id_commune INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,
  oc_code_insee INTEGER UNSIGNED  NULL  ,
  oc_nom TEXT  NULL  ,
  oc_secteur VARCHAR(50)  NULL  ,
  oc_latitude FLOAT  NULL  ,
  oc_longitude FLOAT  NULL    ,
PRIMARY KEY(oc_id_commune));










CREATE TABLE participants (
  id_participant INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT  ,
PRIMARY KEY(id_participant));




CREATE TABLE obs_stations (
  os_id_station INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,
  os_ce_participant INTEGER UNSIGNED  NULL  ,
  os_nom VARCHAR(255)  NULL  ,
  os_ce_commune INTEGER UNSIGNED  NULL  ,
  os_latitude FLOAT  NULL  ,
  os_longitude FLOAT  NULL  ,
  os_altitude FLOAT  NULL  ,
  os_ce_environnement INTEGER UNSIGNED  NULL  ,
  os_commentaire TEXT  NULL    ,
PRIMARY KEY(os_id_station),
  FOREIGN KEY(os_ce_participant)
    REFERENCES participants(id_participant)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(os_ce_commune)
    REFERENCES ods_communes(oc_id_commune)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(os_ce_environnement)
    REFERENCES ods_triples(ot_id_triple)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION);











CREATE INDEX IFK_fk_id_participant_os_ce_pa ON obs_stations (os_ce_participant);
CREATE INDEX IFK_fk_oc_id_commune_os_ce_com ON obs_stations (os_ce_commune);
CREATE INDEX IFK_fk_ot_id_triple_os_ce_envi ON obs_stations (os_ce_environnement);


CREATE TABLE ods_especes (
  oe_id_espece INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,
  oe_nom_vernaculaire VARCHAR(255)  NULL  ,
  oe_nom_scientifique VARCHAR(255)  NULL  ,
  oe_espece_ecole BOOL  NULL  ,
  oe_description TEXT  NULL  ,
  oe_ce_climat INTEGER UNSIGNED  NULL  ,
  oe_ce_evenements INTEGER UNSIGNED  NULL  ,
  oe_ce_type INTEGER UNSIGNED  NULL    ,
PRIMARY KEY(oe_id_espece),
  FOREIGN KEY(oe_ce_climat)
    REFERENCES ods_triples(ot_id_triple)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(oe_ce_evenements)
    REFERENCES ods_triples(ot_id_triple)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(oe_ce_type)
    REFERENCES ods_triples(ot_id_triple)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION);










CREATE INDEX IFK_fk_ot_id_triple_oe_ce_clim ON ods_especes (oe_ce_climat);
CREATE INDEX IFK_fk_ot_id_triples_oe_ce_eve ON ods_especes (oe_ce_evenements);
CREATE INDEX IFK_fk_ot_id_triple_oe_ce_type ON ods_especes (oe_ce_type);


CREATE TABLE ods_individus (
  oi_id_individu INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,
  oi_ce_espece INTEGER UNSIGNED  NULL  ,
  oi_ce_station INTEGER UNSIGNED  NULL    ,
PRIMARY KEY(oi_id_individu),
  FOREIGN KEY(oi_ce_espece)
    REFERENCES ods_especes(oe_id_espece)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(oi_ce_station)
    REFERENCES obs_stations(os_id_station)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION);





CREATE INDEX IFK_fk_oi_ce_espece_oe_id_espe ON ods_individus (oi_ce_espece);
CREATE INDEX IFK_fk_os_id_station_oi_ce_ind ON ods_individus (oi_ce_station);


CREATE TABLE ods_observations (
  oo_id_observation INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,
  oo_ordre INTEGER UNSIGNED  NOT NULL  ,
  oo_ce_individu INTEGER UNSIGNED  NULL  ,
  oo_ce_evenement INTEGER UNSIGNED  NULL  ,
  oo_date DATE  NULL  ,
  oo_commentaire TEXT  NULL  ,
  oo_date_saisie DATE  NULL  ,
  oo_date_modification DATE  NULL    ,
PRIMARY KEY(oo_id_observation),
  FOREIGN KEY(oo_ce_evenement)
    REFERENCES ods_triples(ot_id_triple)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(oo_ce_individu)
    REFERENCES ods_individus(oi_id_individu)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION);










CREATE INDEX IFK_fk_ot_id_triple_oo_ce_even ON ods_observations (oo_ce_evenement);
CREATE INDEX IFK_fk_oi_id_individu_oo_ce_in ON ods_observations (oo_ce_individu);