'Serving the Microsoft® Access
Community since 1997'

This article will examine the structure of the two (2) databases involved in this little application. Keep in mind that I have purposely kept the database structures very simple.

Here is the structure of the MS Access database used on the local PC. First the tables.

• tblArticles - used to hold all the data for the articles
• tblCategories - contains a list of the categories used to classify the articles
• tblWebsites - list of websites that we want to transfer data to (this system allows unlimited websites on multiple hosting servers - well, limited only by Access)

Then the fields in each table.

• tblArticles
  • ArticleID - Long Integer - a standard primary key set up as an autonumber
  • WebSiteID - Long Integer - foreign key for linking to the tblWebsites table
  • CategoryID - Long Integer - foreign key for linking to the tblCategories table
  • PublishDate - Date/Time - the date and time that we want the article to be published (can be dated in future or past, although dating in past before current date is not currently utilized by any logic) The article will not appear on the website until the date occurs
  • ArticleTitle - Text(50) - the full formal title of the article
  • ArticleLinkTitle - Text(50) - a link version of the title; all lower case, dashes replace spaces, only a-z and 0-9 allowed (and the dashes)
  • ArticleSummary - Memo - holds a short summary of the article
  • ArticleText - Memo - the full body of the article (text field type is similar to memo fields in holding power)
  • ArticleKeywords - Text(100) - keywords for the article (used in the Keyword meta tag of the html)
  • HasBeenPublished - Yes/No - a simple flag to know the article was successfully published on the website
• tblCategories
  • CategoryID - Long Integer - primary key (autonumber)
  • WebSiteCategoryID - Long Integer - basically a foreign key for the CategoryID field in the MySQL version of this database (MUST match the data from the MySQL version)
  • WebsiteID - Long Integer - foreign key for linking to the Websites table (needed since different websites have different categories) (not necessarily same numeric values as the MySQL version)
  • CategoryName - Text(50) - holds the category description, used for local reference on the PC (should match MySQL version)
• tblWebsites
  • WebSiteID - Long Integer - primary key (not necessarily same numeric values as the MySQL version)
  • WebSiteTitle - Text(50) - a designated full text name of the website; should match the MySQL version of the data
  • WebSiteURL - Text(50) - the URL (including http://) to the home page of the website
  • WebSiteFTP - Text(50) - the address to the website's FTP server
  • WebSiteFolderName - Text(50) - the folder on the FTP server where the temporary files go to be read and copied into the database (I usually use the same folder as the index.php or index.html file for the website, but another folder could be used to tighten security)
  • WebSiteUsername - Text(50) - the name of the user for the FTP account (the password os not stored in the database and must be typed in each time the transfer takes place)

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Here is the structure of the MySQL database used on the websites. Tables first.

• Articles - used to hold all the data for the articles
• Categories - contains a list of the categories used to classify the articles
• Websites - list of websites that use the same database

Note that I have a table for Websites. Since a MySQL database can be used by multiple websites hosted under the same account, I needed this table. Both the Acne site and the Backpacking site are hosted under a single account at Host Gator, a well known hosting company.

Let's look at the fields in each table.

• Articles
  • ArticleID - int(11) - a standard primary key set up as an autonumber just like I use in my Access tables
  • ArticleTitle - varchar(100) - the full formal title of the article
  • ArticleTitleShort - varchar(100) - a link version of the title; all lower case, dashes replace spaces, only a-z and 0-9 allowed (and the dashes), other characters (like '?') are stripped out
  • ArticleText - text - the full body of the article (text field type is similar to memo fields in holding power)
  • ArticleSummary - text - holds a short summary of the article
  • ArticlePublishDate - datetime - the date and time that the article was published
  • ArticleCategoryID - int(11) - foreign key for linking to the Categories table
  • ArticleWebsiteID - int(11) - foreign key for linking to the Websites table
  • ArticleKeywords - varchar(100) - keywords for the article (used in the Keyword meta tag of the html)
• Categories
  • CategoryID - int(11) - primary key
  • CategoryText - varchar(28) - holds the category description, used to populate the Categories section of the left sidebar of the Acne site (not used on the Backpacking site)
  • CategoryTextShort - varchar(28) - same type of purpose and construction as the ArticleTitleShort field in the Articles table
  • WebsiteID - int(11) - foreign key for linking to the Websites table (needed since different websites have different categories)
• Websites
  • WebsiteID - int(11) - primary key
  • WebsiteName - varchar(28) - a designated full text name of the website
  • WebsiteURL - varchar(50) - the URL (including http://) to the home page of the website

(article continues after sponsor spot)

Some notes:

1. An article will only appear to visitors once its PublishDate occurs. An enhancement would have a start and end date so that an article can be made to only appear between those dates. Even better would be a separate table which would allow multiple start and end dates for each article.
2. Have you noticed that my naming styles are not consistent? Bad Richard. I figured that I would not bother faking it, so you can see that I sometimes do not follow my own standards.
3. As are many of my readers, I am just learning php and MySQL, and thus my choice of field types may not be optimal. (but the thing works!)
4. While proofreading this after posting to my website, I decided that I should have put most of this article into a multi-column table format. It would be easier to read. I'll fix that up another day. Too much to do.

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.