Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    looking for best practices solution (aka help settle a bet)

    Ok so there has been some discussion at work regarding the best way to tackle this solution.

    For a given Person...

    Option A
    PERSON
    *PERSON_ID INT
    PERMISSION_START_DATE SMALLDATETIME --START DATE PERMISSION CAN START
    PERMISSION_END_DATE SMALLDATETIME --END DATE WHEN PERMISSION IS NO LONGER VALID
    :
    About 20 other columns unrelated to this question

    Option B
    PERSON
    *PERSON_ID INT
    PERMISSION_STATUS TINYINT -- 0: INACTIVE | 1: ACTIVE - ALWAYS | 2: ACTIVE - DATEBOUND (USE TWO DATE COLUMNS BELOW)
    PERMISSION_START_DATE SMALLDATETIME
    PERMISSION_END_DATE SMALLDATETIME
    :
    About 20 other columns unrelated to this question

    Option C
    {something different} --perhaps Option B but normalized

    My peer wants to set the date values in Option A by setting start = 0 and end = 65535 (which I guess is min_date '1/1/1900' and max_date '6/6/2079')

    I am opting for Option B since I don't want garbage dates cluttering our system since I think it reflects poorly when reports are generated or when users need to set explicit dates since we either show them these bogus dates or the application needs to interpret the bogus dates as an Always On status.

    Thoughts?
    Last edited by Gagnon; 05-21-08 at 11:52.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your peer thinks 65535 is a valid SMALLDATETIME? it's a valid INTEGER, that's all

    if a "thing" (you didn't mention what this table is about) can be inactive, always active, or datebound, then option A fails because it doesn't allow for those statuses

    what do you do about a thing which has several valid datebound ranges?


    by the way, a very common and practical way to record "bogus dates" is with NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937
    by the way, a very common and practical way to record "bogus dates" is with NULL
    Gasp! Sir, I shall report you to Fabian!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you can find him
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2005
    Posts
    319
    Not sure if it matters but the "thing" in question is a person record, this is a particular type of person - again should be mostly irrelevant.

    I will make it less generic in the original post (assuming I can still edit it).

  6. #6
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by r937
    your peer thinks 65535 is a valid SMALLDATETIME? it's a valid INTEGER, that's all
    Well in MSFT SQL Server it casts it to the dates I mentioned earlier.

    So everyone is on board that storing MIN/MAX dates in date columns is a bad implementation/design, correct?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Gagnon
    Well in MSFT SQL Server it casts it to the dates I mentioned earlier.
    you did not mention this earlier
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Gagnon
    So everyone is on board that storing MIN/MAX dates in date columns is a bad implementation/design, correct?
    no, i'm not
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by r937
    you did not mention this earlier
    try reading my post like this:

    [beginning sentence] - to the dates I mentioned earlier

  10. #10
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by r937
    no, i'm not
    So you often (or even sometimes) have date columns that show dates such as:

    1/1/1900

    and

    6/6/2079?

    Do you interpret these differently (translate the values to something more meaningful) on the UI or in reports?
    Last edited by Gagnon; 05-21-08 at 12:45.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937
    if you can find him
    Code:
    begin tran
    set @FPascal = null
    commit
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Why have a status when you can easily find out whether it's active or not based on the start and end dates?
    George
    Home | Blog

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Why have a status when you can easily find out whether it's active or not based on the start and end dates?
    because you need a status to distinguish NULL/NULL meaning inactive, versus NULL/NULL meaning active always
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Gagnon
    So you often (or even sometimes) have date columns that show dates such as:

    1/1/1900

    and

    6/6/2079?
    i would never, never (never!!!) have date columns that show dates in that format

    sheesh!!!

    BTW, my "no, i'm not" was in response to "is everyone on board that..."

    try reading my posts from [quoted stuff] - [end]

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

  15. #15
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by r937
    i would never, never (never!!!) have date columns that show dates in that format

    sheesh!!!

    BTW, my "no, i'm not" was in response to "is everyone on board that..."

    try reading my posts from [quoted stuff] - [end]

    eh I guess someone misinterpreted a double negative

Posting Permissions

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