My application has a Product and a Purchased table.
The Purchased table includes a full copy of the Product row at time of purchase, so that, even if a product name or description was changed, the purchase table maintains a historical record of the product details.
Purchased.name and Purchased.description fields are taking up too much space. On average, 1 of 30 strings in these fields are unique, and 29 of 30 are duplicates.
I would like to avoid creating a HistoricalProduct table, as many facets of Product change on a regular basis, and that table would grow quite large, too.
Is there a way to get PostgreSQL to manage deduplication of these variable character fields automatically for me?