Before diving into creating our forms and data views, we need to first think about our data and how it will be stored. Once we've setup our tables, we'll find that XMod Pro makes it simple to create our forms and views.

Articles Table (XMP_Articles)

Our application will consist of two tables. One for articles and the other for authors. Let's start with the core data structure in our application - the article. This is where most of the action will happen. All other tables will be in service to this table. I've laid out the design of the table below:

The data is pretty straight forward. Every article will have its own unique numeric ID. Every article must have an author, so we store that person's ID along with the article. We're also tracking the basic components of any article - the Title, a Synopsis or "teaser", and the actual article (Body). Beyond that, we're tracking basic stats - the date the article was first submitted (Created) and last modified (Updated).

While this is going to be a basic article management system, any system worth its salt will allow a modicum of workflow. In our system, we will allow editors to approve articles for publication (Active) as well as schedule their publication date (Publish).

Authors Table (XMP_ArticleAuthors)

With XMod Pro we could easily hook our articles system into the built-in DNN user database. This would allow us to have a single-table article management system. While that's cool, it doesn't give us the opportunity to show how to join data from multiple tables in XMP. More importantly, many publications have articles submitted to them from outside their site - op-eds, guest articles, etc. It could be cumbersome to force those authors to register on the site. Plus, even users who are registered on your site might want a different bio and image displayed with their articles. So, we're going to create a separate Authors Table.

The AuthorId will be the number that uniquely identifies the author's record in our system. So, to assign authorship to an article, we simply have to store that author's ID. That will link it back to the author's record in this table and we can grab additional information like the author's picture and bio. If we didn't do this, we'd have to store the author's name, bio, and image with each article, duplicating a lot of information for no good reason. Plus, if an author's Bio ever changed, we'd have to edit each article she had ever written. This process of removing duplicated data through linked tables is called Normalization. One other advantage is that this approach makes it easy to add more information we want to track about the author in the future - like website, email address and more.

Data Types

If you're new to databases, you may be wondering what those data types are. The list below is by no means a complete data type reference. We're just covering what you need to know —the data types we've used thus far— rather that write a SQL book.

• int: This is an Integer data-type. This stores whole numbers (i.e. no fractions or floating point numbers). It can hold positive or negative numbers but in our case - and in most cases - we'll only be storing positive numbers.
• nvarchar(99): This is a text field. The char means this will store characters. The var indicates that it stores a variable number of characters. The n means that it can hold international characters. Your application may only need to store English text, but if you have an international audience (and who doesn't on the internet) then use the n variant. Finally, the number in parentheses indicates the maximum number of characters this column will allow. So, a Title can be up to 80 characters and a Synopsis can be as long as 500 characters in length. If you set it to MAX, then you can store up to 2GB of text. In all but the most extreme cases, you'll never hit that limit.
• datetime: As its name implies, this data type is used to store a date value with an additional time component. Since we want the Created date and Updated date to reflect the date and time of day, this is the data type to use.
• bit: If you're not familiar with databases or programming, this may seem a "bit" odd to you (pun intended). A bit field can be thought of as a simple true/false field. Technically it stores either a zero (0) or one (1) as its value.

Creating Our Tables

XMod Pro provides a built-in tool to create tables in your DNN database. However, it is designed for one-off situations or prototypes. I recommend you download Microsoft's SQL Server Management Studio Express (or the full version if you can) as that will provide you with a complete database management tool. The Express version is free. Your host may also provide you with a tool you can use for creating tables and stored procedures. Your last option is to execute SQL commands using DNN's SQL tool. It's a very spartan tool, though the most recent version has markedly improved, adding color-coded syntax highlighting.

I've included the SQL Creation scripts below if you'd like to execute/modify the scripts yourself

SQL Script: Creating the Articles Table

CREATE TABLE [dbo].[XMP_Articles](
    [ArticleId] [int] IDENTITY(1,1) NOT NULL,
    [AuthorId] [int] NOT NULL,
    [Title] [nvarchar](80) NOT NULL,
    [Synopsis] [nvarchar](500) NULL,
    [Body] [nvarchar](max) NULL,
    [Created] [datetime] NULL,
    [Updated] [datetime] NULL,
    [Published] [datetime] NULL,
    [Active] [bit] NULL,
 CONSTRAINT [PK_XMP_Articles] PRIMARY KEY CLUSTERED 
(
    [ArticleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
  

SQL Script: Creating the Author's Table

CREATE TABLE [dbo].[XMP_ArticleAuthors](
    [AuthorId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Bio] [nvarchar](500) NULL,
    [Image] [nvarchar](255) NULL
) ON [PRIMARY]

GO