CODEWITHSUNDEEP
database-design
starmandeluxe
starmandeluxe

Reputation: 2538

Basic DB Design: Keeping a "History" Table

A previous developer before I arrived created a database table that keeps a history list of search queries from a certain web app, subsequently loading the last saved search for that user back in upon loading up the page.

The problem is, this search function sees high volume of use and every time a search is made a new row is written to the table, effectively ad infinitum (it seems that old searches are not cleaned up either). So far there are more than 300k entries in this table and counting.

My question is, is this a safe design to do this? And what is a better alternative? I am concerned about limits, performance, and necessity of having something like this exist.

Upvotes: 0

Views: 266

Answers (1)

Gilbert Le Blanc
Gilbert Le Blanc

Reputation: 51445

Is this a safe design to do this?

Sure. As the table is presently constituted, you can do a query and return the 10 most popular searches ever, as well as the 10 most popular searches in the last 6 months.

And what is a better alternative?

You could put a unique index on the search query text if you want to eliminate duplicates.

I am concerned about limits, performance, and necessity of having something like this exist.

Only your organization can determine the necessity. As far as limits and performance, I've worked with data warehouses that added 2 million rows a day. You didn't say which relational database you're concerned about, but most that exist today can handle tables with trillions of rows.

Can you explain why you think it's a good design to not have cleanup on an "infinitely" growing table?

We'll assume there's an organizational reason for an infinitely growing table. To give one example, I worked on a system where every query, every addition, update, or deletion had to be logged. And we had to keep this data log online, forever. It was required by law.

We just made sure that the history files had adequate disk space. We never once thought about the maximum number of rows a table could hold. The relational database was DB2.

Upvotes: 2

Related Questions