Basic Database model for real estate data in Postgres.

clean_db.sql

/* If you've messed up and need to remove all the tables. */
DROP TABLE listings.person   CASCADE;
DROP TABLE listings.property CASCADE;
DROP TABLE listings.agency   CASCADE;
DROP TABLE listings.agent    CASCADE;
DROP TABLE listings.listing  CASCADE;
DROP TABLE listings.offer    CASCADE;

fill_db.sql

/* Adding in a few random names. */
INSERT INTO listings.person(name, givenname, familyname, email)
  VALUES('Tang Yao Zu',      'Yao Zu',    'Tang',      '[email protected]'),
        ('Bandar Ramzi Ba',  'Bandar',    'Ba',        '[email protected]'),
        ('Reggie Schoen',    'Reggie',    'Schoen',    '[email protected]'),
        ('Una Suzuki',       'Una',       'Suzuki',    NULL),
        ('Kimberley Cada',   'Kimberly',  'Cada',      '[email protected]'),
        ('Kasha Sollers',    'Kasha',     'Sollers',   '[email protected]'),
        ('Francesco Abrev',  'Francesco', 'Abrev',     '[email protected]'),
        ('Marhta Niswander', 'Marhta',    'Niswander', '[email protected]');

INSERT INTO listings.property(streetaddress, addresslocality, postalcode,
                              floorsize, floorsizeunits, numberofrooms)
  VALUES('350 2033 TRIUMPH STREET',  'Vancouver', 'V5L4X3', 44.5, 'm^2', 3),
        ('1007 1003 PACIFIC STREET', 'Vancouver', 'V2N1E2', 35, 'm^2', 1),
        ('809 13380 108 AVENUE',     'Surrey', 'V3T0E7', 44.5, 'm^2', 4),
        ('642 SOUTHBOROUGH DRIVE', 'West Vancouver', 'V7S1M6', 1340, 'm^2', 4);

INSERT INTO listings.agency(agency, address, areaserved, contactpoint)
  VALUES('West Vancouver Super Agents', '115 COOLSTREET DRIVE', 'West Vancouver', '[email protected]'),
        ('Vancouver Really Real Estate', '3134 SOUTH CRUMMY STREET', 'Vancouver', '[email protected]'),
        ('Surry Apartment Hunters', '22-313 HORSEGIVENS CR', 'Surry', '[email protected]');

  /* Relates a person to a real estate agency */
INSERT INTO listings.agent(pid, agcyid)
  VALUES((SELECT pid    FROM listings.person AS pr WHERE pr.email LIKE '[email protected]'),
         (SELECT agcyid FROM listings.agency AS agcy WHERE agcy.agency LIKE 'West Vancouver Super Agents')),
        ((SELECT pid    FROM listings.person AS pr WHERE pr.email LIKE '[email protected]'),
         (SELECT agcyid FROM listings.agency AS agcy WHERE agcy.agency LIKE 'Vancouver Really Real Estate')),
        ((SELECT pid    FROM listings.person AS pr WHERE pr.email LIKE '[email protected]'),
         (SELECT agcyid FROM listings.agency AS agcy WHERE agcy.agency LIKE 'Surry Apartment Hunters'));

INSERT INTO listings.listing(agent, pid, propid, listingdate, listingprice, url, open)
VALUES((SELECT pid    FROM listings.person   AS pr WHERE pr.email LIKE '[email protected]'),
       (SELECT pid    FROM listings.person   AS pr WHERE pr.email LIKE '[email protected]'),
       (SELECT propid FROM listings.property AS pp WHERE pp.streetaddress LIKE '642 SOUTHBOROUGH DRIVE'),
       '2018-09-10', 3243323, NULL, True),
      ((SELECT pid    FROM listings.person   AS pr WHERE pr.email LIKE '[email protected]'),
       (SELECT pid    FROM listings.person   AS pr WHERE pr.email LIKE '[email protected]'),
       (SELECT propid FROM listings.property AS pp WHERE pp.streetaddress LIKE '1007 1003 PACIFIC STREET'),
       '2018-09-09', 1435214, NULL, True);

