Experiments with storing typed attributes in Postgres

Column References.sql

CREATE TABLE attribute_types (
  attribute_type_id SERIAL PRIMARY KEY,
  type TEXT NOT NULL,
  value TEXT NOT NULL
);

INSERT INTO attribute_types(type, value) VALUES ('Kitchen', 'Deluxe');
INSERT INTO attribute_types(type, value) VALUES ('Kitchen', 'Small');

INSERT INTO attribute_types(type, value) VALUES ('Windows', 'Clean');
INSERT INTO attribute_types(type, value) VALUES ('Windows', 'Dirty');

INSERT INTO attribute_types(type, value) VALUES ('Light', 'Good');
INSERT INTO attribute_types(type, value) VALUES ('Light', 'Bad');

CREATE OR REPLACE FUNCTION validate(VARCHAR, VARCHAR) RETURNS boolean AS $$
SELECT EXISTS (
    SELECT 1
    FROM attribute_types
    WHERE type = $1 AND value = $2
)
$$ language sql;

CREATE TABLE apartments (
  apartment_id SERIAL PRIMARY KEY,
  sqft INT,
  kitchen TEXT CONSTRAINT chk_kitchen CHECK (validate('Kitchen', kitchen)),
  windows TEXT CONSTRAINT chk_windows CHECK (validate('Windows', windows)),
  light TEXT CONSTRAINT chk_light CHECK (validate('Light', light))
);
                                         
                                         
-------------------------------------------
                                         
INSERT INTO apartments(sqft, kitchen, windows, light) VALUES (100, 'Deluxe', 'Clean', 'Good');

SELECT * FROM attribute_types;
SELECT * FROM apartments;

hstore.sql

CREATE EXTENSION hstore;


CREATE TABLE attributes (
  attribute_id SERIAL,
  type VARCHAR,
  val VARCHAR,
  
  PRIMARY KEY(attribute_id)
);


CREATE OR REPLACE FUNCTION validate(varchar, varchar) RETURNS boolean AS $$
SELECT EXISTS (
    SELECT 1
    FROM attributes
    WHERE type = $1 AND attribute_id = $2::int
)
$$ language sql;
                         
                         
CREATE TABLE apartments (
 attr hstore,
  
 CONSTRAINT chk_light_attr check (validate('Light', attr -> 'Light')),
 CONSTRAINT chk_kitchen_attr check (validate('Kitchen', attr -> 'Kitchen'))
);
                                  
INSERT INTO attributes(attribute_id, type, val) VALUES (1, 'Light', 'Positive');
INSERT INTO attributes(attribute_id, type, val) VALUES (2, 'Light', 'Negative');
INSERT INTO attributes(attribute_id, type, val) VALUES (3, 'Light', 'Neutral');

INSERT INTO attributes(attribute_id, type, val) VALUES (4, 'Kitchen', 'Deluxe');
INSERT INTO attributes(attribute_id, type, val) VALUES (5, 'Kitchen', 'Kitchenette');
INSERT INTO attributes(attribute_id, type, val) VALUES (6, 'Kitchen', 'Hot pot');
                                    
-------------------------------------------------------
                                    
