Blog database design

Databases
21

best… post… ever…

22

thanks, longneck :slight_smile: :slight_smile:

23

Rudy,

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


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

yes :slight_smile:

CREATE TABLE (Transact-SQL)

25

cool I had managed to find some stuff on google and changed that error but the next error appears:


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?

26

actually, i gave you the link already

replace this –

, UNIQUE titles_ux ( title )

with this –

, CONSTRAINT titles_ux UNIQUE ( title )

remove this –

, KEY in_reply_to_ix ( in_reply_to )

and add this as a separate statement –

CREATE INDEX in_reply_to_ix ON t_blog_posts ( in_reply_to )

27

Rudy,

I have been looking into that sql script but, I have found an issue. The code stands at:


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
, CONSTRAINT titles_ux UNIQUE ( title )
CREATE INDEX in_reply_to_ix ON t_blog_posts ( in_reply_to )
, FOREIGN KEY in_reply_to_fk ( in_reply_to )
       REFERENCES t_blog_posts ( id )
) TYPE=InnoDB
;

These errors appear:
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword ‘CREATE’.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ‘,’.

I tried using MSDN for the CREATE INDEX and found this:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,…n ] )

So I tried changing this:

CREATE INDEX in_reply_to_ix ON t_blog_posts ( in_reply_to )

to this

CREATE UNIQUE INDEX in_reply_to_ix ON t_blog_posts ( in_reply_to )

I don’t understand why that hasn’t got rid of part of the error.

Can you please explain where I have gone wrong?

28

separate statements

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
, CONSTRAINT titles_ux UNIQUE ( title )
, CONSTRAINT in_reply_to_fk FOREIGN KEY  ( in_reply_to )
       REFERENCES t_blog_posts ( id )
)

CREATE INDEX in_reply_to_ix ON t_blog_posts ( in_reply_to )
29

Rudy,

I am now trying to create the final table the ‘relationship’ table between my posts and categories table. I have made the following but was wondering if you could check it over to see if I have made any mistakes


create table t_blog_relationship
( postid integer not null
, catid integer not null
, primary key ( postid, catid )
, foreign key ( postid )
     references t_blog_posts ( postid )
, foreign key ( catid )
     references t_blog_categories ( catid )
)

I need to make a slight change to the t_blog_posts table to change the id to postid before I can create this table but I am hoping I am almost there now!

Thanks for your time.

Al

30

if you leave t_blog_posts table as is, and don’t change the id to postid (which is what i would do), then just change the above to

foreign key ( postid )
     references t_blog_posts ( id )

do you see why?

31

yeah ok - it calls the data in the relationship table as postid and references the actual field ‘id’.

Thanks

32

Rudy,

Can you please advise me on how I can best extract the information I require from the tables now they have successfully have been created.

I have the following test data in my tables:

Cateogies

id, title, subcat
1001, FLASH, null

posts

id, title, added
1, TestArt1, Current date
2, TestArt2, Current date
3, TestArt3, Current date

relationship
postid, catid
1, 1001
2, 1001
3, 1001

I am having real problems identifying how to extract all posts that are related to the category ‘1001’ FLASH.

I had this


select A.ID, A.title, A.added, B.catid
from t_blog_relationship as B
inner join t_blog_posts as A

on B.catid = '1001'

But this doesn’t work. I haven’t used 3 tables before to store the data in so have no examples that I can modify. Can you please help?

Thanks,

Al

33

actually I have played and got this


SELECT     t_blog_posts.*
FROM         t_blog_posts INNER JOIN
                      t_blog_relationship ON t_blog_posts.id = t_blog_relationship.postid
WHERE     (t_blog_relationship.catid = '1001')

Does that look correct?

Al

34

you say you want all the posts related to category 1001, so this pre-supposes that you are already in possession of the id value

i mean, whatever the user front end application interface for this looks like, presumably it would involve something like choosing the category name from a dropdown, or clicking on a link…

… and the bottom line is that you don’t need to access the category table, just the other two, since you will pass the category id value, 1001 in this case, to the query

so the query would look like this –

select p.id
     , p.title
     , p.added
  from relationship as r
inner
  join posts as p
    on p.id = r.postid
 where r.catid = 1001
35

Rudy,

Just a quick one, what data type should my post_body be? This needs to be able to hold upto around 5000 words (approx 10,000 char).

I had it at nvarchar(MAX) but this seems to limit it, is that correct? What data type would you use for this field?

Many Thanks,

Al

36

that’s 2 gigs, allan

37

NVARCHAR(MAX) is what you want if you are certain this is going to be Sql 2005 only. If you want to support 2000, then NTEXT is the only option. Or actually IMAGE, which can make more sense when storing HTML because you can take advantage of html filters for full-text search.