homesupportadvanced database structure [tutorial]

database tables

to create a flexible database which can efficiently store news articles it is best to use a three-table design to allow many-to-many relationships between articles and categories. these tables are as follows:

table name description
newsarticles for storing the content for each article
newscategories for storing the news categories
newsarticlecategories for linking categories to articles
newsphotos for storing the news photos
newsarticlephotos for linking photos to articles

for storing the content of each article:

newsarticles
field name description required?
id primary key. the unique newsitem id available via the api (seenewsitem protocol reference) y
headline the newsitem headline y
extract a summary of the newsitem n
encoding character encoding system used eg. utf-8 y
text the text content of the newsitem y
publishdate date article was published y
byline author of the newsitem n
tweettext special 140 character twitter version of newsitem n
source original source of the article n
state current state of the article eg live / approval / draft / deleted n
clientquote quote text from client the newsitem is written for n
createddate date article was created y
lastmodifieddate date article was last modified y
htmltitle text to be inserted into element of page n
htmlmetadescription text to be inserted into the description meta tag of the page newsitem is being rendered on n
htmlmetakeywords text to be inserted into the keywords meta tag of the page newsitem is being rendered on n
htmlmetalangauge language option to be inserted into the language meta tag of the page newsitem is being rendered on n
tags a comma seperated list of free text tags n
priority priority flag 30 = high, norma = 20 used to prioritise news along with publishdate n
format format of text content either html or raw y

for storing the news categories:

newscategories
field name description required?
id the unique category id available via the api (seecategories protocol reference) y
categoryname the text name of the category y

for storing the associations between categories and articles, e.g. what categories a given article belongs to:

newsarticlecategories
field name description required?
id primary key. autogenerated unique integer y
newsarticleid a valid id from the newsarticle table y
newscategoryid a valid id of a category from the newscategory table. y

for storing the news photos:

newsphotos
field name description required?
id the unique photo id available via the api (seephotos protocol reference) y
htmlalt text which appears in the alternative text attribute for the image n
orientation orientation of photo either portrait or landscape n
thumbnailwidth the width in pixels of the thumbnail instance of the photo n
thumbnailheight the height in pixels of the thumbnail instance of the photo n
thumbnailurl the url that points to the source location of the thumbnail photo n
largewidth the width in pixels of the large instance of the photo n
largeheight the height in pixels of the large instance of the photo n
largeurl the url that points to the source location of the large photo n
hireswidth the width in pixels of the high resolution instance of the photo n
hiresheight the height in pixels of the high resolution instance of the photo n
hiresurl the url that points to the source location of the high resolution photo n
customwidth the width in pixels of the custom instance of the photo n
customheight the height in pixels of the custom instance of the photo n
customurl the url that points to the source location of the custom photo n

for storing the associations between photos and articles, e.g. what photos belong to a given article:

newsarticlephotos
field name description required?
id primary key. autogenerated unique integer y
newsarticleid a valid id from the newsarticle table y
newsphotoid a valid id of a photo from the newsphotos table. y

for storing the news comments:

newscomments
field name description required?
id the unique comment id available via the api (seecomments protocol reference) y
text the comment text y
name name information the commentor has entered n
location geographic location information commenter has entered n
postdate date/time when the comment was submitted y

for storing the associations between comments and articles, e.g. which comments belong to a given article:

newsarticlecomments
field name description required?
id primary key. autogenerated unique integer y
newsarticleid a valid id from the newsarticle table y
newscommentid a valid id of a comment from the newscomments table. y

[jump to top]

creating advanced database structure

below are two example scripts which you can run in mysql and sql server respectively which will create a more advanced and comprehensive database structure to persist your feed data to (you can “replace all” the text for dncontentdb with whatever you wish and if you already have a database you would like to use then omit the first line of the script and replace all subsequent instances of dncontentdb with the name of the database you wish to use):

[mysql creation script ]

