homesupportsimple 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

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 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

</pre><p>element of page</p></td>
          <td>n</td>
         </tr>
         <tr>
          <td>htmlmetadescription</td>
          <td>text to be inserted into the description meta tag of the page newsitem is being rendered on</td>
          <td>n</td>
         </tr>
         <tr>
          <td>htmlmetakeywords</td>
          <td>text to be inserted into the keywords meta tag of the page newsitem is being rendered on</td>
          <td>n</td>
         </tr>
         <tr>
          <td>htmlmetalangauge</td>
          <td>language option to be inserted into the language meta tag of the page newsitem is being rendered on</td>
          <td>n</td>
         </tr>
         <tr>
          <td>tags</td>
          <td>a comma seperated list of free text tags</td>
          <td>n</td>
         </tr>
         <tr>
          <td>priority</td>
          <td>priority flag 30 = high, norma = 20 used to prioritise news along with publishdate</td>
          <td>n</td>
         </tr>
         <tr>
          <td>format</td>
          <td>format of text content either html or raw</td>
          <td>y</td>
         </tr>
         <tr>
          <td>photohtmlalt</td>
          <td>text which appears in the alternative text attribute for the image</td>
          <td>n</td>
         </tr>
         <tr>
          <td>photoorientation</td>
          <td>orientation of photo either portrait or landscape</td>
          <td>n</td>
         </tr>
         <tr>
          <td>photowidth</td>
          <td>the width in pixels of the thumbnail instance of the photo</td>
          <td>n</td>
         </tr>
         <tr>
          <td>photoheight</td>
          <td>the height in pixels of the thumbnail instance of the photo</td>
          <td>n</td>
         </tr>
         <tr>
          <td>photourl</td>
          <td>the url that points to the source location of the thumbnail photo</td>
          <td>n</td>
         </tr>
        </tbody>
       </table>
       <p>for storing the news categories:</p>
       <table cellspacing="5">
        <tbody>
         <tr>
          <th colspan="3">newscategories</th>
         </tr>
         <tr>
          <th>field name</th>
          <th>description</th>
          <th>required?</th>
         </tr>
         <tr>
          <td>id</td>
          <td>the unique category id available via the api (see<a href="//www.hotgingerla.com/support/reference" target="_blank">categories protocol reference</a>)</td>
          <td>y</td>
         </tr>
         <tr>
          <td>categoryname</td>
          <td>the text name of the category</td>
          <td>y</td>
         </tr>
        </tbody>
       </table>
       <p>for storing the associations between categories and articles, e.g. what categories a given article belongs to:</p>
       <table cellspacing="5">
        <tbody>
         <tr>
          <th colspan="3">newsarticlecategories</th>
         </tr>
         <tr>
          <th>field name</th>
          <th>description</th>
          <th>required?</th>
         </tr>
         <tr>
          <td>id</td>
          <td>primary key. autogenerated unique integer</td>
          <td>y</td>
         </tr>
         <tr>
          <td>newsarticleid</td>
          <td>a valid id from the newsarticle table</td>
          <td>y</td>
         </tr>
         <tr>
          <td>newscategoryid</td>
          <td>a valid id of a category from the newscategory table.</td>
          <td>y</td>
         </tr>
        </tbody>
       </table>
       <p align="center">[<a href="#top" class="jump">jump to top</a>]</p>
       <h3><a name="mssql">microsoft sql server creation script</a></h3>
       <p>the default name of the database is set to dncontentdbsimple. the creation script is provided below:</p>
       <p><code class="sql" lang="sql">create database basicsampledb;<br>go<br>create table basicsampledb.dbo."newscategories" (<br>"id" int not null,<br>"name" varchar(255) not null,<br>primary key ("id")<br>)<br><br>create table basicsampledb.dbo."newsarticles" (<br>"id" int not null,<br>"headline" varchar(255) not null,<br>"extract" text,<br>"encoding" varchar(45) not null,<br>"text" text not null,<br>"publishdate" datetime not null,<br>"byline" varchar(255) default null,<br>"tweettext" varchar(140) default null,<br>"source" varchar(255) default null,<br>"state" varchar(20) not null,<br>"clientquote" text default null,<br>"createddate" datetime not null,<br>"lastmodifieddate" datetime not null,<br>"htmltitle" varchar(255) default null,<br>"htmlmetadescription" varchar(255) default null,<br>"htmlmetakeywords" varchar(255) default null,<br>"htmlmetalangauge" varchar(255) default null,<br>"tags" varchar(255) default null,<br>"priority" int default null,<br>"format" varchar(10) not null,<br>"photohtmlalt" varchar(255) default null,<br>"photoorientation" varchar(45) default null,<br>"photowidth" smallint default null,<br>"photoheight" smallint default null,<br>"photourl" text,<br>primary key ("id")<br>)<br><br>create table basicsampledb.dbo."newsarticlecategories" (<br>"id" int identity(1,1),<br>"newscategoryid" int not null,<br>"newsarticleid" int not null,<br>primary key ("id"),<br>constraint fk_catid<br>foreign key ("newscategoryid")<br>references basicsampledb.dbo."newscategories" (id) on delete cascade on update cascade,<br>constraint fk_feedid_cat<br>foreign key ("newsarticleid")<br>references basicsampledb.dbo."newsarticles" (id) on delete cascade on update cascade<br>)</code></p>
       <p align="center">[<a href="#top" class="jump">jump to top</a>]</p>
       <h3><a name="mysql">mysql creation script</a></h3>
       <p>the default name of the database is set to dncontentdbsimple. the creation script is provided below:</p>
       <p><code class="sql" lang="sql">create database basicsampledb /*!40100 default character set latin1 */;<br><br>drop table if exists basicsampledb.`newscategories`;<br>create table basicsampledb.`newscategories` (<br>`id` int(10) unsigned not null,<br>`name` varchar(255) not null,<br>primary key (`id`)<br>) engine=innodb default charset=latin1;<br><br>drop table if exists basicsampledb.`newsarticles`;<br>create table basicsampledb.`newsarticles` (<br>`id` int(10) unsigned not null,<br>`headline` varchar(255) not null,<br>`extract` text,<br>`encoding` varchar(45) not null,<br>`text` text not null,<br>`publishdate` datetime not null,<br>`byline` varchar(255) default null,<br>`tweettext` varchar(140) default null,<br>`source` varchar(255) default null,<br>`state` varchar(20) not null,<br>`clientquote` text default null,<br>`createddate` datetime not null,<br>`lastmodifieddate` datetime not null,<br>`htmltitle` varchar(255) default null,<br>`htmlmetadescription` varchar(255) default null,<br>`htmlmetakeywords` varchar(255) default null,<br>`htmlmetalangauge` varchar(255) default null,<br>`tags` varchar(255) default null,<br>`priority` int(10) unsigned default null,<br>`format` varchar(10) not null,<br>`photohtmlalt` varchar(255) default null,<br>`photoorientation` varchar(45) default null,<br>`photowidth` smallint default null,<br>`photoheight` smallint default null,<br>`photourl` text,<br><br>primary key (`id`)<br>) engine=innodb default charset=latin1;<br><br>drop table if exists basicsampledb.`newsarticlecategories`;<br>create table basicsampledb.`newsarticlecategories` (<br>`id` int(10) unsigned not null auto_increment,<br>`newscategoryid` int(10) unsigned not null,<br>`newsarticleid` int(10) unsigned not null,<br>primary key (`id`),<br>key `fk_feedcategories_1` (`newscategoryid`),<br>key `fk_feedcategories_2` (`newsarticleid`),<br>constraint `fk_feedcategories_1` foreign key (`newscategoryid`) references `newscategories` (`id`) on delete cascade on update cascade,<br>constraint `fk_feedcategories_2` foreign key (`newsarticleid`) references `newsarticles` (`id`) on delete cascade on update cascade<br>) engine=innodb auto_increment=937 default charset=latin1;</code></p>
       <p align="center">[<a href="#top" class="jump">jump to top</a>]</p>
       <h3><a name="diagram">entity relationship diagram</a></h3>
       <p></p>
       <p align="center"></p>
       <div id="attachment_16972" style="width: 308px" class="wp-caption aligncenter">
        <a href="//www.hotgingerla.com/wp-content/uploads/2011/01/simlpedbdiagram.png" class="single_image"><img aria-describedby="caption-attachment-16972" loading="lazy" src="//www.hotgingerla.com/wp-content/uploads/2011/01/simlpedbdiagram.png" alt="simple database relationship schematic" title="simple database relationship schematic" width="298" height="300" class="size-medium wp-image-16972" srcset="//www.hotgingerla.com/wp-content/uploads/2011/01/simlpedbdiagram.png 700w, //www.hotgingerla.com/wp-content/uploads/2011/01/simlpedbdiagram-220x220.png 220w, //www.hotgingerla.com/wp-content/uploads/2011/01/simlpedbdiagram-350x351.png 350w" sizes="(max-width: 298px) 100vw, 298px"></a>
        <p id="caption-attachment-16972" class="wp-caption-text">click to enlarge.</p>
       </div>
       <p></p>
       <p align="center">[<a href="#top" class="jump">jump to top</a>]</p>
      </section>
      <footer class="article-footer cf"></footer>
     </article>
    </div>
   </div>
  </div>
  <footer class="footer" role="contentinfo" itemscope itemtype="http://schema.org/wpfooter">
   <div id="inner-footer" class="wrap cf">
    <div class="social">
     <a target="_blank" rel="noreferrer" href="https://www.instagram.com/brafton/"><i class="fa fa-instagram" aria-hidden="true"></i><span class="hide">instagram</span></a>
     <a target="_blank" rel="noreferrer" href="https://www.twitter.com/brafton"><i class="fa fa-twitter" aria-hidden="true"></i><span class="hide">twitter</span></a>
     <a target="_blank" rel="noreferrer" href="https://www.facebook.com/brafton"><i class="fa fa-facebook" aria-hidden="true"></i><span class="hide">facebook</span></a>
     <a target="_blank" rel="noreferrer" href="https://www.linkedin.com/company/brafton/"><i class="fa fa-linkedin" aria-hidden="true"></i><span class="hide">linkedin</span></a>
    </div>
    <div id="nav_menu-4" class="widget widget_nav_menu">
     <div class="menu-contact-us-container">
      <ul id="menu-contact-us" class="menu">
       <li id="menu-item-89754" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-89754"><a href="//www.hotgingerla.com/contact-us/">get in touch!</a></li>
       <li id="menu-item-89912" class="menu-item menu-item-type-custom menu-item-object-custom menu-item-89912"><a href="//www.hotgingerla.com/support">support center</a></li>
       <li id="menu-item-94842" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-94842"><a href="//www.hotgingerla.com/privacy/">privacy statement</a></li>
      </ul>
     </div>
    </div>
    <div id="nav_menu-5" class="widget widget_nav_menu">
     <h4 class="widgettitle">our story</h4>
     <div class="menu-about-us-container">
      <ul id="menu-about-us" class="menu">
       <li id="menu-item-89731" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-89731"><a href="//www.hotgingerla.com/about/">about brafton</a></li>
       <li id="menu-item-89751" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-89751"><a href="//www.hotgingerla.com/about/leadership/">brafton leadership</a></li>
       <li id="menu-item-89752" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-89752"><a href="//www.hotgingerla.com/about/our-writers/">content writers</a></li>
      </ul>
     </div>
    </div>
    <div id="nav_menu-6" class="widget widget_nav_menu">
     <h4 class="widgettitle">2022世界杯足球队名单</h4>
     <div class="menu-世界杯预选赛2022直播
