Delete data or set display to "Inactive"

[morgion]

I've got a question about a best practice for storing historical / reference data in a database.

I'm creating an e-commerce system (online catalog, shopping cart, etc.) with a way of creating arbitrary groupings of products ("Gifts for Mother's Day", etc.) and selecting particular products as "featured".

In both cases, these items will be listed differently: promotional text, a catchy title, a larger or different picture. So I'll have entries in a "promotional_group" table and a "featured_product" table to store the additional text and picture references.

These items will also be temporary; probably not stay around longer than a month, in the case of promotional groups.

I want to keep track of user clicks on these promotional items and relate them specifically to sales. I could statically store the most important fragments of data and delete the temporary promotional group. Or, I could set a display status on the promotional group (setting it to "Inactive" when I'm done with it) and reference the promotional group id with the order, keeping -all- data.

Which option is the "best" way of doing it?

[DaveMaxwell]

If you're going to have other information dependant on a tables information, you don't want to delete it. It's best to make it inactive. The only time I would delete from a table like in this case is if there were no records associated with it on the other table.

[morgion]

If you're going to have other information dependant on a tables information, you don't want to delete it. It's best to make it inactive. The only time I would delete from a table like in this case is if there were no records associated with it on the other table.

Even if i could get by with just storing some of the information with the order, rather than referencing the complete item? What about long-term concerns about tables that just keep getting larger and are populated with mainly historical data? Won't that significan't impact query speeds?

(I'm partially playing devil's advocate now. These are the sorts of questions that dont' seem to be explicitly answered anywhere else. By the way, congrats on Member of the Month. )

[DaveMaxwell]

Even if i could get by with just storing some of the information with the order, rather than referencing the complete item? What about long-term concerns about tables that just keep getting larger and are populated with mainly historical data? Won't that significan't impact query speeds?

The query speed differences shouldn't be that significantly higher if the tables are created/indexed properly. I mean, how many promotional groups would you be talking about? 100? 1000? 10,000? Those numbers would represent some very SMALL spaces on the tables?

Would you NEVER re-use the promotions?

Your only other option is to move the records off to another table, but that would require you to know when and where things are located if you want to query on historical data. Makes it easier when it's on one location.

(I'm partially playing devil's advocate now. [img]images/smilies/wink.gif[/img] These are the sorts of questions that dont' seem to be explicitly answered anywhere else. By the way, congrats on Member of the Month. [img]images/smilies/smile.gif[/img])

I understand that, but sometimes I think people make a LOT of extra work for what amounts to a VERY minimal savings in terms of space and query times. Consistency in data storage makes queries more efficient and reduces the chances for mistakes.

And thanks!

[morgion]

I understand that, but sometimes I think people make a LOT of extra work for what amounts to a VERY minimal savings in terms of space and query times. Consistency in data storage makes queries more efficient and reduces the chances for mistakes.

And thanks!

That's what I was hoping to hear; I've solving similar dillemas with a "display status" flag. This is my first project using postgresql, and the first project of this size that I've had to tackle alone. I have a degree in MIS and have been creating web systems for a few years, but I'm always trying to find a better / more correct solution.

Anyone know of some strong postgresql communities on the web? I've been picking through http://phpbuilder.com and the Techdocs and mailing list archives on http://postgresql.org, but some more interactive feedback would be fun (and I don't want to cram my inbox with mailing lists ).

Articles / informative threads on advanced indexing and triggers/rules would be great. And yes, I am working my way through the entire postgresql online manual, so no RTFM cracks.

And thank you DaveMaxwell!