SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Twitter - @CarlBeckel busy's Avatar
    Join Date
    May 2004
    Location
    Richmond, VA, USA
    Posts
    819
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Quick normalization question

    I have a table "events". Because of how the two types are handled, I need to flag them as upcoming events and past events. (Can't just use the current date, because eventually upcoming events will become past events, but I don't want those to show up in that category.)

    Would it be better to use a table with a true/false flag in table "events" or add another table "upcoming_events" that would list only the ids of the events in question?

    Is making another table making things more complicated than they need to be, or just following good practices?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busy View Post
    Is making another table making things more complicated than they need to be
    yes

    could you explain again why you cannot use the current date to determine whether an event is upcoming or not?

    that's how everybody else does it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Twitter - @CarlBeckel busy's Avatar
    Join Date
    May 2004
    Location
    Richmond, VA, USA
    Posts
    819
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    could you explain again why you cannot use the current date to determine whether an event is upcoming or not?
    I am, but I still have to flag the event.

    My client wants the upcoming events to be used one way, and the past events to be used another way. So let's say that I add an upcoming event for tomorrow. If I use only the date, then in two days the event will appear in the past event list, which is not supposed to happen. (It needs to disappear)

    I don't like it, so I'm trying to include the option of flagging the upcoming events, and allowing for removal of the flag so they can show up as past events as well. That's why I'm not just separating upcoming and past events into two tables. (Basically I'm giving the client what he's asking for but giving him the option to come around to my way of doing things when he sees it works better.)

    I have also wondered about this when flagging posts as hidden for example, or users as suspended. For a boolean value like this it's better to just add another column rather than a table? That makes sense if it's an even mix of true/false, but what if only about 1% or so would be flagged true? Would it still be better to use a column full of nulls with just a couple trues?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busy View Post
    My client wants the upcoming events to be used one way, and the past events to be used another way.
    so use a WHERE clause in the queries which retrieve events

    in fact you can even create two views for this purpose, called "past_events" and "upcoming_events"

    and you will never have to manipulate a flag

    do you honestly want to go into the table every day to reset flags?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Twitter - @CarlBeckel busy's Avatar
    Join Date
    May 2004
    Location
    Richmond, VA, USA
    Posts
    819
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think we're on the same page. I would never have to reset a flag.

    Let me put it this way, an upcoming event will never become a past event by default. It sounds stupid but that's how it's going to work.

    By two views you mean one to show events occurring after today and one to show events occurring before today? I agree that's very easy to implement and makes the most sense, but my client wants to use this feature in a weird way so it won't work. If I do that then there will be events showing up on the past events list that were supposed to disappear from the site once they happened. Doesn't make sense? I agree but that's what I'm hired to do.

  6. #6
    Twitter - @CarlBeckel busy's Avatar
    Join Date
    May 2004
    Location
    Richmond, VA, USA
    Posts
    819
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let's simplify my question. Forget all about events or what I'm using it for. Which of these would be better...
    Code:
    table items
    -id (int auto-inc)
    -name (varchar)
    -flag (tinyint null)
    or

    Code:
    table items
    -id (int auto-inc)
    -name (varchar)
    
    table flag
    -items_id (int)

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    would be better for what purpose, though?

    the difference would depend on things like how often you retrieve one type versus the other, how many of one type of row versus how many of the other...

    but i think i prefer the first one

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Twitter - @CarlBeckel busy's Avatar
    Join Date
    May 2004
    Location
    Richmond, VA, USA
    Posts
    819
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK. I understand what you mean about both of them having appropriate situations.

    You've answered my question and cleared this up. Thanks!


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
  •