Postgresql Schema

Here is the actual SQL statements used to implement the PhysicalDatabaseDesign in Postgresql:

CREATE TABLE address (
        addressid                               serial PRIMARY KEY,
        addressline1                            varchar(100),
        addressline2                            varchar(100),
        addressline3                            varchar(100),
        city                                    varchar(20),
        state                                   char(2),
        zipcode                                 integer
);

CREATE TABLE property (
        propertyid                              serial PRIMARY KEY,
        taxid                                   varchar(100),
        dedicationdate                          date,
        sellingprice                            integer,
        addressid                               integer CONSTRAINT addresskey1 REFERENCES address(addressid)
);

CREATE TABLE propertyvalue (
        propertyvalueid                         serial PRIMARY KEY,
        assessmentdate                          date,
        value                                   integer,
        propertyid                              integer CONSTRAINT propertyidref3 REFERENCES property(propertyid)

);

CREATE TABLE insurancecompany (
        insurancecompanyid                      serial PRIMARY KEY,
        companyname                             varchar(120),
        companyid                               varchar(100),
        phonenumber                             varchar(20),
        faxnumber                               varchar(20),
        addressid                               integer CONSTRAINT addresskey2 REFERENCES address(addressid)
);

CREATE TABLE insurancedata (
        insurancedataid                         serial PRIMARY KEY,
        policynumber                            varchar(100),
        insurancecompanyid                      integer CONSTRAINT insurancecompanyidref REFERENCES insurancecompany(insurancecompanyid),
        rate                                    integer,
        policyeffectivedate                     date,
        policyexpirationdate                    date,
        paymentdate                             date,
        propertyid                              integer CONSTRAINT propertyref REFERENCES property(propertyid)
);

CREATE TABLE homeowner (
        homeownerid                             serial PRIMARY KEY,
        firstname                               varchar(20),
        middlename                              varchar(20),
        lastname                                varchar(20),
        birthdate                               date,
        spousefirstname                         varchar(20),
        spousemiddlename                        varchar(20),
        spouselastname                          varchar(20),
        spousebirthdate                         date,
        currentaddressid                        integer CONSTRAINT currentaddresskey1 REFERENCES address(addressid),
        moveindate                              date,
        propertyid                              integer CONSTRAINT propertyref REFERENCES property(propertyid)
);

CREATE TABLE mortgagedata (
        homeownerid                             integer PRIMARY KEY CONSTRAINT mortgagedatareference REFERENCES homeowner(homeownerid),
        settlementdate                          date,
        incomeasofsettlementdate                integer,
        expectedmonthlypayment                  integer,
        enddate                                 date,
        initialprinciplebalance                 integer,
        initialescrowbalance                    integer,
        principlebalance                        integer,
        escrowbalance                           integer
);

CREATE TABLE mortgagepayment (
        mortgagepaymentid                       serial PRIMARY KEY,
        homeownerid                             integer CONSTRAINT mortgagepaymentreference REFERENCES mortgagedata(homeownerid),
        invoicedate                             date,
        paymentduedate                          date,
        paymentreceiveddate                     date,
        amount                                  integer,
        amountpaid                              integer,
        amountappliedtoprinciple                integer,
        amountappliedtoescrow                   integer,
        paymenttype                             varchar(20),
        paymentnumber                           integer
);

CREATE TABLE arreagecredit (
   arreagecreditid                              serial PRIMARY KEY,
        amount                                  integer,
        appliedfromid                           integer CONSTRAINT appliedfromreference REFERENCES mortgagepayment(mortgagepaymentid),
        appliedtoid                             integer CONSTRAINT appliedtoreference REFERENCES mortgagepayment(mortgagepaymentid)
);

CREATE TABLE sweatequitybalance (
        homeownerid                             integer PRIMARY KEY CONSTRAINT sweatequitybalanceref REFERENCES homeowner(homeownerid),
        sweatequityhours                        integer
);

CREATE TABLE sweatequity (
        sweatequityid                           serial PRIMARY KEY,
        sedate                                  date,
        amount                                  integer,
        name                                    varchar(50),
        sweatequitybalanceid                    integer CONSTRAINT sweateqbalanceref REFERENCES sweatequitybalance(homeownerid)
);

CREATE TABLE taxdata (
        taxdataid                               serial PRIMARY KEY,
        duedate                                 date,
        datepaid                                date,
        amount                                  integer,
        taxtype                                 varchar(15) CONSTRAINT taxtypecheck CHECK ((taxtype = 'CITY') OR (taxtype='COUNTY') OR (taxtype='SCHOOL')),
        propertyid                              integer CONSTRAINT propertyidref2 REFERENCES property(propertyid)
);

CREATE TABLE homeownerchild (
        homeownerchildid                        serial PRIMARY KEY,
        firstname                               varchar(20),
        middlename                              varchar(20),
        lastname                                varchar(20),
        birthdate                               date,
        homeownerid                             integer CONSTRAINT homeownerchildref REFERENCES homeowner(homeownerid)
);

CREATE TABLE users (
        username                                varchar(50) PRIMARY KEY,
        userpassword                            varchar(50)
);

CREATE TABLE userroles (
        username                                varchar(50) CONSTRAINT usernamereference REFERENCES users(username),
        userrole                                varchar(50),
        PRIMARY KEY(username,userrole)
);