SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Enthusiast greg606's Avatar
    Join Date
    Jan 2006
    Location
    Poland, Wrocław
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question db structure problem - post from 2 kinds of users (registered and guests)

    Hi,
    I create simple news system. I have to kinds of users: registered and guests.
    They both can add news and then these news should be displayed on the main page sorted by time(not users)
    When I have just one sort of users there is no problem.
    I just create two tables - users, posts and JOIN them.
    Problems appears when I want to add posts by guest.
    I came up with guest_nick and guest_mail table posts.
    But then I have a lot of NULL cells.
    Here is my structure: http://paste.org/index.php?id=5145
    Could you help me to design this db better?
    Thanks
    Greg

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Please post your 'create table' statements so that we can see your structure more clearly.


    bazz

  3. #3
    SitePoint Enthusiast greg606's Avatar
    Join Date
    Jan 2006
    Location
    Poland, Wrocław
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here it goes:
    Code MySQL:
    #
    CREATE TABLE `forum_post` (
    #
      `post_ID` INT(10) NOT NULL AUTO_INCREMENT,
    #
      `post_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    #
      `user_ID` INT(10) DEFAULT NULL,
    #
      `post_content` VARCHAR(2000) COLLATE utf8_unicode_ci NOT NULL,
    #
      `guest_nick` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    #
      `guest_mail` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    #
      PRIMARY KEY  (`post_ID`)
    #
    ) AUTO_INCREMENT=46 ;
    #
     
    #
     
     
    #
    CREATE TABLE `forum_user` (
    #
      `user_ID` INT(11) NOT NULL AUTO_INCREMENT,
    #
      `user_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    #
      `user_nick` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
    #
      `user_mail` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
    #
      `user_pass` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
    #
      `forum_admin` VARCHAR(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'nie',
    #
      PRIMARY KEY  (`user_ID`)
    #
    ) AUTO_INCREMENT=8 ;

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    And what exactly is the problem? The query to get the data you want from the 2 tables? Please post the query, the result it gives and the result you would like it to give.

  5. #5
    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 greg606 View Post
    I came up with guest_nick and guest_mail table posts.
    But then I have a lot of NULL cells.
    what is "a lot"? and the problem with NULL cells is...?


    Quote Originally Posted by greg606 View Post
    Could you help me to design this db better?
    remove guest_nick and guest_mail from the posts table

    link a post made by a guest to the guest's row in the users table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast greg606's Avatar
    Join Date
    Jan 2006
    Location
    Poland, Wrocław
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what is "a lot"? and the problem with NULL cells is...?

    remove guest_nick and guest_mail from the posts table

    link a post made by a guest to the guest's row in the users table
    Sorry for not answering fast. I was on a trip yesterday and on a weddding today

    I read somewhere that null cells should be avoided and mean that db is not designed well.

    So I will have guests and registered users mixed up in one table?

  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)
    Quote Originally Posted by greg606 View Post
    I read somewhere that null cells should be avoided and mean that db is not designed well.
    probably some theoretician in love with relational algebra

    in the real world, NULLs are useful, practical, and common

    Quote Originally Posted by greg606 View Post
    So I will have guests and registered users mixed up in one table?
    that's what i would do

    if you track separate guests (and don't assign them all to one entry called "anonymous loser") then yeah

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

  8. #8
    SitePoint Enthusiast greg606's Avatar
    Join Date
    Jan 2006
    Location
    Poland, Wrocław
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since is also have admins I merged admin/guest in one column: role
    Is it ok?
    Should this column be INT or VARCHAR?
    Regards
    Greg

  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)
    you probably don't need a "role" column

    guests don't have passwords
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast greg606's Avatar
    Join Date
    Jan 2006
    Location
    Poland, Wrocław
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    point taken
    still i must mark some users as admins

  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)
    i thought that's what the forum_admin column was for

    ( nie = no , correct? )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast greg606's Avatar
    Join Date
    Jan 2006
    Location
    Poland, Wrocław
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i just changed the column admin to role.

    (correct )

  13. #13
    SitePoint Enthusiast greg606's Avatar
    Join Date
    Jan 2006
    Location
    Poland, Wrocław
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So in this case I would have

    insert (new user - guest)
    select (this user)
    and only after those insert of the post.

    I bit complicated

  14. #14
    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)
    the logic for what you want to do is outside of mysql

    for example, how does your code know that the person submitting the news item is a registered user or a guest?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

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
  •