2

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?

  • 1
    Have you considered using the temporal_tables extension? github.com/arkhipov/temporal_tables – gsiems May 13 '15 at 1:05
  • How do you intend to query this historical data ? – Clément Prévost May 30 '15 at 18:58
  • This can work... but you'll have some hard time querying this structure. I'd recommend that you first write down which queries are you going to perform before deciding on structure. – joanolo Jan 4 '17 at 0:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.