SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2010
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wink Mark objects as NEW in MYSQL based application

    Hey,
    I am making a web-based application written in PHP and MYSQL (My first full app, hooray!).
    I won't go into details much, but here is a dilemma I have:

    The application will have events, and users who didn't read the event content yet should see the event as "NEW".
    Now, the question is how to implement it.

    I have a users and Events tables in my database.

    Adding all the IDs of user who did see the event to the Events DB seems a bad idea, the DB will grow very quickly.
    Reversing the process and deleting IDs of users who saw the event seems better, but the Event row in the DB increases size and that impacts.

    So I thought of adding each "NEW" Event ID to every user who didn't see it. Seems more logical to me. When the event is no longer "NEW" to the user, the Event ID gets deleted. That way the load is distributed among the users.
    Plus I can clear "NEW" events once in a while.

    If you have a better way of implementing it (a "CONNECTION" object perhaps), please advise.

    The post was written quickly, excuse the bad explanation.

    Thanks in advance.

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Unless you have loads of users I really doubt you'll have issues with just storing a "UserViewedEvents" table of some sort if you need to track if an individual user has seen an event.

    Now, the quick and dirty way is to just track the user's last visit and then mark anything newer than said visit as "new".

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2010
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2010
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question What is the best datatype to store a list of numbers

    Hey,
    I would like to store a list of integers in a row.
    Each row represents a user.
    The list can grow and shrink, but it has to be attached to a particular user.

    What is the best data type for a list of numbers?

    Thanks

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,039
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    What do the numbers in the list represent? Do you need to do something with those numbers? It might be better to create a seperate table for the numbers that has a 1:N relation with your users table, but that really depends on what those numbers are and what you want to do with them.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2010
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I want to highlight new events that are added.
    The list (comma separated) will be loaded into a variable, and if an event ID matches than the event hasn't been viewed by the user yet, it will be highlighted.
    When the user did see an event, the event ID is deleted from the list, and the DB is updated.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    do not use a comma-separated list for this, use a single column in a separate multi-row table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2010
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @r937:
    Could you give a bit more details please?
    (I'm new to servers side and databases)

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    users ( id, name, etc.)

    events ( id, title, datescheduled, etc.)

    user_events ( userid, eventid )


    bold indicates primary key, underline indicates foreign key

    the user_events table will contain 1 row for each event for each user

    in my opinion, it's easier to keep track of which events the user has seen, than to load them all up as unseen and later to delete the ones seen
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Jul 2010
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wouldn't the db become large?

    The application is planned in a way that most users should see most events.
    Events are attached to Projects, when the project is done it's moved to completed and all the data of new events is deleted.

    The NEW EVENTS data is only needed for the active projects (a small portion).
    I thought that it would be better to reduce the DB size by keeping only the UNSEEN EVENTS rather than all the events.

    The event count will be very large, while the user count will remain relatively small.

    I am just curious about optimizing the whole thing.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DeanKucher View Post
    Wouldn't the db become large?
    no!! (emphatically)

    as for that other information, you should've presented that a lot earlier

    i'm swinging around to think that unseen events might be better to store than seen ones, but not merely on the size consideration, but rather on the usage pattern

    same table design, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast
    Join Date
    Jul 2010
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Quote Originally Posted by r937 View Post
    no!! (emphatically)

    as for that other information, you should've presented that a lot earlier
    Sorry

    The app also uses the same model for permissions:
    "Events" and "Projects" can have several "Owners" (user IDs).
    Would suggest the same table design here?

    Thanks!

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DeanKucher View Post
    "Events" and "Projects" can have several "Owners" (user IDs).
    Would suggest the same table design here?
    absolutely
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •