The Product

In building a shopping cart application you always have to remember that the product is the backbone of the application, well at least to the customer it is. The reason a customer needs a shopping cart is because they need the ability to sell their product so I always like to remind myself the why! With that being said the Product Table and model are my next steps in building a rock solid core. The first thing I would like to visit is the product table and define the basic fields. This is just a very basic example of what I believe are the essentials. You may need more and you may even need less and usually your application needs will drive that question.

Product Table

• ProductId(int) - This is just a primary key in our table and I like using incremental int as long as it is not information that security is a main factor of. The product id can and will be public knowledge.
• Name(varchar(100)) - The name of the product
• SKU(varchar(50)) - SKU (http://en.wikipedia.org/wiki/Stock_Keeping_Unit - thanks to Dan Wilson.
• Price(float) - This is the price that the product will sell for in your application.
• SalePrice(float) - This will be the price of the product if it goes on sale. If the item is not on sale this can either be 0 o NULL.
• ShortDesc(text) - I am still not settled on what I want to do with the decription. It seems most shops offer a short description and a description which I can completely comprehend. The only problem I have is that it violates a major rule of mine, never duplicate data! I tend to think that with data duplication comes an architecture flaw somewhere. For now we will have to separate fields here.
• Decription(text) - This will be the full text of he description, it can contain text & html.
• Featured(bit) - This will be a flag in our system for this product. Feaured products are good for home page promotions, banners and cross selling.
• Active(bit) - The end all be all of display. I like to put some type of flag in the system to know if this product is visible to customers. Only logged in as an administrator will you see these items in view. This is a great way of checking your product detail pages content before launching the product.
• DisplayOrder(int) - I think I am going to use this field for much smaller shops. In a regular cart you would be displaying from product name (a-z,z-a) or by price (lowest-highest,highest-lowest) but on a site where they are selling less than 10 items a display might come in handy.
• Weight(float) - I have found that some customers do not care about weight but those who want precise shipping calculations rely on it. Even though some users will not use it I still consider weight tracking essential.
• Image(varchar(100)) I have struggled with this and gone back and forth with what I want to do. For now we are going to store the name of the image(the name not the path). We could just store a mast image and write a bunch of sizes of the image but for now I think we will just stick with a full and a thumb. This image will be shown on our product detail page.
• Thumbnail(varchar(100)) - This is the name of the thumbnail image. This image will be used when displaying a list of products.

There it is in all of its glory, my product essentials. I am sure I have left off items that you feel are essential but I guess this list can always be viewed on a need by need basis. Now that that is covered lets see what I left out and why.

The first thing is category, how do I tie this to a category. You could always have a category field that either contains a single category(tying a product to 1 cat) or delimited list of category id's. I prefer to have a category table and a ProductToCategory table that contains foreign keys for both the product and category. I will show you a working example of this later on.

We also have not discussed how to handle product attributes. First thing you should do is read this article by Peter Bell where he talks about this very item. He really did a good job at breaking down this problem. What is a product attribute? Lets say you sell t shirts, that same shirt has different sizes and colors. The sizes and colors are are attributes of the t shirt. It really gets tricky when you start thinking in terms of inventory though. Your shop could track a small green shirt and a large black shirt of the same shirt as 2 separate items. I think for now I am going to use Peter's approach where I will have only 1 product but that 1 product can have many attributes. This solution is a good start plus it makes it easier on the customer when entering products.

Finally, what about product images. Some products may have 10 even 20 images. Again i think is better handled using sereration. I would create a table called ProductImage that would contain an id,productId, and image name. This would then make it easy to iterate images for each product.

Well thats all I have for this time. In my next post we will dive into Categories and I will post some code that will tie our products and categories together.