insert into apartments (attr) values (
  ('
   "Light" => 1,
   "Kitchen" => 4,
   "SQFT" => 500
  ')
);

select * from (
select (each(attr)).key, (each(attr)).value from apartments
) as t
                          
left join attributes on t.value::int = attributes.attribute_id

JSONB Typing.sql

CREATE TABLE attribute_types (
  apartment varchar[]
);

CREATE TABLE attributes (
  attribute_id SERIAL PRIMARY KEY,
  home_id INT,
  name VARCHAR,
  value JSONB
);

INSERT INTO attribute_types(apartment) values ('{"kitchen", "light"}');

INSERT INTO attributes(name, value) VALUES ('kitchen', to_jsonb('Deluxe'::VARCHAR));
INSERT INTO attributes(name, value) VALUES ('kitchen', to_jsonb('Kitchenette'::TEXT));;

INSERT INTO attributes(name, value) VALUES ('light', to_jsonb('Positive'::TEXT));
INSERT INTO attributes(name, value) VALUES ('light', to_jsonb('Negative'::TEXT));

INSERT INTO attributes(name, value) VALUES ('has_balcony', to_jsonb(true));
INSERT INTO attributes(name, value) VALUES ('has_balcony', to_jsonb(false));

INSERT INTO attributes(name, value) VALUES ('num_bathrooms', to_jsonb(1));
INSERT INTO attributes(name, value) VALUES ('num_bathrooms', to_jsonb(2));

CREATE TABLE attribute_mods (
  attribute_mod_id SERIAL PRIMARY KEY,
  attribute_id INTEGER REFERENCES attributes,
  mod FLOAT,
  home_id INTEGER,
  type VARCHAR
);

INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (1, 2.0, 1, 'coliving');
INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (2, 1.0, 1, 'coliving');;
INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (1, 1.5, 1, 'traditional');
INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (2, 0.5, 1, 'traditional');;

INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (3, 1.0, 1, 'coliving');
INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (4, 0.0, 1, 'coliving');
INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (3, 1.5, 1, 'traditional');
INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (4, 0.5, 1, 'traditional');

INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (5, 2.0, 1, 'coliving');
INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (6, 0.0, 1, 'coliving');
INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (5, 1.5, 1, 'traditional');
INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (6, 0.0, 1, 'traditional');

INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (7, 1.0, 1, 'coliving');
INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (8, 2.0, 1, 'coliving');
INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (7, 1.5, 1, 'traditional');
INSERT INTO attribute_mods(attribute_id, mod, home_id, type) VALUES (8, 2.5, 1, 'traditional');

CREATE TABLE apartments (
  apartment_id SERIAL PRIMARY KEY,
  name VARCHAR NOT NULL,
  type VARCHAR,
  home_id INTEGER
);

INSERT INTO apartments(name, type, home_id) VALUES ('apartment 1', 'coliving', 1);

CREATE TABLE apartment_attributes (
  apartment_attribute_id SERIAL PRIMARY KEY,
  apartment_id INTEGER REFERENCES apartments,
  attribute_id INTEGER REFERENCES attributes
);

INSERT INTO apartment_attributes(apartment_id, attribute_id) VALUES (1, 1);
INSERT INTO apartment_attributes(apartment_id, attribute_id) VALUES (1, 3);
INSERT INTO apartment_attributes(apartment_id, attribute_id) VALUES (1, 5);
INSERT INTO apartment_attributes(apartment_id, attribute_id) VALUES (1, 7);

References with validation.sql

CREATE TABLE attributes (
  attribute_id SERIAL,
  type VARCHAR,
  val VARCHAR,
  
  PRIMARY KEY(attribute_id)
);

CREATE OR REPLACE FUNCTION validate_attribute_type(varchar, int) RETURNS boolean AS $$
SELECT EXISTS (
    SELECT 1
    FROM attributes
    WHERE type = $1 AND attribute_id = $2
)
$$ language sql;

CREATE TABLE apartment_attrs (
  apartment_id INTEGER,
  light_attribute_id INTEGER,
  kitchen_attribute_id INTEGER,
  sqft VARCHAR,

  CONSTRAINT chk_light_attr check (validate_attribute_type('Light', light_attribute_id)),
  CONSTRAINT chk_kitchen_attr check (validate_attribute_type('Kitchen', kitchen_attribute_id)),
  UNIQUE(apartment_id)
);
                                     
CREATE TABLE apartment_attr_modifiers (
  apartment_id INTEGER,
  attribute_id INTEGER,
  val FLOAT,

  UNIQUE(apartment_id, attribute_id)
);
                                   
INSERT INTO attributes(attribute_id, type, val) VALUES (1, 'Light', 'Positive');
INSERT INTO attributes(attribute_id, type, val) VALUES (2, 'Light', 'Negative');
INSERT INTO attributes(attribute_id, type, val) VALUES (3, 'Light', 'Neutral');

INSERT INTO attributes(attribute_id, type, val) VALUES (4, 'Kitchen', 'Deluxe');
INSERT INTO attributes(attribute_id, type, val) VALUES (5, 'Kitchen', 'Kitchenette');
INSERT INTO attributes(attribute_id, type, val) VALUES (6, 'Kitchen', 'Hot pot');

INSERT INTO apartment_attrs(apartment_id, light_attribute_id, kitchen_attribute_id, sqft) VALUES (1, 2, 5, '1000');                           

Split attribute tables.sql

CREATE TABLE space_attrs (
  space_attr_id SERIAL,
  space_attr_light VARCHAR,
  space_attr_sqft VARCHAR,
  
  PRIMARY KEY(space_attr_id)
);

CREATE TABLE space_bed_attrs (
  space_bed_attr_id SERIAL,
  space_bed_attr_size VARCHAR,
  
  PRIMARY KEY(space_bed_attr_id)
);

CREATE TABLE space_common_attrs (
  space_common_attr_id SERIAL,
  space_common_attr_bathroom_count VARCHAR,
  
  PRIMARY KEY(space_common_attr_id)
);

CREATE TABLE suites (
  suite_id SERIAL,
  apartment_name VARCHAR,
  apartment_attr_id INTEGER,
  apartment_common_space_attr_id INTEGER,
  
  PRIMARY KEY(suite_id),
  FOREIGN KEY (apartment_attr_id) REFERENCES space_attrs (space_attr_id),
  FOREIGN KEY (apartment_common_space_attr_id) REFERENCES space_common_attrs (space_common_attr_id)
);
  
CREATE TABLE rooms (
  room_id SERIAL,
  room_name VARCHAR,
  suite_id INTEGER,
  room_attr_id INTEGER,
  bed_attr_id INTEGER,
  
  PRIMARY KEY(room_id),
  FOREIGN KEY (suite_id) REFERENCES suites (suite_id),
  FOREIGN KEY (room_attr_id) REFERENCES space_attrs (space_attr_id),
  FOREIGN KEY (bed_attr_id) REFERENCES space_bed_attrs (space_bed_attr_id)
);

CREATE TABLE singles (
  single_id SERIAL,
  apartment_name VARCHAR,
  apartment_attr_id INTEGER,
  apartment_common_space_attr_id INTEGER,
  bed_attr_id INTEGER,
  
  PRIMARY KEY(single_id),
  FOREIGN KEY (apartment_attr_id) REFERENCES space_attrs (space_attr_id),
  FOREIGN KEY (bed_attr_id) REFERENCES space_bed_attrs (space_bed_attr_id),
  FOREIGN KEY (apartment_common_space_attr_id) REFERENCES space_common_attrs (space_common_attr_id)
);

CREATE TABLE rentable_spaces (
  rentable_space_id SERIAL,
  suite_id INTEGER DEFAULT NULL,
  room_id INTEGER DEFAULT NULL,
  single_id INTEGER DEFAULT NULL,
  
  PRIMARY KEY (rentable_space_id),
  FOREIGN KEY (suite_id) REFERENCES suites (suite_id),
  FOREIGN KEY (room_id) REFERENCES rooms (room_id),
  FOREIGN KEY (single_id) REFERENCES singles (single_id),
  UNIQUE (suite_id, room_id, single_id),
  CONSTRAINT space_type CHECK (
    (
      (suite_id IS NOT NULL)::integer +
      (room_id IS NOT NULL)::integer +
      (single_id IS NOT NULL)::integer
    ) = 1
  )
);

-- Traditional
INSERT INTO space_attrs (space_attr_id, space_attr_light, space_attr_sqft) VALUES (1, 'Great light', '1000 sqft'); -- Apartment
INSERT INTO space_attrs (space_attr_id, space_attr_light, space_attr_sqft) VALUES (4, 'Great light', '500 sqft'); -- Bed 1
INSERT INTO space_bed_attrs (space_bed_attr_id, space_bed_attr_size) VALUES (1, 'big room');
INSERT INTO space_attrs (space_attr_id, space_attr_light, space_attr_sqft) VALUES (5, 'Great light', '400 sqft'); -- Bed 2
INSERT INTO space_bed_attrs (space_bed_attr_id, space_bed_attr_size) VALUES (2, 'big room');
INSERT INTO space_common_attrs (space_common_attr_id, space_common_attr_bathroom_count) VALUES (1, 1);

-- Co living
INSERT INTO space_attrs (space_attr_id, space_attr_light, space_attr_sqft) VALUES (2, 'Poor light', '800 sqft'); -- Apartment
INSERT INTO space_attrs (space_attr_id, space_attr_light, space_attr_sqft) VALUES (6, 'Great light', '150 sqft'); -- Bed 1
INSERT INTO space_bed_attrs (space_bed_attr_id, space_bed_attr_size) VALUES (3, 'small room');
INSERT INTO space_attrs (space_attr_id, space_attr_light, space_attr_sqft) VALUES (7, 'Great light', '175 sqft'); -- Bed 2
INSERT INTO space_bed_attrs (space_bed_attr_id, space_bed_attr_size) VALUES (4, 'small room');
INSERT INTO space_attrs (space_attr_id, space_attr_light, space_attr_sqft) VALUES (8, 'Great light', '200 sqft'); -- Bed 3
INSERT INTO space_bed_attrs (space_bed_attr_id, space_bed_attr_size) VALUES (5, 'medium room');
INSERT INTO space_common_attrs (space_common_attr_id, space_common_attr_bathroom_count) VALUES(2, 2);

-- Micro
INSERT INTO space_attrs (space_attr_id, space_attr_light, space_attr_sqft) VALUES (3, 'Medium light', '200 sqft'); -- Apartment
INSERT INTO space_bed_attrs (space_bed_attr_id, space_bed_attr_size) VALUES (6, 'medium room');
INSERT INTO space_common_attrs (space_common_attr_id, space_common_attr_bathroom_count) VALUES(3, 1);


INSERT INTO suites (suite_id, apartment_name, apartment_attr_id, apartment_common_space_attr_id) VALUES (1, 'Traditional apartment', 1, 1);
INSERT INTO suites (suite_id, apartment_name, apartment_attr_id, apartment_common_space_attr_id) VALUES (2, 'Co-living apartment', 2, 2);
INSERT INTO singles (single_id, apartment_name, apartment_attr_id, apartment_common_space_attr_id, bed_attr_id) VALUES (1, 'Micro apartment', 3, 3, 6);

INSERT INTO rooms (room_id, suite_id, room_name, room_attr_id, bed_attr_id) VALUES(1, 1, 'Traditional RM 1', 4, 1);
INSERT INTO rooms (room_id, suite_id, room_name, room_attr_id, bed_attr_id) VALUES(2, 1, 'Traditional RM 2', 5, 2);

INSERT INTO rooms (room_id, suite_id, room_name, room_attr_id, bed_attr_id) VALUES(3, 2, 'Co-living RM 1', 6, 3);
INSERT INTO rooms (room_id, suite_id, room_name, room_attr_id, bed_attr_id) VALUES(4, 2, 'Co-living RM 2', 7, 4);
INSERT INTO rooms (room_id, suite_id, room_name, room_attr_id, bed_attr_id) VALUES(5, 2, 'Co-living RM 3', 8, 5);

INSERT INTO rentable_spaces (suite_id) VALUES (1);
INSERT INTO rentable_spaces (room_id) VALUES (3);
INSERT INTO rentable_spaces (room_id) VALUES (4);
INSERT INTO rentable_spaces (room_id) VALUES (5);
INSERT INTO rentable_spaces (single_id) VALUES (1);

-----------------------------------------------------

SELECT 
	rs.*,
    apartment_attr_id,
    apartment_common_space_attr_id,
    room_attr_id,
    bed_attr_id
FROM
	rentable_spaces AS rs
   
LEFT JOIN LATERAL (
  	-- Traditional Suite
	SELECT
  		apartment_attr_id,
  		apartment_common_space_attr_id, 
  		null AS room_attr_id,
  		null AS bed_attr_id, 
  		null AS room_id,
  		suite_id,
  		null::integer AS single_id
  	FROM suites
  	WHERE rs.suite_id = suites.suite_id
  
  	UNION
  
  	-- Co-living
  	SELECT 
  		apartment_attr_id,
    	apartment_common_space_attr_id, 
  		room_attr_id,
  		bed_attr_id, 
  		rooms.room_id,
  		null AS suite_id,
  		null AS single_id
  	FROM rooms
  	LEFT JOIN suites ON rooms.room_id = rs.room_id
  	WHERE rs.room_id = rooms.room_id 
  
  	UNION
  
  	-- Single
  	SELECT 
  		apartment_attr_id,
    	apartment_common_space_attr_id, 
  		null as room_attr_id,
  		bed_attr_id, 
  		null AS room_id,
  		null AS suite_id,
  		single_id
  	FROM singles
  	WHERE rs.single_id = singles.single_id 
) AS t ON t.suite_id = rs.suite_id OR t.room_id = rs.room_id OR t.single_id = rs.single_id

Type Casting.sql

CREATE TABLE apartments (
  apartment_id SERIAL,
  attrs JSONB,
  
  PRIMARY KEY (apartment_id)
);

CREATE TABLE apartment_data (
  apartment_data_id SERIAL,
  attrs JSONB,
  
  PRIMARY KEY (apartment_data_id)
);

CREATE OR REPLACE FUNCTION coerce_attrs(JSONB) RETURNS JSONB AS $$
	WITH raw_data AS (
    	SELECT key,
        	CASE WHEN value ~ E'^\\d+$' THEN to_jsonb(value::INTEGER)
        	WHEN value ~ 'true|false' THEN to_jsonb(value::BOOLEAN)
        	ELSE to_jsonb(value)
        	END AS value
    	FROM jsonb_each_text($1)
	)

	SELECT jsonb_object_agg(key, value)
	FROM raw_data
$$ LANGUAGE SQL;

-- TODO: Convert to trigger and eliminate the apartment_data table
CREATE OR REPLACE RULE coerce_attrs AS ON INSERT TO apartments
    DO INSTEAD INSERT INTO apartment_data (attrs) VALUES (
      coerce_attrs(NEW.attrs)
    );

INSERT INTO apartments (attrs) VALUES (
  '{"Windows": "true", "Light": "Neutral", "Kitchen": "Hot pot", "Sqft": "100"}'
);

--------------------------------------------------------

SELECT
	apartment_id,
    (jsonb_each(attrs)).key,
    (jsonb_each(attrs)).value,
    jsonb_typeof((jsonb_each(attrs)).value) AS type
FROM
	apartments;

User defined type.sql

CREATE TYPE apartment_attributes_type AS (
    sqft INT,
    kitchen VARCHAR
);

CREATE TABLE attributes (
  attribute_id SERIAL,
  type VARCHAR,
  val VARCHAR,
  
  PRIMARY KEY(attribute_id)
);

//
CREATE OR REPLACE FUNCTION validate(attrs apartment_attributes_type) RETURNS boolean LANGUAGE plpgsql AS $$
declare 
  attr_exist bool;
  in_attributes bool;
  attr_name varchar;
  attr_value varchar;
BEGIN
    FOR attr_name IN
        SELECT attname FROM pg_attribute WHERE attrelid = (SELECT typrelid FROM pg_type WHERE typname = 'apartment_attributes_type')
    LOOP
      EXECUTE 'SELECT $1.' || quote_ident(attr_name) USING attrs INTO attr_value;
      EXECUTE 'SELECT EXISTS(SELECT 1 FROM attributes WHERE type = ''' || attr_name || ''') ' INTO in_attributes;

      IF in_attributes THEN
         EXECUTE 'SELECT EXISTS(SELECT 1 FROM attributes WHERE type = ''' || attr_name || ''' AND val = ''' || attr_value || ''') ' INTO attr_exist;
         IF NOT attr_exist THEN
           RAISE EXCEPTION 'Invalid value for %', attr_name;
           RETURN attr_exist;
         END IF;
      END IF;
    END LOOP;

    RETURN true;
END;
$$;
//

CREATE TABLE apartment_attributes (
  attribute_id SERIAL,
  attrs apartment_attributes_type,
  
  PRIMARY KEY(attribute_id),
  
  CONSTRAINT chk_attrs check (validate(attrs))
);

INSERT INTO attributes(attribute_id, type, val) VALUES (4, 'kitchen', 'Deluxe');
INSERT INTO attributes(attribute_id, type, val) VALUES (5, 'kitchen', 'Kitchenette');
INSERT INTO attributes(attribute_id, type, val) VALUES (6, 'kitchen', 'Hot pot'); 

 -----------------------------------------------------
                              
INSERT INTO apartment_attributes(attrs) values (
  json_populate_record(null::apartment_attributes_type, '{"kitchen": "Deluxe", "sqft1": 100}')
);

INSERT INTO apartment_attributes(attrs) values (
  json_populate_record(null::apartment_attributes_type, '{"kitchen1": "Deluxe1", "sqft1": 100}')
);

Wide Reference.sql

CREATE TABLE attributes (
  kitchen VARCHAR,
  balcony BOOLEAN,
  view VARCHAR,
  
  UNIQUE(kitchen),
  UNIQUE(balcony),
  UNIQUE(view),
  
  CONSTRAINT space_type CHECK (
    (
      (kitchen IS NOT NULL)::integer +
      (balcony IS NOT NULL)::integer +
      (view IS NOT NULL)::integer
    ) = 1
  )
);

CREATE TABLE apartments (
  apartment_id SERIAL,
  home_id INTEGER,
  
  PRIMARY KEY (apartment_id)
);

CREATE TABLE apartment_attributes (
  apartment_id INTEGER,
  kitchen VARCHAR,
  balcony BOOLEAN,
  
  PRIMARY KEY (apartment_id),
  FOREIGN KEY (kitchen) REFERENCES attributes (kitchen),
  FOREIGN KEY (balcony) REFERENCES attributes (balcony)
);

CREATE TABLE mods (
    home_id INTEGER,
    attribute_name VARCHAR,
    attribute_value VARCHAR,
    modifier FLOAT,
  
  
  UNIQUE(home_id, attribute_name, attribute_value)
);

INSERT INTO attributes (kitchen) VALUES ('Deluxe');
INSERT INTO attributes (kitchen) VALUES ('Kitchenette');
INSERT INTO attributes (balcony) VALUES ('Yes');
INSERT INTO attributes (balcony) VALUES ('No');


-----------------------------------------------

INSERT INTO apartments (apartment_id, home_id) VALUES (1, 1);
INSERT INTO apartment_attributes (apartment_id, kitchen, balcony) VALUES (1, 'Deluxe', true);

INSERT INTO mods(home_id, attribute_name, attribute_value, modifier) VALUES (1, 'kitchen', 'Deluxe', '1.0');
INSERT INTO mods(home_id, attribute_name, attribute_value, modifier) VALUES (1, 'kitchen', 'Kitchenette', '-0.5');
INSERT INTO mods(home_id, attribute_name, attribute_value, modifier) VALUES (1, 'balcony', 'true', '1.0');
INSERT INTO mods(home_id, attribute_name, attribute_value, modifier) VALUES (1, 'balcony', 'false', '0');

select * from attributes;
select * from apartments;

SELECT mods.*
FROM information_schema.columns
LEFT JOIN mods ON column_name = attribute_name::VARCHAR
WHERE 
  table_name  = 'apartment_attributes' AND column_name <> 'apartment_id';
  
  SELECT mods.*
FROM information_schema.columns
LEFT JOIN apartments ON column_name = attribute_name::VARCHAR
WHERE 
  table_name  = 'apartment_attributes' AND column_name <> 'apartment_id';