Before creating product and category functionality, let’s think about the information our customers and site administrators will need. We must provide enough information about the product, including the product name, detailed description, and price. We may also display a product photo and additional images. The product weight may be needed to calculate shipping costs. We also need to track the quantity in stock to avoid selling out-of-stock items. For the site administrator, we'll need a product ID and a human-readable URL for search engines.
So, at a minimum, we should have the following fields:
| Field |
Description |
| ID |
Product number for internal site operations |
| Name |
Product name |
| URL alias |
Mainly for search engines |
| Description |
Detailed product info |
| SKU |
Stock-keeping unit for users |
| Price |
Product price |
| Stock |
Product quantity in stock |
| Primary image |
Main product image |
| Additional images |
Extra product photos |
In future articles, we will expand this list, but for now, this is sufficient.
Product categories have their own fields:
| Field |
Description |
| ID |
Category ID for internal site operations |
| Name |
Category name |
| Description |
Category description |
| URL alias |
Search engine-friendly URL |
We could move forward and implement data structure and functionality for displaying products and categories using our framework, but doing so now might limit flexibility. Much of the content displays common information. If we identify this shared information and create an abstract content type, our framework will be more flexible. We could then use various features for these content types without duplicating code. Such features could include:
- Content versioning
- Access control
- Comments on content, pages, products
- Ratings for pages and products
Pages
Pages are static site sections: Contact, delivery and payment info, etc. The data for these pages are as follows:
| Field |
Description |
| Name |
Page name |
| URL alias |
Page URL |
| ID |
Internal page ID |
| Heading |
H1 tag content |
| Title |
<title> tag content |
| Content |
Page content |
| Keywords |
Meta keywords tag |
| Description |
Meta description tag |
Content
Pages are the main content type that we'll extend when we need to store more fields. Category pages are essentially the same but require more fields to relate to products.
| Field |
Description |
| ID |
Internal ID |
| Name |
Content name |
| URL alias |
|
| Content |
Content body |
| Type |
Content type (e.g., page, product, category) |
| Order |
Content sort order, e.g., for menu items |
| Parent |
Parent content (for hierarchy, subcategories, etc.) |
| Meta keywords |
SEO keywords |
| Meta description |
SEO meta description |
| Date created |
Date of creation |
| Creator |
Author |
| Active |
Show content to visitors or not |
| Secure |
Restrict access, e.g., to registered users only |
Revisions
Drupal has a revision system; we’ll build a similar one.
| Field |
Description |
| ID |
Internal content ID |
| Current revision |
Current revision number |
Now that we've planned our content types, let’s build our database. Each type will have its own table. We should have these tables:
| Table |
Description |
| Content |
Stores relationships of active content versions with static data like author |
| Versions |
Stores data for specific content revisions |
| Content types |
Lists framework content types and relates content to type |
| Products |
Extends product-specific data |
| Revision history |
Saves revision history |
Content
All site content will be stored in this table. If we want to extend a content type, we’ll do so in a separate table. For example, products will be stored in the Content table but linked to the Products table via ID.
| Field |
Type |
Description |
| ID |
Integer (auto increment) |
Key for linking to other tables |
| Current_revision |
Integer |
Link to versions table |
| Active |
Boolean |
Published status |
| Secure |
Boolean |
Restricted to authenticated users |
| Parent |
Integer |
Parent content relationship |
| Order |
Integer |
Sort order |
| Author |
Integer |
ID of content creator |
| Type |
Integer |
Content type ID |
| Path |
Varchar |
Human-readable URL |
Now we can run the following SQL to create the Content table:
CREATE TABLE `content` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`current_revision` int(11) NOT NULL,
`active` tinyint(1) NOT NULL,
`secure` tinyint(1) NOT NULL,
`parent` int(11) NOT NULL,
`order` int(11) NOT NULL,
`author` int(11) NOT NULL,
`type` int(11) NOT NULL,
`path` char(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
