You are here: TWiki>

BioGIS Web>Dababase (2009-11-17, TWikiAdminUser)

Database

This is the main documentation about the used Postgres database.

The actual datase is lifedb.

The documentation is automaticaly generated with the plugin:

%PSQLDOC{hostt="server" port="number* dbname="dabase" user="who" command="do"}%

where command can be one of the unix psql commands:

\d to get the name of all tables
\d public.table to get a table description

You can find more information about the plugin hier PsqlDocPlugin.

Tablel of Contents

All Tables
General Tables
Table language
Table region
Table continent
Table country
Table url
Table region_url
Table class
Table order
Table family
Table genus
Table species
Table iucn
Table risk
Table risk_iucn
Table species_habitat
Table species_risk

All Tables

This is a overview of all the tables found in the database:

                   List of relations
 Schema |          Name          |   Type   |  Owner   
--------+------------------------+----------+----------
 public | class                  | table    | postgres
 public | class_id_seq           | sequence | postgres
 public | continent              | table    | postgres
 public | continent_id_seq       | sequence | postgres
 public | country                | table    | postgres
 public | country_id_seq         | sequence | postgres
 public | family                 | table    | postgres
 public | family_id_seq          | sequence | postgres
 public | genus                  | table    | postgres
 public | genus_id_seq           | sequence | postgres
 public | iucn                   | table    | postgres
 public | iucn_id_seq            | sequence | postgres
 public | language               | table    | postgres
 public | language_id_seq        | sequence | postgres
 public | order                  | table    | postgres
 public | order_id_seq           | sequence | postgres
 public | region                 | table    | postgres
 public | region_id_seq          | sequence | postgres
 public | region_url             | table    | postgres
 public | region_url_id_seq      | sequence | postgres
 public | risk                   | table    | postgres
 public | risk_id_seq            | sequence | postgres
 public | risk_iucn              | table    | postgres
 public | risk_iucn_id_seq       | sequence | postgres
 public | species                | table    | postgres
 public | species_habitat        | table    | postgres
 public | species_habitat_id_seq | sequence | postgres
 public | species_id_seq         | sequence | postgres
 public | species_risk           | table    | postgres
 public | species_risk_id_seq    | sequence | postgres
 public | url                    | table    | postgres
 public | url_id_seq             | sequence | postgres
(32 rows)

General Tables

Table language

The table language containes all supported languages of our application.

                                   Table "public.language"
   Column    |          Type          |                       Modifiers                       
-------------+------------------------+-------------------------------------------------------
 id          | integer                | not null default nextval('language_id_seq'::regclass)
 code        | character varying(5)   | not null
 description | character varying(100) | 
Indexes:
    "language_id_pkey" PRIMARY KEY, btree (id)

Table region

The table region containes all regions of interest for our application. The region can be any: geographical, political, social, eucological or biological.

                                   Table "public.region"
   Column    |          Type          |                      Modifiers                      
-------------+------------------------+-----------------------------------------------------
 id          | integer                | not null default nextval('region_id_seq'::regclass)
 region      | character varying(50)  | not null
 description | character varying(100) | 
 language_id | integer                | not null
Indexes:
    "regions_id_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "$1" FOREIGN KEY (language_id) REFERENCES language(id)

Table continent

The table continent points to the regions which are continents.

                           Table "public.continent"
  Column   |  Type   |                       Modifiers                        
-----------+---------+--------------------------------------------------------
 id        | integer | not null default nextval('continent_id_seq'::regclass)
 region_id | integer | not null
Indexes:
    "countinent_id_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "$1" FOREIGN KEY (region_id) REFERENCES region(id)

Table country

The table country points to the regions which are countries.

                             Table "public.country"
  Column   |     Type     |                      Modifiers                       
-----------+--------------+------------------------------------------------------
 id        | integer      | not null default nextval('country_id_seq'::regclass)
 region_id | integer      | not null
 code      | character(2) | not null
Indexes:
    "country_id_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "$1" FOREIGN KEY (region_id) REFERENCES region(id)

Table url

The table url containes all URL's which are used by our application, for instance by the web robots.

                                    Table "public.url"
    Column    |          Type          |                    Modifiers                     
--------------+------------------------+--------------------------------------------------
 id           | integer                | not null default nextval('url_id_seq'::regclass)
 url          | character varying(200) | not null
 href         | character varying(200) | 
 params       | character varying(200) | 
 organization | character varying(50)  | 
 description  | character varying(100) | 
 language_id  | integer                | 
Indexes:
    "url_id_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "$1" FOREIGN KEY (language_id) REFERENCES language(id)

Table region_url

The table url collects URL information on some regions. This is primary used by the web robots of application.

                           Table "public.region_url"
  Column   |  Type   |                        Modifiers                        
-----------+---------+---------------------------------------------------------
 id        | integer | not null default nextval('region_url_id_seq'::regclass)
 region_id | integer | not null
 url_id    | integer | not null
Indexes:
    "region_url_id_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "$1" FOREIGN KEY (region_id) REFERENCES region(id)
    "$2" FOREIGN KEY (url_id) REFERENCES url(id)

Table class

The table class is for the taxonomy of:

*class* ---> order ---> family ---> genus ---> species

                                   Table "public.class"
   Column    |          Type          |                     Modifiers                      
-------------+------------------------+----------------------------------------------------
 id          | integer                | not null default nextval('class_id_seq'::regclass)
 class       | character varying(50)  | not null
 name        | character varying(50)  | not null
 description | character varying(100) | 
 language_id | integer                | not null
Indexes:
    "class_id_pkey" PRIMARY KEY, btree (id)
    "class_key" UNIQUE, btree (class)
Foreign-key constraints:
    "$2" FOREIGN KEY (language_id) REFERENCES language(id)

Table order

The table order is for the taxonomy:

class ---> order ---> family ---> genus ---> species

                                   Table "public.order"
   Column    |          Type          |                     Modifiers                      
-------------+------------------------+----------------------------------------------------
 id          | integer                | not null default nextval('order_id_seq'::regclass)
 class_id    | integer                | 
 order       | character varying(50)  | not null
 name        | character varying(50)  | not null
 description | character varying(100) | 
 language_id | integer                | not null
Indexes:
    "order_id_pkey" PRIMARY KEY, btree (id)
    "order_key" UNIQUE, btree ("order")
Foreign-key constraints:
    "$1" FOREIGN KEY (class_id) REFERENCES class(id)
    "$2" FOREIGN KEY (language_id) REFERENCES language(id)

Table family

The table family is for the taxonomy:

class ---> order ---> family ---> genus ---> species

                                   Table "public.family"
   Column    |          Type          |                      Modifiers                      
-------------+------------------------+-----------------------------------------------------
 id          | integer                | not null default nextval('family_id_seq'::regclass)
 order_id    | integer                | 
 family      | character varying(50)  | not null
 name        | character varying(50)  | not null
 description | character varying(100) | 
 language_id | integer                | not null
Indexes:
    "family_id_pkey" PRIMARY KEY, btree (id)
    "family_key" UNIQUE, btree (family)
Foreign-key constraints:
    "$1" FOREIGN KEY (order_id) REFERENCES "order"(id)
    "$2" FOREIGN KEY (language_id) REFERENCES language(id)

Table genus

The table genus is for the taxonomy:

class ---> order ---> family ---> genus ---> species

                                   Table "public.genus"
   Column    |          Type          |                     Modifiers                      
-------------+------------------------+----------------------------------------------------
 id          | integer                | not null default nextval('genus_id_seq'::regclass)
 family_id   | integer                | 
 genus       | character varying(50)  | not null
 name        | character varying(50)  | not null
 description | character varying(100) | 
 language_id | integer                | not null
Indexes:
    "genus_id_pkey" PRIMARY KEY, btree (id)
    "genus_key" UNIQUE, btree (genus)
Foreign-key constraints:
    "$1" FOREIGN KEY (family_id) REFERENCES family(id)
    "$2" FOREIGN KEY (language_id) REFERENCES language(id)

Table species

The table species is for the taxonomy:

class ---> order ---> family ---> genus ---> species

                                   Table "public.species"
   Column    |          Type          |                      Modifiers                       
-------------+------------------------+------------------------------------------------------
 id          | integer                | not null default nextval('species_id_seq'::regclass)
 genus_id    | integer                | 
 species     | character varying(50)  | not null
 name        | character varying(50)  | not null
 description | character varying(100) | 
 language_id | integer                | not null
Indexes:
    "species_id_pkey" PRIMARY KEY, btree (id)
    "species_key" UNIQUE, btree (species)
Foreign-key constraints:
    "$1" FOREIGN KEY (genus_id) REFERENCES genus(id)
    "$2" FOREIGN KEY (language_id) REFERENCES language(id)

Table iucn

The table iucn defines the risk for species:

class ---> order ---> family ---> genus ---> species

                                   Table "public.iucn"
   Column    |          Type          |                     Modifiers                     
-------------+------------------------+---------------------------------------------------
 id          | integer                | not null default nextval('iucn_id_seq'::regclass)
 code        | character varying(2)   | not null
 risk        | character varying(50)  | not null
 description | character varying(100) | 
 language_id | integer                | not null
Indexes:
    "iucn_id_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "$1" FOREIGN KEY (language_id) REFERENCES language(id)

Table risk

The table risk is a extension of the iucn risk categories:

class ---> order ---> family ---> genus ---> species

                                   Table "public.iucn"
   Column    |          Type          |                     Modifiers                     
-------------+------------------------+---------------------------------------------------
 id          | integer                | not null default nextval('iucn_id_seq'::regclass)
 code        | character varying(2)   | not null
 risk        | character varying(50)  | not null
 description | character varying(100) | 
 language_id | integer                | not null
Indexes:
    "iucn_id_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "$1" FOREIGN KEY (language_id) REFERENCES language(id)

Table risk_iucn

The table risk_iucn matchs the extented risks to the iucn risk categories:

class ---> order ---> family ---> genus ---> species

                          Table "public.risk_iucn"
 Column  |  Type   |                       Modifiers                        
---------+---------+--------------------------------------------------------
 id      | integer | not null default nextval('risk_iucn_id_seq'::regclass)
 risk_id | integer | not null
 iucn_id | integer | not null
Indexes:
    "risk_iucn_id_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "$1" FOREIGN KEY (risk_id) REFERENCES risk(id)
    "$2" FOREIGN KEY (iucn_id) REFERENCES iucn(id)

Table species_habitat

The table species_habitat containes the habitat of species:

                           Table "public.species_habitat"
   Column   |  Type   |                          Modifiers                           
------------+---------+--------------------------------------------------------------
 id         | integer | not null default nextval('species_habitat_id_seq'::regclass)
 species_id | integer | not null
 region_id  | integer | not null
Indexes:
    "habitat_id_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "$1" FOREIGN KEY (species_id) REFERENCES species(id)
    "$2" FOREIGN KEY (region_id) REFERENCES region(id)

Table species_risk

The table species_risk gives the risk for a species:

                           Table "public.species_risk"
   Column   |  Type   |                         Modifiers                         
------------+---------+-----------------------------------------------------------
 id         | integer | not null default nextval('species_risk_id_seq'::regclass)
 species_id | integer | not null
 risk_id    | integer | not null
Indexes:
    "species_risk_id" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "$1" FOREIGN KEY (species_id) REFERENCES species(id)
    "$2" FOREIGN KEY (risk_id) REFERENCES risk(id)

-- TWikiAdminUser - 2009-11-10

Topic revision: r11 - 2009-11-17 - 23:11:55 - TWikiAdminUser

TWIKI.NET

BioGIS

Edit

Attach