- i. Database Tables
- ii. Microsoft SQL Server Creation Script
- iii. MySQL Creation Script
- iv. Entity Relationship Diagram
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 |
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 | Y |
| 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
<title> 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 |
| photoHtmlAlt | text which appears in the alternative text attribute for the image | N |
| photoOrientation | orientation of photo either PORTRAIT or LANDSCAPE | N |
| photoWidth | the width in pixels of the thumbnail instance of the photo | N |
| photoHeight | the height in pixels of the thumbnail instance of the photo | N |
| photoURL | the URL that points to the source location of the thumbnail photo | N |
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 |
Microsoft SQL Server Creation Script
The default name of the database is set to DNContentDBSimple. The creation script is provided below:
CREATE DATABASE BasicSampleDB;
GO
CREATE TABLE BasicSampleDB.dbo."NewsCategories" (
"id" INT NOT NULL,
"name" VARCHAR(255) NOT NULL,
PRIMARY KEY ("id")
)
CREATE TABLE BasicSampleDB.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 DEFAULT 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,
"photoHtmlAlt" VARCHAR(255) DEFAULT NULL,
"photoOrientation" VARCHAR(45) DEFAULT NULL,
"photoWidth" SMALLINT DEFAULT NULL,
"photoHeight" SMALLINT DEFAULT NULL,
"photoURL" TEXT,
PRIMARY KEY ("id")
)
CREATE TABLE BasicSampleDB.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 BasicSampleDB.dbo."NewsCategories" (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_feedID_cat
FOREIGN KEY ("newsArticleID")
REFERENCES BasicSampleDB.dbo."NewsArticles" (id) ON DELETE CASCADE ON UPDATE CASCADE
)
MySQL Creation Script
The default name of the database is set to DNContentDBSimple. The creation script is provided below:
CREATE DATABASE BasicSampleDB /*!40100 DEFAULT CHARACTER SET latin1 */;
DROP TABLE IF EXISTS BasicSampleDB.`NewsCategories`;
CREATE TABLE BasicSampleDB.`NewsCategories` (
`id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS BasicSampleDB.`NewsArticles`;
CREATE TABLE BasicSampleDB.`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 DEFAULT 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,
`photoHtmlAlt` VARCHAR(255) DEFAULT NULL,
`photoOrientation` VARCHAR(45) DEFAULT NULL,
`photoWidth` SMALLINT DEFAULT NULL,
`photoHeight` SMALLINT DEFAULT NULL,
`photoURL` TEXT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS BasicSampleDB.`NewsArticleCategories`;
CREATE TABLE BasicSampleDB.`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;
Entity Relationship Diagram
Click to enlarge.