CREATE TABLE State (
    stateID    SMALLINT
,   abbrev     CHAR(2)   not NULL
,   PRIMARY KEY (stateID)
);


CREATE TABLE County (
    stateID     SMALLINT       not NULL
,   countyID    SMALLINT       not NULL
,   name       VARCHAR(40)
,   PRIMARY KEY (stateID,countyID)
,   FOREIGN KEY (stateID) REFERENCES State
);


CREATE TABLE CFCC (
    CFCC         VARCHAR(3) not NULL
,   description  VARCHAR(220) not NULL
,   PRIMARY KEY (CFCC)
);


CREATE TABLE Point (
    TZID      INTEGER
,   coord     POINT
,   PRIMARY KEY (TZID)
);


CREATE TABLE Chain (
    TLID      INTEGER  not NULL
,   CFCC      VARCHAR(3) not NULL
,   TZIDS     INTEGER  not NULL   -- starting point
,   TZIDE     INTEGER  not NULL   -- ending point
,   PRIMARY KEY (TLID)
,   FOREIGN KEY (CFCC) REFERENCES CFCC (CFCC)
,   FOREIGN KEY (TZIDS) REFERENCES Point (TZID)
,   FOREIGN KEY (TZIDE) REFERENCES Point (TZID)
);


CREATE TABLE ChainThroughCounty(
    TLID      INTEGER not NULL
,   stateID   SMALLINT not NULL
,   countyID  SMALLINT not NULL
,   PRIMARY KEY (stateID,countyID,TLID)
,   FOREIGN KEY (TLID) REFERENCES Chain
,   FOREIGN KEY (stateID,countyID) REFERENCES County
);


CREATE TABLE Feature (
    stateID     SMALLINT  not NULL
,   countyID    SMALLINT  not NULL
,   featID      INTEGER  not NULL
,   Name        VARCHAR(31)
,   Prefix  VARCHAR(2)
,   Type    VARCHAR(4)
,   Suffix  VARCHAR(2)
,   PRIMARY KEY (stateID,countyID,featID)
,   FOREIGN KEY (stateID,countyID) REFERENCES County
);

CREATE TABLE ChainName (
    stateID     SMALLINT  not NULL
,   countyID    SMALLINT  not NULL
,   featID      INTEGER   not NULL
,   TLID        INTEGER   not NULL
,   PRIMARY KEY (TLID,stateID,countyID,featID)
,   FOREIGN KEY (stateID,countyID,featID) REFERENCES Feature
,   FOREIGN KEY (TLID) REFERENCES Chain
);

CREATE TABLE AddressRange (
    TLID        INTEGER  not NULL
,   seq         SMALLINT not NULL
,   orientation CHAR(1)     not NULL
,   fraddr      VARCHAR(11)
,   toaddr      VARCHAR(11)
,   zip         INTEGER
,   zipplus     SMALLINT
,   PRIMARY KEY (tlid,seq,orientation)
,   FOREIGN KEY (TLID) REFERENCES Chain
);
