Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2010
    Posts
    53

    Storing Redundant Data

    I'm working on a system that stores orders - there are fields for recording the time an order was created, completed, processed, and shipped.

    Therefore it is possible to determine the status of an order by querying these fields

    However, i thought it would make queries much simpler if i had a status field enum(created, completed, processed, shipped) that is updated at the same time as the time fields

    Instinctively it felt a bit wrong to store data that could be calculated from other fields but i think it would make like much easier...

    any thoughts would be gratefully recieved..

    ta
    j

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, go ahead and add the status column -- but don't use ENUM, which is the spawn of the devil, use TINYINT or CHAR(1), and link it via a foreign key to a status table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    53
    many thanks, rudy, is enum really so bad? using a foreign key to an external table means harder work glueing everything back together. with the enums i can see whats what from the raw table without having to remeber 0 = this, 1 = that, etc.
    Last edited by jx12345; 07-30-11 at 07:46.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jx12345 View Post
    many thanks, rudy, is enum really so bad? using a foreign key to an external table means harder work glueing everything back together. with the enums i can see whats what from the raw table without having to remeber 0 = this, 1 = that, etc.
    yes, ENUM is evil

    so okay, instead of using a TINYINT or CHAR(1) as the foreign key, use VARCHAR(12) as the foreign key, and store the status name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2010
    Posts
    53
    ok, so even if i dont store anything in the related status table other than the primary key ie. the status_name, it is still a valid struture because the status table is used to validate status entries in the orders table???

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, perfectly valid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2010
    Posts
    53
    many thanks!

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Quote Originally Posted by r937 View Post
    yes, go ahead and add the status column -- but don't use ENUM, which is the spawn of the devil, use TINYINT or CHAR(1), and link it via a foreign key to a status table
    I'd prefer a CHECK constraint as long as the status codes need not be translated through SQL (e.g. through a JOIN)

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    one of the problems with a CHECK constraint, as well as with ENUM, is that it requires the DBA to make a change to the production schema when you want to add a new status value

    if you've ever worked in a large organization, where the bureaucracy requires you to fill out a project plan and get three levels of management signature just to ask the DBA to pick his nose, you'll agree that this is a non-starter

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2011
    Posts
    14

    A general comment!

    The question looks is already answered.
    I just want to add a very general rule for those who thinks very academic.
    Sometimes you need to add a redundant field to your tables because you want to code easier and to maintain your code easier.
    Remember that your code writing is 10-15% of the life cycle but maintenance is 85-90% (and even more). So, the easier the code structure is, the easier would be the maintenance!
    Good luck!

Posting Permissions

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