SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 37
  1. #1
    SitePoint Evangelist Muller2's Avatar
    Join Date
    Feb 2005
    Location
    Oxford, UK
    Posts
    522
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    blog database design

    All,

    I am in the middle of creating my first blog, and I am at the stage where I now need to develop my database. It will be a MS SQL 2005 database and I need to meet the following:

    1) Select last 5 posts entires for the homepage
    2) Select * posts where category = '@categoryid'
    3) select post, comments where postid = '@postid'

    These one's are more layout based:

    4) On my homepage I would like to have a list of my Category names and within a pair of brackets display how many posts are related to that category.

    5) Also I would like to have the same ability to list the last 6 months (and display how many posts were made within that month.


    So far I have the following for my database, but can I get your input into changes and ways to query and get my desired results (as above?)?

    postid <<pk>>
    posttitle
    postbody
    posttimestamp

    postid
    commentsid
    (Composite foreign key?)

    commentsid <<pk>>
    commentsbody
    commentsemail
    commentsurl
    commentsname

    categoryid <<pk>>
    categoryname

    postid
    categoryid
    (Composite foreign key?)

    Many thanks for your time.

    Al

  2. #2
    SitePoint Addict
    Join Date
    Feb 2000
    Location
    Vilnius, Lithuania
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why would you want to link comments to post through an intermediate table? Do you plan to have one comment for several posts?
    SPAW Editor v.2 - web based wysiwyg editor for PHP and ASP.NET.
    Opera and Safari support, tabbed interface, floating toolbar...

  3. #3
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I would go and look at a functioning blog application, such as SubText, to see a working blog database model.

    In fact, at this point I would not build a blogging package from scratch as anything other than an academic exercise . . .

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    in my blog design i put the post and the comment into the same table, because they have so many common columns

    the table uses a column called "in_reply_to" and when this is NULL, the row represents a post, and if it isn't, then it's a reply/comment

    notice that this nicely allows you to have comments on other comments, and not all just tied to the main post
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Feb 2000
    Location
    Vilnius, Lithuania
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the table uses a column called "in_reply_to" and when this is NULL, the row represents a post, and if it isn't, then it's a reply/comment

    notice that this nicely allows you to have comments on other comments, and not all just tied to the main post
    In case your blog becomes popular this could be a pain to filter posts in-between lots of comments. And with that replies to comments approach calculating comments for the post is also a little more complex task
    SPAW Editor v.2 - web based wysiwyg editor for PHP and ASP.NET.
    Opera and Safari support, tabbed interface, floating toolbar...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    a pain to filter posts in-between lots of comments? no
    Code:
    where in_reply_to is null

    calculating comments for the post is also a little more complex? no
    Code:
    group by thread_starter
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Feb 2000
    Location
    Vilnius, Lithuania
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    a pain to filter posts in-between lots of comments? no
    Code:
    where in_reply_to is null
    I meant pain for DB server, not for coder Imagine that you have 5000 posts with 100 comments per post. Not a great pain but still

    Quote Originally Posted by r937 View Post
    calculating comments for the post is also a little more complex? no
    Code:
    group by thread_starter
    Well, this is another field you haven't mentioned. Anyway I've used a similar approach in one application (this was with MS Access something like 8 years ago) and it turned into constant performance optimization nightmare. That wasn't a blog however...
    SPAW Editor v.2 - web based wysiwyg editor for PHP and ASP.NET.
    Opera and Safari support, tabbed interface, floating toolbar...

  8. #8
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ailon View Post
    I meant pain for DB server, not for coder Imagine that you have 5000 posts with 100 comments per post. Not a great pain but still
    Not really. If you have the right indexes on the table, 5,000,000 rows should not really hurt a thing on proper hardware. Databases are designed to look up things. Fast.

    Well, this is another field you haven't mentioned. Anyway I've used a similar approach in one application (this was with MS Access something like 8 years ago) and it turned into constant performance optimization nightmare. That wasn't a blog however...
    Access is a performance nightmare . . .

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ailon View Post
    Well, this is another field you haven't mentioned.
    of course -- because it was in response to a requirement that you haven't mentioned until just now

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

  10. #10
    SitePoint Evangelist Muller2's Avatar
    Join Date
    Feb 2005
    Location
    Oxford, UK
    Posts
    522
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    I would go and look at a functioning blog application, such as SubText, to see a working blog database model.

    In fact, at this point I would not build a blogging package from scratch as anything other than an academic exercise . . .
    I am trying to learn from this whole project and I also want the experiance of developing a database that properly interacts with my .net code. So ideally I would like to develop it myself. I had a look online but couldn't find any real information/diagrams to do with the structure of the SubText database, do you know of any?

  11. #11
    SitePoint Evangelist Muller2's Avatar
    Join Date
    Feb 2005
    Location
    Oxford, UK
    Posts
    522
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    of course -- because it was in response to a requirement that you haven't mentioned until just now

    So can you please post your structure then please? I found an old post where you demonstrated your single table approach and it makes sense to me but how would I go about dealing with the categories? Would they be in a seperate table or would they be better located within the single table?

    This is the old post: here

    Thanks,

    Al

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Muller2 View Post
    ... but how would I go about dealing with the categories?
    they would be in a separate table, and there would be a many-to-many relationship table to relate posts to categories
    Code:
    CREATE TABLE posts
    ( id   INTEGER   NOT NULL PRIMARY KEY AUTO_INCREMENT
    , in_reply_to INTEGER      NULL
    , title       VARCHAR(255) NOT NULL
    , descr       VARCHAR(255) NULL
    , url         VARCHAR(255) NULL
    , added       DATETIME     NOT NULL
    , chged       DATETIME     NOT NULL
    , other_columns ...
    , UNIQUE titles_ux ( title )
    , KEY in_reply_to_ix ( in_reply_to )
    , FOREIGN KEY in_reply_to_fk ( in_reply_to )
           REFERENCES posts ( id )
    ) TYPE=InnoDB
    ;
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Evangelist Muller2's Avatar
    Join Date
    Feb 2005
    Location
    Oxford, UK
    Posts
    522
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so the category table would be
    Code:
    CREATE TABLE posts
    ( catid   INTEGER   NOT NULL PRIMARY KEY AUTO_INCREMENT
    , subcatid INTEGER      NULL
    , cattitle       VARCHAR(255) NOT NULL
    That would give me catid, subcatid, and cattitle.

    Then would I have a table called relationships that had
    Code:
    catid,
    postid
    (As Composite key's?)

    Thanks Rudy,

    Al

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the relationship table is correct -- two columns, each a foreign key to its respective table, and the pair together as a composite primary key

    as for the category table, i would not use subcatid, rather parent_id

    perhaps this may help -- Categories and Subcategories
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Evangelist Muller2's Avatar
    Join Date
    Feb 2005
    Location
    Oxford, UK
    Posts
    522
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cool thanks rudy - I don't need subcategories yet so I might look into that for a future task!

    Also the biggest issue I have with sql is knowing which join to use, is there a 'grid'/diagram that highlights which one is for which task?

    Thanks again.

    Al

  16. #16
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ailon View Post
    I meant pain for DB server, not for coder Imagine that you have 5000 posts with 100 comments per post. Not a great pain but still



    Well, this is another field you haven't mentioned. Anyway I've used a similar approach in one application (this was with MS Access something like 8 years ago) and it turned into constant performance optimization nightmare. That wasn't a blog however...
    Quote Originally Posted by Muller2 View Post
    I am trying to learn from this whole project and I also want the experiance of developing a database that properly interacts with my .net code. So ideally I would like to develop it myself. I had a look online but couldn't find any real information/diagrams to do with the structure of the SubText database, do you know of any?
    The best source is probably the source code itself. Get it from the project's sourceforge site.

  17. #17
    SitePoint Evangelist Muller2's Avatar
    Join Date
    Feb 2005
    Location
    Oxford, UK
    Posts
    522
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy,

    I get the following error in ms sql 2k5 when running this to create the tables
    Code:
    create table categories
    ( id       integer     not null  primary key 
    , name     varchar(37) not null
    , parentid integer     null
    , foreign key parentid_fk (parentid) 
          references categories (id)
    );
    The error is:
    Msg 102, Level 15, State 1, Line 5
    Incorrect syntax near 'parentid_fk'.

    Is that because there is _fk on the end?

    Thanks

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Muller2 View Post
    Also the biggest issue I have with sql is knowing which join to use, is there a 'grid'/diagram that highlights which one is for which task?
    sure, let's make one up right here

    suppose you have these two tables --
    Code:
     A     B
    102   101
    104   102
    106   104
    107   106
    here are what you'd get for the various joins --
    Code:
    A inner join B
    102   102
    104   104
    106   106
    
    A left outer join B
    102   102
    104   104
    106   106
    107   null
    
    A right outer join B
    null  101
    102   102
    104   104
    106   106
    
    A full outer join B
    null  101
    102   102
    104   104
    106   106
    107   null
    
    A cross join B
    102   101
    104   101
    106   101
    107   101
    102   102
    104   102
    106   102
    107   102
    102   104
    104   104
    106   104
    107   104
    102   106
    104   106
    106   106
    107   106
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Muller2 View Post
    I get the following error in ms sql 2k5 when running this to create the tables
    my apologies, that article was using mysql syntax, the correct syntax is
    Code:
    , constraint parentid_fk foreign key (parentid) 
          references categories (id)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Evangelist Muller2's Avatar
    Join Date
    Feb 2005
    Location
    Oxford, UK
    Posts
    522
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    my apologies, that article was using mysql syntax, the correct syntax is
    Code:
    , constraint parentid_fk foreign key (parentid) 
          references categories (id)
    Please don't applogise! Your help is ace!

    Al

  21. #21
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sure, let's make one up right here

    suppose you have these two tables --
    Code:
     A     B
    102   101
    104   102
    106   104
    107   106
    here are what you'd get for the various joins --
    best... post... ever...

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    thanks, longneck
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Evangelist Muller2's Avatar
    Join Date
    Feb 2005
    Location
    Oxford, UK
    Posts
    522
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy,

    I have tried creating this table in Ms2k5 but I get an error simular to before

    Code:
    CREATE TABLE t_blog_posts
    ( id   INTEGER   NOT NULL PRIMARY KEY AUTO_INCREMENT
    , in_reply_to INTEGER      NULL
    , title       VARCHAR(255) NOT NULL
    , descr       VARCHAR(255) NULL
    , url         VARCHAR(255) NULL
    , added       DATETIME     NOT NULL
    , chged       DATETIME     NOT NULL
    , UNIQUE titles_ux ( title )
    , KEY in_reply_to_ix ( in_reply_to )
    , FOREIGN KEY in_reply_to_fk ( in_reply_to )
           REFERENCES posts ( id )
    ) TYPE=InnoDB
    ;
    Error:
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near 'AUTO_INCREMENT'.


    Is this a syntax error becasue it was written for mysql? Can you highlight what I need to change so I can get the table to create in ms2k5 please?

    Thanks,

    Al

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Muller2 View Post
    Is this a syntax error becasue it was written for mysql?
    yes

    CREATE TABLE (Transact-SQL)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Evangelist Muller2's Avatar
    Join Date
    Feb 2005
    Location
    Oxford, UK
    Posts
    522
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cool I had managed to find some stuff on google and changed that error but the next error appears:

    Code:
    CREATE TABLE t_blog_posts
    ( id INTEGER NOT NULL Primary Key IDENTITY(1,1) 
    , in_reply_to INTEGER      NULL
    , title       VARCHAR(255) NOT NULL
    , descr       VARCHAR(255) NULL
    , url         VARCHAR(255) NULL
    , added       DATETIME     NOT NULL
    , chged       DATETIME     NOT NULL
    , UNIQUE titles_ux ( title )
    , KEY in_reply_to_ix ( in_reply_to )
    , FOREIGN KEY in_reply_to_fk ( in_reply_to )
           REFERENCES posts ( id )
    ) TYPE=InnoDB
    ;
    Error now:
    Msg 102, Level 15, State 1, Line 10
    Incorrect syntax near 'titles_ux'.

    I don't know what to google to begin fixing this error though? Can you advise me please?


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
  •