-container">
      <ul id="menu-世界杯预选赛2022直播
" class="menu">
       <li id="menu-item-89729" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-89729"><a href="//www.hotgingerla.com/clients/">2022世界杯足球队名单</a></li>
       <li id="menu-item-89730" class="menu-item menu-item-type-post_type menu-item-object-page current_page_parent menu-item-89730"><a href="//www.hotgingerla.com/brafton-blog/">blog</a></li>
       <li id="menu-item-89750" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-89750"><a href="//www.hotgingerla.com/resource-center/">resource center</a></li>
       <li id="menu-item-126964" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-126964"><a href="//www.hotgingerla.com/content-marketing/">what is content marketing?</a></li>
      </ul>
     </div>
    </div>
    <div id="nav_menu-7" class="widget widget_nav_menu">
     <h4 class="widgettitle">our services</h4>
     <div class="menu-content-marketing-container">
      <ul id="menu-content-marketing" class="menu">
       <li id="menu-item-89971" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-has-children menu-item-89971"><a href="//www.hotgingerla.com/content-marketing-services/">content marketing services</a>
        <ul class="sub-menu">
         <li id="menu-item-89741" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-89741"><a href="//www.hotgingerla.com/content-creation/">content creation services</a></li>
         <li id="menu-item-89744" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-89744"><a href="//www.hotgingerla.com/writing/">copywriting services</a></li>
         <li id="menu-item-89745" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-89745"><a href="//www.hotgingerla.com/graphic-design/">2022年世界杯抽签
