SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast morgion's Avatar
    Join Date
    Jun 2002
    Location
    Fargo, ND
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Delete data or set display to "Inactive"

    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?
    Lord Morgion
    Paranoia is just reality on a finer scale. - Strange Days
    [ MacOS X | Mozilla | 1280 x 854 (15" PowerBook G4) | Millions ]
    [ www.ambientshadow.com | www.gerbilmechs.com | www.gogeek.org ]

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Enthusiast morgion's Avatar
    Join Date
    Jun 2002
    Location
    Fargo, ND
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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.
    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. )
    Lord Morgion
    Paranoia is just reality on a finer scale. - Strange Days
    [ MacOS X | Mozilla | 1280 x 854 (15" PowerBook G4) | Millions ]
    [ www.ambientshadow.com | www.gerbilmechs.com | www.gogeek.org ]

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by morgion
    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.

    Quote Originally Posted by morgion
    (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!
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    SitePoint Enthusiast morgion's Avatar
    Join Date
    Jun 2002
    Location
    Fargo, ND
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell
    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!
    Last edited by morgion; May 7, 2003 at 08:04.
    Lord Morgion
    Paranoia is just reality on a finer scale. - Strange Days
    [ MacOS X | Mozilla | 1280 x 854 (15" PowerBook G4) | Millions ]
    [ www.ambientshadow.com | www.gerbilmechs.com | www.gogeek.org ]


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •