LocDB Database layout draft

This is a possible DB layout. It has been developed in July 2007 by hansm when playing around with possible LocDB features. The SQL is designed for MySQL, but should be migrated to PG and be improved. (See also LocDB draft (Request for comments), Locations Database)

CREATE TABLE IF NOT EXISTS location(       loc_id int(6) unsigned NOT NULL auto_increment,        loc_page int(8) unsigned NOT NULL,        loc_isintitle varchar(255) binary,        loc_type int(3) unsigned,        loc_population int(10) unsigned,        loc_area float,        loc_flagtitle varchar(255) binary,        loc_coatarmstitle varchar(255) binary,        loc_maptitle varchar(255) binary,        loc_sharedcattitle varchar(255) binary,        loc_un char(6),        loc_gns varchar(255) binary,        loc_iso3166_2 char(6),

PRIMARY KEY loc_id (loc_id), UNIQUE INDEX loc_page (loc_page) );

CREATE TABLE IF NOT EXISTS locname (       ln_id int(8) unsigned NOT NULL auto_increment,        ln_loc int(6) unsigned NOT NULL,        ln_langcode char(8),        ln_name varchar(255) binary,

PRIMARY KEY ln_id (ln_id), UNIQUE INDEX ln_loclang( ln_loc,ln_langcode), INDEX ln_loc (ln_loc), INDEX (ln_name) );

CREATE TABLE IF NOT EXISTS locnamelocal (       lnl_id int(8) unsigned NOT NULL auto_increment,        lnl_loc int(6) unsigned NOT NULL,        lnl_langcode char(8),        lnl_name varchar(255) binary,        lnl_transname varchar(255) binary,

PRIMARY KEY lnl_id (lnl_id), UNIQUE INDEX lnl_loclang( lnl_loc,lnl_langcode), INDEX lnl_loc (lnl_loc) );

CREATE TABLE IF NOT EXISTS altisin (       aii_id int(8) unsigned NOT NULL auto_increment,        aii_loc int(6) unsigned NOT NULL,        aii_altisintitle varchar(255) binary,        aii_idx int(5) unsigned NOT NULL,

PRIMARY KEY aii_id (aii_id), -- UNIQUE INDEX aii_localtisin( aii_loc,aii_altisintitle), INDEX aii_localtisin( aii_loc,aii_altisintitle), INDEX aii_loc (aii_loc), INDEX (aii_altisintitle) );

CREATE TABLE IF NOT EXISTS suplcountry (       scn_id int(8) unsigned NOT NULL auto_increment,        scn_loc int(6) unsigned NOT NULL,        scn_capitaltitle varchar(255) binary,        scn_gov char(8),        scn_currency char(3),        scn_currabrev varchar(32),        scn_currabrev2 varchar(32),        scn_postcntry varchar(64),        scn_phonecntry varchar(64),        scn_volt varchar(255) binary,        scn_freq varchar(255) binary,        scn_plug varchar(255) binary,        scn_tld char(2),        scn_tld1 char(2),        scn_tld2 char(2),        scn_cia char(2),        scn_timezonefrom int(4),        scn_timezoneto   int(4),

PRIMARY KEY scn_id (scn_id), INDEX scn_loc (scn_loc) );

CREATE TABLE IF NOT EXISTS suplcity (       sct_id int(8) unsigned NOT NULL auto_increment,        sct_loc int(6) unsigned NOT NULL,        sct_elev int(4),        sct_postcty varchar(64),        sct_phonecty varchar(64),        sct_carboard varchar(16),

PRIMARY KEY sct_id (sct_id), INDEX sct_loc (sct_loc) );

CREATE TABLE IF NOT EXISTS religion ( rel_id int(8) unsigned NOT NULL auto_increment,  rel_loc int(6) unsigned NOT NULL,  rel_religion char(8),  rel_confession char(8),        rel_percent float,  rel_year int(4) unsigned,

PRIMARY KEY rel_id (rel_id), UNIQUE INDEX rel_locrelconf( rel_loc,rel_religion,rel_confession), INDEX rel_loc (rel_loc), INDEX rel_religion( rel_religion) );

CREATE TABLE IF NOT EXISTS langofficial ( lgo_id int(8) unsigned NOT NULL auto_increment,  lgo_loc int(6) unsigned NOT NULL,  lgo_lang char(8) NOT NULL,        lgo_idx int(2) unsigned NOT NULL,

PRIMARY KEY lgo_id (lgo_id), INDEX lgo_loc (lgo_loc), INDEX lgo_lang( lgo_lang) );

CREATE TABLE IF NOT EXISTS langusual ( lgu_id int(8) unsigned NOT NULL auto_increment,  lgu_loc int(6) unsigned NOT NULL,  lgu_lang char(8) NOT NULL,        lgu_idx int(2) unsigned NOT NULL,

PRIMARY KEY lgu_id (lgu_id), INDEX lgu_loc (lgu_loc), INDEX lgu_lang( lgu_lang) );

CREATE TABLE IF NOT EXISTS phone ( phn_id int(8) unsigned NOT NULL auto_increment,  phn_loc int(6) unsigned NOT NULL,  phn_phone varchar(64) NOT NULL,  phn_what char(8) NOT NULL,

PRIMARY KEY phn_id (phn_id), INDEX phn_loc (phn_loc) );

