I currently have a products
table which stores product information (title, description, tags, manufacturer, etc.) for a website. I would like to store historical information of changes to this table - edits, rollbacks, deletes, etc. like StackOverflow does for posts.
I took a look at StackOverflow's schema for inspiration. It appears they store post history in a single denormalized PostHistory
table, which has an enum field PostHistoryTypeId
(FK to PostHistoryTypes
table) which specifies what kind of change it is and a single text field Text
which stores a serialized version of whatever data structure that specific type of history change needs (e.g. for the Initial Tags
type, it stores the tags in angle brackets - <postgres><foo><bar>
).
That approach made me a bit apprehensive, so I was wondering if I could come up with something different. Table inheritance caught my eye, so I was thinking of giving it a try, but I do know there are some weird caveats so was wondering if I'm missing anything. I'll share what I sketched out.
This is what my existing products
table looks like:
CREATE TABLE products (
id bigint NOT NULL,
title character varying(75),
description text,
tags text[],
specifications hstore,
manufacturer_id bigint,
visible boolean DEFAULT true NOT NULL
);
CREATE SEQUENCE products_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE products_id_seq OWNED BY products.id;
ALTER TABLE ONLY products ALTER COLUMN id SET DEFAULT nextval('products_id_seq'::regclass);
ALTER TABLE ONLY products
ADD CONSTRAINT products_pkey PRIMARY KEY (id);
I was thinking of creating a base history table like this:
/* parent table */
CREATE TABLE products_history (
id bigint NOT NULL,
product_id bigint NOT NULL,
created_at timestamp without time zone DEFAULT (now())::timestamp without time zone NOT NULL,
event_type TEXT NOT NULL, /* enum field of history type */
CHECK (false), /* prevent INSERT into the parent table */
FOREIGN KEY (product_id) REFERENCES products (id) /* is this even necessary since children don't inherit this? */
);
CREATE SEQUENCE products_history_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE products_history_id_seq OWNED BY products_history.id;
ALTER TABLE ONLY products_history ALTER COLUMN id SET DEFAULT nextval('products_history_id_seq'::regclass);
ALTER TABLE ONLY products_history
ADD CONSTRAINT products_history_pkey PRIMARY KEY (id);
And then create partitioned tables off of this that have additional columns needed by whatever particular history event the enum corresponds to:
/* purpose: the initial attributes of a newly-created product */
CREATE TABLE products_history_initial (
title character varying(75),
description text,
tags text[],
specifications hstore,
manufacturer_id bigint,
CONSTRAINT products_history_initial_pkey PRIMARY KEY (id), /* replicate parent PK */
FOREIGN KEY (product_id) REFERENCES products (id), /* repeat products FK */
CHECK (event_type = 'I')
) INHERITS (products_history);
/* purpose: edits to product attributes */
CREATE TABLE products_history_edit (
title character varying(75),
description text,
tags text[],
specifications hstore,
manufacturer_id bigint,
CONSTRAINT products_history_edit_pkey PRIMARY KEY (id), /* replicate parent PK */
FOREIGN KEY (product_id) REFERENCES products (id), /* repeat products FK */
CHECK (event_type = 'E')
) INHERITS (products_history);
/* purpose: rollback to a previous history event */
CREATE TABLE products_history_rollback (
products_history_id bigint,
CONSTRAINT products_history_rollback_pkey PRIMARY KEY (id), /* replicate parent PK */
FOREIGN KEY (product_id) REFERENCES products (id), /* repeat products FK */
FOREIGN KEY (products_history_id) REFERENCES products_history (id),
CHECK (event_type = 'R')
) INHERITS (products_history);
/* purpose: "delete" a product by setting visible=false */
CREATE TABLE products_history_delete (
CONSTRAINT products_history_delete_pkey PRIMARY KEY (id), /* replicate parent PK */
FOREIGN KEY (product_id) REFERENCES products (id), /* repeat products FK */
CHECK (event_type = 'D')
) INHERITS (products_history);
/* purpose: "undelete" a product by setting visible=true */
CREATE TABLE products_history_undelete (
CONSTRAINT products_history_undelete_pkey PRIMARY KEY (id), /* replicate parent PK */
FOREIGN KEY (product_id) REFERENCES products (id), /* repeat products FK */
CHECK (event_type = 'UD')
) INHERITS (products_history);
Is this a good approach? Any drawbacks I'm not considering? This will be for use by a Rails application (ActiveRecord ORM) if you're familiar with those (I haven't totally thought out all those related considerations yet - I like to start from a nice schema).
Perhaps I'm overcomplicating matters and it would be simpler to go with a denormalized table with a single JSON or hstore column similar to how StackOverflow does it, or something else entirely?