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 (see newsItem 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 (see categories 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 (see photos 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 (see comments 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 |
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;
[ 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
)