create database advancedsampledb /*!40100 default character set latin1 */;

drop table if exists advancedsampledb.`newscategories`;
create table advancedsampledb.`newscategories` (
`id` int(10) unsigned not null,
`name` varchar(255) not null,
primary key (`id`)
) engine=innodb default charset=latin1;

drop table if exists advancedsampledb.`newsarticles`;
create table advancedsampledb.`newsarticles` (
`id` int(10) unsigned not null,
`headline` varchar(255) not null,
`extract` text,
`encoding` varchar(45) not null,
`text` text not null,
`publishdate` datetime not null,
`byline` varchar(255) default null,
`tweettext` varchar(140) default null,
`source` varchar(255) default null,
`state` varchar(20) not null,
`clientquote` text not null,
`createddate` datetime not null,
`lastmodifieddate` datetime not null,
`htmltitle` varchar(255) default null,
`htmlmetadescription` varchar(255) default null,
`htmlmetakeywords` varchar(255) default null,
`htmlmetalangauge` varchar(255) default null,
`tags` varchar(255) default null,
`priority` int(10) unsigned default null,
`format` varchar(10) not null,
primary key (`id`)
) engine=innodb default charset=latin1;

drop table if exists advancedsampledb.`newsarticlecategories`;
create table advancedsampledb.`newsarticlecategories` (
`id` int(10) unsigned not null auto_increment,
`newscategoryid` int(10) unsigned not null,
`newsarticleid` int(10) unsigned not null,
primary key (`id`),
key `fk_feedcategories_1` (`newscategoryid`),
key `fk_feedcategories_2` (`newsarticleid`),
constraint `fk_feedcategories_1` foreign key (`newscategoryid`) references `newscategories` (`id`) on delete cascade on update cascade,
constraint `fk_feedcategories_2` foreign key (`newsarticleid`) references `newsarticles` (`id`) on delete cascade on update cascade
) engine=innodb auto_increment=937 default charset=latin1;

drop table if exists advancedsampledb.`newsphotos`;
create table advancedsampledb.`newsphotos` (
`id` int(10) unsigned not null,
`htmlalt` varchar(255) default null,
`orientation` varchar(45) default null,
`thumbnailwidth` smallint(5) unsigned default null,
`thumbnailheight` smallint(5) unsigned default null,
`thumbnailurl` text,
`largewidth` smallint(5) unsigned default null,
`largeheight` smallint(5) unsigned default null,
`largeurl` text,
`hireswidth` smallint(5) unsigned default null,
`hiresheight` smallint(5) unsigned default null,
`hiresurl` text,
`customwidth` smallint(5) unsigned default null,
`customheight` smallint(5) unsigned default null,
`customurl` text,
primary key (`id`)
) engine=innodb default charset=latin1;

drop table if exists advancedsampledb.`newsarticlephotos`;
create table advancedsampledb.`newsarticlephotos` (
`id` int(10) unsigned not null auto_increment,
`newsarticleid` int(10) unsigned not null,
`newsphotoid` int(10) unsigned not null,
primary key (`id`),
key `fk_feedphotos_1` (`newsphotoid`),
key `fk_feedphotos_2` (`newsarticleid`),
constraint `fk_feedphotos_1` foreign key (`newsphotoid`) references `newsphotos` (`id`) on delete cascade on update cascade,
constraint `fk_feedphotos_2` foreign key (`newsarticleid`) references `newsarticles` (`id`) on delete cascade on update cascade
) engine=innodb auto_increment=812 default charset=latin1;

drop table if exists advancedsampledb.`newscomments`;
create table advancedsampledb.`newscomments` (
`id` int(10) unsigned not null auto_increment,
`text` text not null,
`name` varchar(255) default null,
`location` varchar(255) default null,
`postdate` datetime not null,
primary key (`id`)
) engine=innodb default charset=latin1;

