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);