establishment table structure

establishment table structure#

The establishments table include endogenous establishments and a subset of the exogenous establishments and their attributes: parent firm, sector, county, employees, medium duty and heavy duty truck fleets, production and consumption of freight tonnage

Table Structure#
Field	Type	NULL allowed	Default Value	Foreign key	Description
establishment*	INTEGER	NO			The unique identifier of this establishment
firm	INTEGER	NO		Firm(firm)	The parent firm identifier (foreign key to the Firm table)
naics	INTEGER	NO			The 3-digit NAICS code of the establishment
county	INTEGER	NO			The county FIPS code of the establishment
location	INTEGER	YES	-1		The selected location of the establishment
employees	INTEGER	NO	0		Number of employees
medium_duty_trucks	INTEGER	YES	0		Number of medium duty trucks in the firm fleet
heavy_duty_trucks	INTEGER	YES	0		Number of heavy duty trucks in the firm fleet
production	REAL	YES	0		Freight production (units: metric tons)
consumption	REAL	YES	0		Freight consumption (units: metric tons)

(* - Primary key)

The SQL statement for table and index creation is below.

CREATE TABLE Establishment (
    "establishment"         INTEGER NOT NULL  PRIMARY KEY,
    "firm"                  INTEGER NOT NULL,
    "naics"                 INTEGER NOT NULL,
    "county"                INTEGER NOT NULL,
    "location"              INTEGER           DEFAULT -1,
    "employees"             INTEGER NOT NULL  DEFAULT 0,
    "medium_duty_trucks"    INTEGER           DEFAULT 0,
    "heavy_duty_trucks"     INTEGER           DEFAULT 0,
    "production"            REAL              DEFAULT 0,
    "consumption"           REAL              DEFAULT 0,
    CONSTRAINT firm_fk FOREIGN KEY (firm)
    REFERENCES Firm (firm) DEFERRABLE INITIALLY DEFERRED
);