INSERT INTO listings.offer(agent, pid, listid,
                           validfrom,
                           pricevaliduntil,
                           price, notes)
  VALUES((SELECT pid    FROM listings.person AS pr WHERE pr.email LIKE '[email protected]'),
         (SELECT pid    FROM listings.person AS pr WHERE pr.email LIKE '[email protected]'),
         (SELECT listid FROM listings.listing AS ls
            WHERE ls.agent = (SELECT pid FROM listings.person AS pr WHERE pr.email LIKE '[email protected]')
            AND ls.pid = (SELECT pid FROM listings.person AS pr WHERE pr.email LIKE '[email protected]')),
         '2018-09-09', '2018-09-16',
         3000000,
         'Have requested that the owners fix the hole in the roof.'),
         ((SELECT pid    FROM listings.person AS pr WHERE pr.email LIKE '[email protected]'),
          (SELECT pid    FROM listings.person AS pr WHERE pr.email LIKE '[email protected]'),
          (SELECT listid FROM listings.listing AS ls
            WHERE ls.agent = (SELECT pid FROM listings.person AS pr WHERE pr.email LIKE '[email protected]')
            AND ls.pid = (SELECT pid FROM listings.person AS pr WHERE pr.email LIKE '[email protected]')),
          '2018-09-09', '2018-09-16',
          3100000, 'Have requested that the owners close the skylight.'),
         ((SELECT pid    FROM listings.person AS pr WHERE pr.email LIKE '[email protected]'),
          (SELECT pid    FROM listings.person AS pr WHERE pr.email LIKE '[email protected]'),
          (SELECT listid FROM listings.listing AS ls
            WHERE ls.agent = (SELECT pid FROM listings.person AS pr WHERE pr.email LIKE '[email protected]')
            AND ls.pid = (SELECT pid FROM listings.person AS pr WHERE pr.email LIKE '[email protected]')),
          '2018-09-09', '2018-09-16',
          1300000, 'The buyers have requested that the apartment be bigger.');

realestatedb.sql

/* Uncomment the next section if you are creating the 
   database and schema from the commandline. */

/*
CREATE DATABASE realestate
  WITH
    OWNER = postgres;

CREATE SCHEMA listings
  AUTHORIZATION postgres; 
*/

/* Based on schema https://schema.org/Person */
CREATE TABLE listings.person(  pid SERIAL             PRIMARY KEY,
                              name CHARACTER VARYING,
                         givenname CHARACTER VARYING,
                        familyname CHARACTER VARYING,
                    additionalname CHARACTER VARYING,
                             email CHARACTER VARYING);

/* Based on schema https://schema.org/House and https://schema.org/Accommodation*/
CREATE TABLE listings.property(propid SERIAL             PRIMARY KEY,
                        streetaddress CHARACTER VARYING,
                      addresslocality CHARACTER VARYING,
                           postalcode CHARACTER VARYING,
                            floorsize NUMERIC,
                       floorsizeunits CHARACTER VARYING DEFAULT 'm^2',
                        numberofrooms NUMERIC);

/* Based on https://schema.org/Organization */
CREATE TABLE listings.agency(agcyid SERIAL            PRIMARY KEY,
                             agency CHARACTER VARYING,
                            address CHARACTER VARYING,
                         areaserved CHARACTER VARYING,
                       contactpoint CHARACTER VARYING);

/* Relates a person to a real estate agency */
CREATE TABLE listings.agent(agtid SERIAL  PRIMARY KEY,
                              pid INTEGER REFERENCES listings.person,
                           agcyid INTEGER REFERENCES listings.agency);

CREATE TABLE listings.listing(listid SERIAL PRIMARY KEY,
                               agent INTEGER           REFERENCES listings.person,
                                 pid INTEGER           REFERENCES listings.person,
                              propid INTEGER           REFERENCES listings.property,
                         listingdate DATE,
                        listingprice NUMERIC,
                                 url CHARACTER VARYING,
                                open BOOLEAN           DEFAULT true);

/* From https://schema.org/Offer */
CREATE TABLE listings.offer(offerid SERIAL PRIMARY KEY,
                              agent INTEGER           REFERENCES listings.person,
                                pid INTEGER           REFERENCES listings.person,
                             listid INTEGER           REFERENCES listings.listing,
                          validfrom DATE,
                    pricevaliduntil DATE,
                              price NUMERIC CONSTRAINT get_positive_price CHECK (price > 0),
                      pricecurrency CHARACTER VARYING DEFAULT 'CAD',
                              notes TEXT,
                           accepted BOOLEAN           DEFAULT false);