</a></li>
         <li id="menu-item-89748" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-89748"><a href="//www.hotgingerla.com/video/">video marketing services</a></li>
        </ul></li>
       <li id="menu-item-89743" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-has-children menu-item-89743"><a href="//www.hotgingerla.com/content-strategy/">2022世界杯预选赛
</a>
        <ul class="sub-menu">
         <li id="menu-item-89740" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-89740"><a href="//www.hotgingerla.com/consulting/">marketing consulting services</a></li>
         <li id="menu-item-89738" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-89738"><a href="//www.hotgingerla.com/analytics/">content analytics</a></li>
         <li id="menu-item-89746" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-89746"><a href="//www.hotgingerla.com/seo/">seo content writing services</a></li>
        </ul></li>
       <li id="menu-item-89742" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-has-children menu-item-89742"><a href="//www.hotgingerla.com/promotions-engagement/">social media marketing services</a>
        <ul class="sub-menu">
         <li id="menu-item-89747" class="menu-item menu-item-type-post_type menu-item-object-page menu-item-89747"><a href="//www.hotgingerla.com/social-media-marketing/">social media consulting services</a></li>
        </ul></li>
      </ul>
     </div>
    </div>
    <div id="text-51" class="widget widget_text">
     <h4 class="widgettitle">us</h4>
     <div class="textwidget">
      <div class="logo">
       <img src="//www.hotgingerla.com/wp-content/themes/brafton2016/library/images/full_logo.png" alt="brafton - a content marketing agency">
      </div>
      <div class="cities">
       boston
       <div class="sep">
        <img src="//www.hotgingerla.com/wp-content/themes/brafton2016/library/images/bullet1.png" alt="sep">
       </div>chicago
       <div class="sep">
        <img src="//www.hotgingerla.com/wp-content/themes/brafton2016/library/images/bullet1.png" alt="sep">
       </div>san francisco
       <div class="sep">
        <img src="//www.hotgingerla.com/wp-content/themes/brafton2016/library/images/bullet1.png" alt="sep">
       </div>sydney
       <div class="sep">
        <img src="//www.hotgingerla.com/wp-content/themes/brafton2016/library/images/bullet1.png" alt="sep">
       </div>london
      </div>
      <div class="contact-info">
       <span class="phone">p. +1 617-206-3040</span>
       <span class="phone">p. +61 2 8973 1908</span>
       <span class="fax">f. 866.272.8112</span>
       <span class="email">e.<a href="mailto:info@brafton.com">info@brafton.com</a></span>
       <span class="email">support:<a href="mailto:techsupport@brafton.com">techsupport@brafton.com</a></span>
      </div>
     </div>
    </div>
   </div>
   <div class="source-org copyright">
    <div class="wrap">
     © 塞尔维亚vs瑞士走地
     <span style="display:none;">2022/12/06 13:02:04</span>
    </div>
   </div>
  </footer>
  <div id="translatemap" style="display: none;">
   { "en": { "lastname": { "placeholder": "testing-name", "submit": "go home" } }, "de": { "firstname": { "placeholder": "vorname", "label": "vorname", "validation": "dieses feld muss ausgefüllt werden", "submit": { "submit": "absenden", "get pricing!": "angebot erhalten", "default": "absenden" } }, "lastname": { "placeholder": "nachname", "label": "nachname", "validation": "dieses feld muss ausgefüllt werden" }, "inboundcomment": { "placeholder": "ihre nachricht", "label": "ihre nachricht", "validation": "dieses feld muss ausgefüllt werden" }, "website": { "placeholder": "website", "label": "website", "validation": "dieses feld muss ausgefüllt werden" } } }
  </div>
  <!-- cookie notice plugin v2.3.1 by hu-manity.co https://hu-manity.co/ -->
  <div id="cookie-notice" role="dialog" class="cookie-notice-hidden cookie-revoke-hidden cn-position-bottom" aria-label="cookie notice" style="background-color: rgba(33,33,33,1);">
   <div class="cookie-notice-container" style="color: #fff;">
    <span id="cn-notice-text" class="cn-text-container">we use cookies to improve your browsing experience. you can read more about our cookie policy in our<a href="//www.hotgingerla.com/privacy/">privacy statement.</a></span>
    <span id="cn-notice-buttons" class="cn-buttons-container"><a href="#" id="cn-accept-cookie" data-cookie-set="accept" class="cn-set-cookie cn-button cn-button-custom green-btn" aria-label="accept cookies">accept cookies</a></span>
    <span id="cn-close-notice" data-cookie-set="accept" class="cn-close-icon" title="deny"></span>
   </div>
  </div>
  <!-- / cookie notice plugin -->
  <!-- <script async src="https://use.fontawesome.com/548d31e453.js"></script> -->
  <!-- temporary for marketo -->
  <!-- end of site. what a ride! -->
 </body>
</html>