CREATE TABLE current
(
    ballotpedia_id      TEXT,
    bioguide_id         TEXT,
    birthday_bio        DATE,
    cspan_id            REAL,
    fec_id              TEXT,
    first_name          TEXT,
    gender_bio          TEXT,
    google_entity_id_id TEXT,
    govtrack_id         INTEGER,
    house_history_id    REAL,
    icpsr_id            REAL,
    last_name           TEXT,
    lis_id              TEXT,
    maplight_id         REAL,
    middle_name         TEXT,
    nickname_name       TEXT,
    official_full_name  TEXT,
    opensecrets_id      TEXT,
    religion_bio        TEXT,
    suffix_name         TEXT,
    thomas_id           INTEGER,
    votesmart_id        REAL,
    wikidata_id         TEXT,
    wikipedia_id        TEXT,
        primary key (bioguide_id, cspan_id)
);
current
CREATE TABLE "current-terms"
(
    address            TEXT,
    bioguide           TEXT,
    caucus             TEXT,
    chamber            TEXT,
    class              REAL,
    contact_form       TEXT,
    district           REAL,
    end                TEXT,
    fax                TEXT,
    last               TEXT,
    name               TEXT,
    office             TEXT,
    party              TEXT,
    party_affiliations TEXT,
    phone              TEXT,
    relation           TEXT,
    rss_url            TEXT,
    start              TEXT,
    state              TEXT,
    state_rank         TEXT,
    title              TEXT,
    type               TEXT,
    url                TEXT,
    primary key (bioguide, end),
    foreign key (bioguide) references current(bioguide_id)
);
current-terms
CREATE TABLE historical
(
    ballotpedia_id             TEXT,
    bioguide_id                TEXT
            primary key,
    bioguide_previous_id       TEXT,
    birthday_bio               TEXT,
    cspan_id                   TEXT,
    fec_id                     TEXT,
    first_name                 TEXT,
    gender_bio                 TEXT,
    google_entity_id_id        TEXT,
    govtrack_id                INTEGER,
    house_history_alternate_id TEXT,
    house_history_id           REAL,
    icpsr_id                   REAL,
    last_name                  TEXT,
    lis_id                     TEXT,
    maplight_id                TEXT,
    middle_name                TEXT,
    nickname_name              TEXT,
    official_full_name         TEXT,
    opensecrets_id             TEXT,
    religion_bio               TEXT,
    suffix_name                TEXT,
    thomas_id                  TEXT,
    votesmart_id               TEXT,
    wikidata_id                TEXT,
    wikipedia_id               TEXT
);
historical
CREATE TABLE "historical-terms"
(
    address            TEXT,
    bioguide           TEXT
            primary key,
    chamber            TEXT,
    class              REAL,
    contact_form       TEXT,
    district           REAL,
    end                TEXT,
    fax                TEXT,
    last               TEXT,
    middle             TEXT,
    name               TEXT,
    office             TEXT,
    party              TEXT,
    party_affiliations TEXT,
    phone              TEXT,
    relation           TEXT,
    rss_url            TEXT,
    start              TEXT,
    state              TEXT,
    state_rank         TEXT,
    title              TEXT,
    type               TEXT,
    url                TEXT,
    foreign key (bioguide) references historical(bioguide_id)
);
historical-terms
CREATE TABLE "social-media"
(
    bioguide     TEXT
            primary key,
    facebook     TEXT,
    facebook_id  REAL,
    govtrack     REAL,
    instagram    TEXT,
    instagram_id REAL,
    thomas       INTEGER,
    twitter      TEXT,
    twitter_id   REAL,
    youtube      TEXT,
    youtube_id   TEXT,
    foreign key (bioguide) references current(bioguide_id)
);
social-media
original_column_name,column_name,column_description,data_format,value_description
address,,the address of this legislator,text,
bioguide,bioguide id,The alphanumeric ID for this legislator ,text,
caucus,,caucus,text,"For independents, the party that the legislator caucuses with, using the same values as the party field. Omitted if the legislator caucuses with the party indicated in the party field. When in doubt about the difference between the party and caucus fields, the party field is what displays after the legislator's name (i.e. ""(D)"") but the caucus field is what normally determines committee seniority. This field was added starting with terms for the 113th Congress."
chamber,,chamber,text,"â¢ senate
â¢ house"
class,,class,real,"For senators, their election class (1, 2, or 3). 
commonsense evidence:
only senator has class, if the value is null or empty, it means this legislator is not senator."
contact_form,,The website URL of the contact page on the legislator's official website,text,
district,,district,real,"For representatives, the district number they are serving from. 
commonsense evidence:
if null or empty, they are not representatives."
end,,the end of the term,text,"end: The date the term ended (because the Congress ended or the legislator died or resigned, etc.). End dates follow the Constitutional end of a term. Since 1935, terms begin and end on January 3 at noon in odd-numbered years, and thus a term end date may also be a term start date. Prior to 1935, terms began on March 4 and ended either on March 3 or March 4. The end date is the last date on which the legislator served this term. Unlike the start date, whether Congress was in session or not does not affect the value of this field."
fax,,"The fax number of the legislator's Washington, D.C. office",text,only valid if the term is current
last,,the last known number,text,
name,,,text,not useful
office,,office ,text,"only valid if the term is current, otherwise the last known office"
party,,The political party of the legislator.,text,"commonsense evidence:
If the legislator changed parties, this is the most recent party held during the term and party_affiliations will be set. Values are typically ""Democrat"", ""Independent"", or ""Republican"". The value typically matches the political party of the legislator on the ballot in his or her last election, although for state affiliate parties such as ""Democratic Farmer Labor"" we will use the national party name (""Democrat"") instead to keep the values of this field normalized."
party_affiliations,party affiliations,This field is present if the legislator changed party or caucus affiliation during the term.,text,"The value is a list of time periods, with start and end dates, each of which has a party field and a caucus field if applicable, with the same meanings as the main party and caucus fields. The time periods cover the entire term, so the first start will match the term start, the last end will match the term end, and the last party (and caucus if present) will match the term party (and caucus)."
phone,,"The phone number of the legislator's Washington, D.C. office",text,"only valid if the term is current, otherwise the last known number"
relation,,,text,not useful
rss_url,Really Simple Syndication URL,The URL to the official website's RSS feed,text,
start,,"The date legislative service began: the date the legislator was sworn in, if known, or else the beginning of the legislator's term. ",text,"Since 1935 regularly elected terms begin on January 3 at noon on odd-numbered years, but when Congress does not first meet on January 3, term start dates might reflect that swearing-in occurred on a later date. (Prior to 1935, terms began on March 4 of odd-numbered years, see here.) "
state,,state code,text,"commonsense evidence:
AK: Alaska 
AL: Alabama 
AR: Arkansas 
AZ: Arizona 
CA: California 
CO: Colorado 
CT: Connecticut 
DE: Delaware 
FL: Florida 
GA: Georgia 
HI: Hawaii 
IA: Iowa 
ID: Idaho 
IL: Illinois 
IN: Indiana 
KS: Kansas 
KY: Kentucky 
LA: Louisiana 
MA: Massachusetts 
MD: Maryland 
ME: Maine 
MI: Michigan 
MN: Minnesota 
MO: Missouri 
MS: Mississippi 
MT: Montana 
NC: North Carolina 
ND: North Dakota 
NE: Nebraska 
NH: New Hampshire 
NJ: New Jersey
9 divisions of states in us: (please mention)
https://www2.census.gov/geo/pdfs/maps-data/maps/reference/us_regdiv.pdf"
state_rank,,"whether they are the ""junior"" or ""senior"" senator",text,"only valid if the term is current, otherwise the senator's rank at the time the term ended
commonsense evidence:
only senator has this value"
title,,title of the legislator,text,
type,,The type of the term.,text,"Either ""sen"" for senators or ""rep"" for representatives and delegates to the House"
url,,The official website URL of the legislator ,text,only valid if the term is current
current-terms
original_column_name,column_name,column_description,data_format,value_description
bioguide,,The unique alphanumeric ID for this legislator ,text,
facebook,,The username of the current official Facebook presence of the legislator.,text,
facebook_id,,The numeric ID of the current official Facebook presence of the legislator.,real,
govtrack,,The numeric ID for this legislator on GovTrack.us,real,
instagram,,The current official Instagram handle of the legislator.,text,
instagram_id,,The numeric ID of the current official Instagram handle of the legislator.,real,
thomas,,The numeric ID for this legislator on http://thomas.gov and http://beta.congress.gov. ,integer,
twitter,,The current official Twitter handle of the legislator.,text,
twitter_id,,The numeric ID of the current official twitter handle of the legislator.,real,
youtube,,The current official YouTube username of the legislator.,text,
youtube_id,,The current official YouTube channel ID of the legislator.,text,
social-media
original_column_name,column_name,column_description,data_format,value_description
ballotpedia_id,ballotpedia id,"The ballotpedia.org page name for the person (spaces are given as spaces, not underscores).",text,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on ballotpedia.org"
bioguide_id,bioguide id,The alphanumeric ID for this legislator ,text,
birthday_bio,birthday bio,"The legislator's birthday,",date,in YYYY-MM-DD format.
cspan_id,cspan id,"The numeric ID for this legislator on C-SPAN's video website,",real,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on C-SPAN's video website"
fec_id,fec id, A list of IDs for this legislator in Federal Election Commission data.,text,"commonsense evidence:
if this value is null or empty, it means this legislator hasn't not been registered in Federal Election Commission data."
first_name,first name,first name of the legislator,text,
gender_bio,gender bio,gender of the legislator,text,
google_entity_id_id,google entity id,google entity id,text,"commonsense evidence:
if this value is null or empty, it means this legislator are not google entities"
govtrack_id,govtrack id,The numeric ID for this legislator on GovTrack.us,integer,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on GovTrack.us"
house_history_id,house history id,The numeric ID for this legislator on http://history.house.gov/People/Search/,real,"commonsense evidence:
The ID is present only for members who have served in the U.S. House."
icpsr_id,interuniversity consortium for political and social research id,"The numeric ID for this legislator in Keith Poole's VoteView.com website, originally based on an ID system by the Interuniversity Consortium for Political and Social Research (stored as an integer).",real,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on VoteView.com "
last_name,last name,last name of the legislator,text,
lis_id,legislator id,The alphanumeric ID for this legislator found in Senate roll call votes,text,"commonsense evidence:
The ID is present only for members who attended in Senate roll call votes"
maplight_id,maplight id,The numeric ID for this legislator on maplight.org,real,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on maplight.org"
middle_name,middle name ,the middle name of the legislator,text,
nickname_name,nickname,nickname of the legislator,text,
official_full_name,official full name,official full name,text,
opensecrets_id,opensecrets id,The alphanumeric ID for this legislator on OpenSecrets.org.,text,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on OpenSecrets.org."
religion_bio,religion bio,The legislator's religion.,text,
suffix_name,suffix name,suffix name,text,
thomas_id,thomas id,The numeric ID for this legislator on http://thomas.gov and http://beta.congress.gov. ,integer,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on both http://thomas.gov and http://beta.congress.gov."
votesmart_id,votesmart id,The numeric ID for this legislator on VoteSmart.org,real,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on VoteSmart.org"
wikidata_id,wikidata id,the id for wikidata,text,
wikipedia_id,wikipedia id, The Wikipedia page name for the person,text,"commonsense evidence:
if a legislator has wikipedia id, it means he or she is famous or impact"
current
original_column_name,column_name,column_description,data_format,value_description
bioguide,bioguide id,The alphanumeric ID for this legislator ,text,
address,,the address of this legislator,text,
caucus,,caucus,text,"For independents, the party that the legislator caucuses with, using the same values as the party field. Omitted if the legislator caucuses with the party indicated in the party field. When in doubt about the difference between the party and caucus fields, the party field is what displays after the legislator's name (i.e. ""(D)"") but the caucus field is what normally determines committee seniority. This field was added starting with terms for the 113th Congress."
chamber,,chamber,text,"â¢ senate
â¢ house"
class,,class,real,"For senators, their election class (1, 2, or 3). 
commonsense evidence:
only senator has class, if the value is null or empty, it means this legislator is not senator."
contact_form,,The website URL of the contact page on the legislator's official website,text,
district,,district,real,"For representatives, the district number they are serving from. 
commonsense evidence:
if null or empty, they are not representatives."
end,,the end of the term,text,"end: The date the term ended (because the Congress ended or the legislator died or resigned, etc.). End dates follow the Constitutional end of a term. Since 1935, terms begin and end on January 3 at noon in odd-numbered years, and thus a term end date may also be a term start date. Prior to 1935, terms began on March 4 and ended either on March 3 or March 4. The end date is the last date on which the legislator served this term. Unlike the start date, whether Congress was in session or not does not affect the value of this field."
fax,,"The fax number of the legislator's Washington, D.C. office",text,only valid if the term is current
last,,the last known number,text,
middle,,,text,
name,,,text,not useful
office,,office ,text,"only valid if the term is current, otherwise the last known office"
party,,The political party of the legislator.,text,"commonsense evidence:
If the legislator changed parties, this is the most recent party held during the term and party_affiliations will be set. Values are typically ""Democrat"", ""Independent"", or ""Republican"". The value typically matches the political party of the legislator on the ballot in his or her last election, although for state affiliate parties such as ""Democratic Farmer Labor"" we will use the national party name (""Democrat"") instead to keep the values of this field normalized."
party_affiliations,party affiliations,This field is present if the legislator changed party or caucus affiliation during the term.,text,"The value is a list of time periods, with start and end dates, each of which has a party field and a caucus field if applicable, with the same meanings as the main party and caucus fields. The time periods cover the entire term, so the first start will match the term start, the last end will match the term end, and the last party (and caucus if present) will match the term party (and caucus)."
phone,,"The phone number of the legislator's Washington, D.C. office",text,"only valid if the term is current, otherwise the last known number"
relation,,,text,not useful
rss_url,Really Simple Syndication URL,The URL to the official website's RSS feed,text,
start,,"The date legislative service began: the date the legislator was sworn in, if known, or else the beginning of the legislator's term. ",text,"Since 1935 regularly elected terms begin on January 3 at noon on odd-numbered years, but when Congress does not first meet on January 3, term start dates might reflect that swearing-in occurred on a later date. (Prior to 1935, terms began on March 4 of odd-numbered years, see here.) "
state,,state code,text,"commonsense evidence:
AK: Alaska 
AL: Alabama 
AR: Arkansas 
AZ: Arizona 
CA: California 
CO: Colorado 
CT: Connecticut 
DE: Delaware 
FL: Florida 
GA: Georgia 
HI: Hawaii 
IA: Iowa 
ID: Idaho 
IL: Illinois 
IN: Indiana 
KS: Kansas 
KY: Kentucky 
LA: Louisiana 
MA: Massachusetts 
MD: Maryland 
ME: Maine 
MI: Michigan 
MN: Minnesota 
MO: Missouri 
MS: Mississippi 
MT: Montana 
NC: North Carolina 
ND: North Dakota 
NE: Nebraska 
NH: New Hampshire 
NJ: New Jersey
9 divisions of states in us: (please mention)
https://www2.census.gov/geo/pdfs/maps-data/maps/reference/us_regdiv.pdf"
state_rank,,"whether they are the ""junior"" or ""senior"" senator",text,"only valid if the term is current, otherwise the senator's rank at the time the term ended
commonsense evidence:
only senator has this value"
title,,title of the legislator,text,
type,,The type of the term.,text,"Either ""sen"" for senators or ""rep"" for representatives and delegates to the House"
url,,The official website URL of the legislator ,text,only valid if the term is current
historical-terms
original_column_name,column_name,column_description,data_format,value_description
ballotpedia_id,ballotpedia id,"The ballotpedia.org page name for the person (spaces are given as spaces, not underscores).",text,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on ballotpedia.org"
bioguide_previous_id,bioguide previous id,The previous alphanumeric ID for this legislator ,text,
bioguide_id,bioguide id,The alphanumeric ID for this legislator ,text,
birthday_bio,birthday bio,"The legislator's birthday,",text,in YYYY-MM-DD format.
cspan_id,cspan id,"The numeric ID for this legislator on C-SPAN's video website,",text,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on C-SPAN's video website"
fec_id,fec id, A list of IDs for this legislator in Federal Election Commission data.,text,"commonsense evidence:
if this value is null or empty, it means this legislator hasn't not been registered in Federal Election Commission data."
first_name,first name,first name of the legislator,text,
gender_bio,gender bio,gender of the legislator,text,
google_entity_id_id,google entity id,google entity id,text,"commonsense evidence:
if this value is null or empty, it means this legislator are not google entities"
govtrack_id,govtrack id,The numeric ID for this legislator on GovTrack.us,integer,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on GovTrack.us"
house_history_alternate_id,house history alternate id,The alternative numeric ID for this legislator,text,
house_history_id,house history id,The numeric ID for this legislator on http://history.house.gov/People/Search/,real,"commonsense evidence:
The ID is present only for members who have served in the U.S. House."
icpsr_id,interuniversity consortium for political and social research id,"The numeric ID for this legislator in Keith Poole's VoteView.com website, originally based on an ID system by the Interuniversity Consortium for Political and Social Research (stored as an integer).",real,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on VoteView.com "
last_name,last name,last name of the legislator,text,
lis_id,legislator id,The alphanumeric ID for this legislator found in Senate roll call votes,text,"commonsense evidence:
The ID is present only for members who attended in Senate roll call votes"
maplight_id,maplight id,The numeric ID for this legislator on maplight.org,text,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on maplight.org"
middle_name,middle name ,the middle name of the legislator,text,
nickname_name,nickname,nickname of the legislator,text,
official_full_name,official full name,official full name,text,
opensecrets_id,opensecrets id,The alphanumeric ID for this legislator on OpenSecrets.org.,text,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on OpenSecrets.org."
religion_bio,religion bio,The legislator's religion.,text,
suffix_name,suffix name,suffix name,text,
thomas_id,thomas id,The numeric ID for this legislator on http://thomas.gov and http://beta.congress.gov. ,text,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on both http://thomas.gov and http://beta.congress.gov."
votesmart_id,votesmart id,The numeric ID for this legislator on VoteSmart.org,text,"commonsense evidence:
if this value is null or empty, it means this legislator doesn't have account on VoteSmart.org"
wikidata_id,wikidata id,the id for wikidata,text,
wikipedia_id,wikipedia id, The Wikipedia page name for the person,text,"commonsense evidence:
if a legislator has wikipedia id, it means he or she is famous or impact"
historical