I have a table in an SQL database for storing information about some business object that looks something like this (details changed so as not to give away what company I work for):
CREATE TABLE product ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, price MONEY NOT NULL, ... -- Some other stuff );
This data is updated "manually" (with a tool) and rarely changes.
I'm planning to cache the result of an expensive operation that we normally do at runtime in the DB, which maps 1-to-1 with the aforementioned table. The cache should be able to be cleared and rebuilt every few minutes.
My instinct is to keep this autogenerated data in a separate table, with a foreign key linking it to the manually updated table. Something like:
CREATE TABLE product_is_popular_cache ( id SERIAL PRIMARY KEY, product_id INTEGER NOT NULL REFERENCES product(id), is_popular BOOLEAN NOT NULL );
My question is: Is this just unnecessary complication? I could make it a new column on the original table, but mixing human-generated and machine-generated data smells to me. Perhaps there is a performance benefit. I could imagine that deleting and inserting many rows faster than scanning the
product table and changing only the relevant rows. Maybe they scale differently.