CREATE TABLE IF NOT EXISTS climate ( clm_id int(8) unsigned NOT NULL auto_increment,  clm_loc int(6) unsigned NOT NULL,  clm_what char(8) NOT NULL,        clm_m1 float,        clm_m2 float,        clm_m3 float,        clm_m4 float,        clm_m5 float,        clm_m6 float,        clm_m7 float,        clm_m8 float,        clm_m9 float,        clm_m10 float,        clm_m11 float,        clm_m12 float,

PRIMARY KEY clm_id (clm_id), INDEX clm_loc (clm_loc), INDEX clm_wm1 (clm_what,clm_m1), INDEX clm_wm2 (clm_what,clm_m2), INDEX clm_wm3 (clm_what,clm_m3), INDEX clm_wm4 (clm_what,clm_m4), INDEX clm_wm5 (clm_what,clm_m5), INDEX clm_wm6 (clm_what,clm_m6), INDEX clm_wm7 (clm_what,clm_m7), INDEX clm_wm8 (clm_what,clm_m8), INDEX clm_wm9 (clm_what,clm_m9), INDEX clm_wm10 (clm_what,clm_m10), INDEX clm_wm11 (clm_what,clm_m11), INDEX clm_wm12 (clm_what,clm_m12) );

CREATE TABLE IF NOT EXISTS vcard ( vcd_id int(8) unsigned NOT NULL auto_increment,  vcd_loc int(6) unsigned NOT NULL,  vcd_key varchar(255) NOT NULL,  vcd_name varchar(255) NOT NULL,  -- vcd_type char(8) NOT NULL,  -- vcd_subtype char(8),  vcd_address varchar(255),  vcd_phone varchar(64),  vcd_mobile varchar(64),  vcd_fax varchar(64),  vcd_faxmob varchar(64),  vcd_email varchar(128),  vcd_web varchar(255),  vcd_open text,  vcd_pricecat char(1),  vcd_creditcard varchar(255),

PRIMARY KEY vcd_id (vcd_id), UNIQUE INDEX vcd_lockey (vcd_loc,vcd_key) -- INDEX vcd_loc (vcd_loc) );

CREATE TABLE IF NOT EXISTS vcardkeys ( vck_id int(8) unsigned NOT NULL auto_increment,  vck_vcard int(8) unsigned NOT NULL,  vck_type char(8) NOT NULL,  vck_subtype char(8) NOT NULL,        vck_idx int(5) unsigned NOT NULL,

PRIMARY KEY vck_id (vck_id), INDEX vck_vcard (vck_vcard), INDEX vck_keys (vck_type,vck_subtype) );

CREATE TABLE IF NOT EXISTS distance ( dst_id int(8) unsigned NOT NULL auto_increment,  dst_loc int(6) unsigned NOT NULL,  dst_loc2 int(6) unsigned NOT NULL,  dst_by char(8) NOT NULL,  dst_dist float NOT NULL,

PRIMARY KEY dst_id (dst_id), INDEX dst_locloc (dst_loc, dst_loc2) );

CREATE TABLE IF NOT EXISTS traveltime ( ttm_id int(8) unsigned NOT NULL auto_increment,  ttm_loc int(6) unsigned NOT NULL,  ttm_loc2 int(6) unsigned NOT NULL,  ttm_by char(8) NOT NULL,  ttm_time float NOT NULL,

PRIMARY KEY ttm_id (ttm_id), INDEX ttm_locloc (ttm_loc, ttm_loc2) );

CREATE TABLE IF NOT EXISTS currency ( cur_id int(8) unsigned NOT NULL auto_increment,  cur_iso char(3) NOT NULL,  cur_symbol char(8),  cur_rate float NOT NULL,  cur_date char(8),

PRIMARY KEY cur_id (cur_id), UNIQUE INDEX cur_iso (cur_iso) );

-- -- Definitions of codes. --

CREATE TABLE IF NOT EXISTS vcardcodes ( vcc_id int(8) unsigned NOT NULL auto_increment,  vcc_major varchar(8) NOT NULL,  vcc_minor varchar(8),

PRIMARY KEY vcc_id (vcc_id), UNIQUE INDEX vcc_majmin (vcc_major,vcc_minor) );

CREATE TABLE IF NOT EXISTS loctypecodes ( ltc_id int(8) unsigned NOT NULL auto_increment,  ltc_type varchar(8) NOT NULL,

PRIMARY KEY ltc_id (ltc_id), UNIQUE INDEX ltc_type (ltc_type) );

CREATE TABLE IF NOT EXISTS religioncodes ( rlc_id int(8) unsigned NOT NULL auto_increment,  rlc_religion varchar(8) NOT NULL,  rlc_confession varchar(8),

PRIMARY KEY rlc_id (rlc_id), UNIQUE INDEX rlc_relconf (rlc_religion,rlc_confession) );

CREATE TABLE IF NOT EXISTS governmentcodes ( gmc_id int(8) unsigned NOT NULL auto_increment,  gmc_gov varchar(8) NOT NULL,

PRIMARY KEY gmc_id (gmc_id), UNIQUE INDEX gmc_gov (gmc_gov) );

CREATE TABLE IF NOT EXISTS climatecodes ( clc_id int(8) unsigned NOT NULL auto_increment,  clc_climate varchar(8) NOT NULL,

PRIMARY KEY clc_id (clc_id), UNIQUE INDEX clc_climate (clc_climate) );