Articles Table

Recently I posted about what fields you might want to include if you were building a newsletters table so I thought somebody out there might find it useful if I posted information on how I'm building my Articles table. DevCampus is going to contain technical content in the form of articles so the Articles table is going to have quite a number of columns. This is your chance to provide feedback to me too, while I'm still building the database. I've visited various technical websites and looked at what they offer along with their articles to help form my ideas. Anyway, I'll get right to it:

Table: Articles

  1. Alias - (Primary Key) This varchar column is going to contain a unique identifier that I am calling an "alias" name. It will be used in URLs throughout the site to point to an article by it's alias. For example, if I wrote an article on database naming conventions, I might give it an alias of "DatabaseNamingConventions". Then, by having my default.aspx page look for an alias querystring name, be able to provide URL's like http://www.devcampus.com/?DatabaseNamingConventions (don't bother clicking it's just an example) in order to point to an article easily and also have a logical name (the alias) appear in the URL in an attempt to gather as much google juice as possible. This would also make constructing URL's dynamically very simple.
  2. AuthorSystemUserName (Foreign Key) - This column isn't named "AuthorId" because the reference (at least in my database) points to the "SystemUsers.Name" column. DevCampus authors will have to be registered system users so - for my situation the name makes sense. Man, I'm quite a nerd defending my naming choices, aren't I? Anyway - this column is pretty self explanitory.
  3. PublishedDate - Pretty obvious. The date the article is visible on the site.
  4. PublisherSystemUserName (Foreign Key) - In my case, I want to know who clicked the "publish" button if it wasn't me.
  5. LastEditDate - The last time a registered user edited the content.
  6. LastEditorSystemUserName (Foreign Key) - So I know who was the last one to touch the content of an article.
  7. CurrentVersion - all kinds of content on DevCampus will be editable by registered users so, it's also going to get versioned.
  8. PreviousVersionCount - how many previous versions of the content there have been.
  9. NumberOfRatings - how many people have rated the content
  10. RatingPoints - I'll divide this by value by NumberOfRatings to get a rating value from 1 to 5 stars.
  11. Ranking - unlike a "rating" - a "ranking" will be more of a popularity thing - from 1 to 10 scale instead of 1 to 5 - formula for this still to be determined.
  12. Title - Something more descriptive than the alias that can be displayed as the article title on a web page.
  13. TeaserText - also referred to as the "abstract" by some people.
  14. IsDraft - articles that are marked as drafts won't be visible on the site until they are published. This will help control what's published and allow authors to save their work from the sites admin tools.
  15. PageViews - how many times the article has been viewed on the site.
  16. FeedViews - how many times the article's feed has been accessed.
  17. FeaturedArticleImage - the name of the file to go with the article while it is a featured article in its respective department, course, or level.

Some other tables that will have a foreign key constraint referencing the Articles.Alias column:

  1. Comments - pretty obvious. To store comments submitted for various pieces of content.
  2. ToolBox - this table will store information about the technologies used/discussed in the content.
  3. RevisionHistory - this table will hold previous versions of the content.
  4. ArticlesDepartmentsCoursesLevels - this table will allow me to associate content with various different departments, courses and levels (levels are like 100, 200, 300, etc. under courses in the content hierarchy). An article on Atlas in ASP.NET 2.0 obviously spans across more than one course (at least ASP.NET 2.0, Atlas, and Ajax...maybe JavaScript, too) so I want to be able to have that many-to-many relationship information stored in this table.
  5. Downloads - a table that contains the location of files associated with the article; code, images, etc.

Well, that's the list so far - this is actually a fun exercise for me to do as I build portions of the DevCampus database. I'm actually thinking that I will create a course under the database technologies department for "What columns should I have in my table" that just has all kinds of examples for things like Products, Employees, Invoices, Vehicles, Orders, etc. Then anybody with some expertise in a certain domain can contribute to the table examples. Good stuff.

No Comments