drop table if exists advancedsampledb.`newsarticlecomments`;
create table advancedsampledb.`newsarticlecomments` (
`id` int(10) unsigned not null auto_increment,
`newsarticleid` int(10) unsigned not null,
`newscommentid` int(10) unsigned not null,
primary key (`id`),
key `fk_feedcomments_1` (`newscommentid`),
key `fk_feedcomments_2` (`newsarticleid`),
constraint `fk_feedcomments_1` foreign key (`newscommentid`) references `newscomments` (`id`) on delete cascade on update cascade,
constraint `fk_feedcomments_2` foreign key (`newsarticleid`) references `newsarticles` (`id`) on delete cascade on update cascade
) engine=innodb default charset=latin1;

[jump to top]

[mssql server 2005 creation script ]

create database advancedsampledb;
go
create table advancedsampledb.dbo."newscategories" (
"id" int not null,
"name" varchar(255) not null,
primary key ("id")
)

create table advancedsampledb.dbo."newsarticles" (
"id" int not null,
"headline" varchar(255) not null,
"extract" text,
"encoding" varchar(45) not null,
"text" text not null,
"publishdate" datetime not null,
"byline" varchar(255) default null,
"tweettext" varchar(140) default null,
"source" varchar(255) default null,
"state" varchar(20) not null,
"clientquote" text not null,
"createddate" datetime not null,
"lastmodifieddate" datetime not null,
"htmltitle" varchar(255) default null,
"htmlmetadescription" varchar(255) default null,
"htmlmetakeywords" varchar(255) default null,
"htmlmetalangauge" varchar(255) default null,
"tags" varchar(255) default null,
"priority" int default null,
"format" varchar(10) not null,
primary key ("id")
)

create table advancedsampledb.dbo."newsarticlecategories" (
"id" int identity(1,1),
"newscategoryid" int not null,
"newsarticleid" int not null,
primary key ("id"),
constraint fk_catid
foreign key ("newscategoryid")
references advancedsampledb.dbo."newscategories" (id) on delete cascade on update cascade,
constraint fk_feedid_cat
foreign key ("newsarticleid")
references advancedsampledb.dbo."newsarticles" (id) on delete cascade on update cascade
)

create table advancedsampledb.dbo."newsphotos" (
"id" int not null,
"htmlalt" varchar(255) default null,
"orientation" varchar(45) default null,
"thumbnailwidth" smallint default null,
"thumbnailheight" smallint default null,
"thumbnailurl" text,
"largewidth" smallint default null,
"largeheight" smallint default null,
"largeurl" text,
"hireswidth" smallint default null,
"hiresheight" smallint default null,
"hiresurl" text,
"customwidth" smallint default null,
"customheight" smallint default null,
"customurl" text,
primary key ("id")
)

create table advancedsampledb.dbo."newsarticlephotos" (
"id" int identity(1,1),
"newsarticleid" int not null,
"newsphotoid" int not null,
primary key ("id"),
constraint fk_photoid
foreign key ("newsphotoid")
references advancedsampledb.dbo."newsphotos" (id) on delete cascade on update cascade,
constraint fk_feedid_photo
foreign key ("newsarticleid")
references advancedsampledb.dbo."newsarticles" (id) on delete cascade on update cascade
)

create table advancedsampledb.dbo."newscomments" (
"id" int identity(1,1),
"text" text not null,
"name" varchar(255) default null,
"location" varchar(255) default null,
"postdate" datetime not null,
primary key ("id")
)

create table advancedsampledb.dbo."newsarticlecomments" (
"id" int identity(1,1),
"newsarticleid" int not null,
"newscommentid" int not null,
primary key ("id"),
constraint fk_commentid
foreign key ("newscommentid")
references advancedsampledb.dbo."newscomments" (id) on delete cascade on update cascade,
constraint fk_feedid_comm
foreign key ("newsarticleid")
references advancedsampledb.dbo."newsarticles" (id) on delete cascade on update cascade
)

[jump to top]