establishments table structure#
The establishments table include endogenous establishments and a subset of the exogenous establishments and their attributes: parent firm, sector, county, employees, revenue, endogenous/exogenous, type of goods, freight rates, points of entry, possible land uses
Field |
Type |
NULL allowed |
Default Value |
Foreign key |
Description |
---|---|---|---|---|---|
estab_id* |
INTEGER |
NO |
The unique identifier of this establishment |
||
firm_id |
INTEGER |
NO |
Firms(firm_id) |
The parent firm identifier (foreign key to the Firm table) |
|
naics3 |
INTEGER |
NO |
The 3-digit NAICS code of the establishment |
||
fips_cnty |
INTEGER |
NO |
The county FIPS code of the establishment |
||
employees |
INTEGER |
NO |
0 |
Number of employees |
|
rev_1000_usd |
REAL |
YES |
0 |
Establishment revenue (units: $USD thousands) |
|
is_external |
INTEGER |
NO |
0 |
1, if an exogenous establishment, 0 if an endogenous establishment |
|
fp_rate |
REAL |
YES |
0 |
Freight production rate per empolyee (units: tons/employee) |
|
fa_rate |
REAL |
YES |
0 |
Freight attraction rate per empolyee (units: tons/employee) |
|
good_prod |
INTEGER |
NO |
0 |
Type of good produced by the establishment (none, bulk, intermediate, finished) |
|
good_cons |
INTEGER |
NO |
0 |
Type of good consumed by the establishment (none, bulk, intermediate, finished) |
|
poe_truck |
INTEGER |
NO |
0 |
Location of the highway point of entry/exit closest to the establishment (foreign key to the Location table) |
|
poe_rail |
INTEGER |
NO |
0 |
Location of the Railyard point of entry/exit closest to the establishment (foreign key to the Location table) |
|
poe_air |
INTEGER |
NO |
0 |
Location of the Airport point of entry/exit closest to the establishment (foreign key to the Location table) |
|
ohd_accept |
INTEGER |
NO |
0 |
boolean flag - can this establishment accept off-hour deliveries? |
|
land_use |
INTEGER |
NO |
0 |
Which types of land use can be used by the establishment (TODO: Add FreightLandUse enum when freight branch is merged) |
(* - Primary key)
The SQL statement for table and index creation is below.
CREATE TABLE Establishments (
"estab_id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"firm_id" INTEGER NOT NULL,
"naics3" INTEGER NOT NULL,
"fips_cnty" INTEGER NOT NULL,
"employees" INTEGER NOT NULL DEFAULT 0,
"rev_1000_usd" REAL DEFAULT 0,
"is_external" INTEGER NOT NULL DEFAULT 0,
"fp_rate" REAL DEFAULT 0,
"fa_rate" REAL DEFAULT 0,
"good_prod" INTEGER NOT NULL DEFAULT 0,
"good_cons" INTEGER NOT NULL DEFAULT 0,
"poe_truck" INTEGER NOT NULL DEFAULT 0,
"poe_rail" INTEGER NOT NULL DEFAULT 0,
"poe_air" INTEGER NOT NULL DEFAULT 0,
"ohd_accept" INTEGER NOT NULL DEFAULT 0,
"land_use" INTEGER NOT NULL DEFAULT 0,
CONSTRAINT firm_fk FOREIGN KEY (firm_id)
REFERENCES Firms (firm_id) DEFERRABLE INITIALLY DEFERRED
);