Partilhar via

Adding Image Management to your Commerce Site Part(III)

  • Artigo

Let's take a trip to memory lane and see what we have learned so far. We gathered some requirements and from that we created an architecture design. Now we are going to try to develop what we have designed.

Product Catalog Database

We need to figure out our Database Design first. So let's get back to our requirements and see how this fits.

  • Images\files should support sequencing
  • Displaying multi lingual images
  • Support pageable searches
  • When deleting images\file records need to store the deleted names so IT personal can remove ghost images\files from file server
    • When deleting images make sure that there are no other association before removing file to deleted table
  • Need to support file paths
  • Image\files can be shared among other products

Tables

The ImageHierarchy table is used to join with <CatalogName>_CatalogProducts table for image\file association. ImageHierarchyID and ImageFileID are the primary key to keep uniqueness of files.

 ImageHierarchy
 Column Name  Data Type  Description
 ImageHierarchyID  int  An integer value for Image Hierarchy
 ImageFileID  int  An integer value used to join to Images table
 Sequence  int  sequence value for a given image\file
 ProductID  nvarchar(255)  The value of ProductID is used to join to <CatalogName>_CatalogProducts table to retrieve images for products
 VariantID  nvarchar(255)  The value of VariantID is used to join to <CatalogName>_CatalogProducts table to retrieve images for products Variants
 CategoryName  nvarchar(128)  The value of CategoryName is used to join to <CatalogName>_CatalogProducts table to retrieve images for Categories
 CatalogName  nvarchar(85)  Catalog where the image resides

The Images table records what type of image\file is stored and when it was created. You may want to create another record for ModifiedDate. The DeleteMe column can be used to do offline bulk operation if there are performance impacts.

 Images
 Column Name  Data Type  Description
 ImageFileID  int  An integer value to represent an image
 FeatureID  smallint  An integer value to join an image type
 CreateDate  datetime  Date that the image\file record was created (may need one more for ModifiedDate)
 DeleteMe  bit  A value to identify that the image can be deleted (may not need this)

The ImageType table records type of images\files that are supported by your system. To be consistent and organized we hold the path for the images based on type.

 ImageTypes
 Column Name  Data Type  Description
 FeatureID  smallint  An integer value representing the image type
 ImageType  nvarchar(128)  A value for the type of image\file
 Path  nvarchar(128)  Path to the image\file type

The Images_<language code> Need a table to hold multi lingual images\files. For this post I will assume that we do not have multilingual tables other than US English.

 Images_<language code> (i.e. en-US)
 Column Name  Data Type  Description
 DisplayName  nvarchar(128)  A value describing the image\file
 ImageName  nvarchar(128)  Image file name
 Height  nvarchar(4)  Image height
 Width  nvarchar(4)  Image Width
 ImageFileID  int  An integer value joining to Images table

Need a table to hold deleted image\files. You will use this table to generate batch scripts to clean out ghost images.

 Images_Deleted
 Column Name  Data Type  Description
 ImageFileID  int  An integer value representing the deleted image
 ImageName  nvarchar(128)  Image file name
 ImagePath  nvarchar(128)  Image path

 

T-SQL Code for Tables

/****** Object: Table [dbo].[ImageHierarchy] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO

CREATE TABLE [dbo].[ImageHierarchy]([ImageHierarchyID] [int] IDENTITY(1,1) NOT NULL,[ImageFileID] [int] NOT NULL,[Sequence] [int] NULL,[ProductID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[VariantID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CategoryName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CatalogName] [nvarchar](85) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,CONSTRAINT [PK_ImageHierarchy] PRIMARY KEY CLUSTERED ([ImageHierarchyID] ASC,[ImageFileID] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

GO

/****** Object: Table [dbo].[Images] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO

CREATE TABLE [dbo].[Images]([ImageFileID] [int] IDENTITY(1,1) NOT NULL,[FeatureID] [smallint] NULL,[CreateDate] [datetime] NULL,[DeleteMe] [bit] NULL,CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED ([ImageFileID] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

GO

/****** Object: Table [dbo].[Images_Deleted] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Images_Deleted]([ImageFileID] [int] NOT NULL,[ImageName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[ImagePath] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]

GO

/****** Object: Table [dbo].[Images_en-US] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Images_en-US]([DisplayName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ImageName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Height] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Width] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ImageFileID] [int] NULL) ON [PRIMARY]

GO

/****** Object: Table [dbo].[ImageTypes] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[ImageTypes]([FeatureID] [smallint] IDENTITY(1,1) NOT NULL,[ImageType] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Path] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,CONSTRAINT [PK_ImageTypes] PRIMARY KEY CLUSTERED ([FeatureID] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ImageHierarchy] WITH CHECK ADD CONSTRAINT [FK_ImageHierarchy_Images] FOREIGN KEY([ImageFileID])REFERENCES [dbo].[Images] ([ImageFileID])GO

ALTER TABLE [dbo].[ImageHierarchy] CHECK CONSTRAINT [FK_ImageHierarchy_Images]GO

ALTER TABLE [dbo].[Images] WITH CHECK ADD CONSTRAINT [FK_Images_ImageTypes] FOREIGN KEY([FeatureID])REFERENCES [dbo].[ImageTypes] ([FeatureID])GO

ALTER TABLE [dbo].[Images] CHECK CONSTRAINT [FK_Images_ImageTypes]GO

ALTER TABLE [dbo].[Images_en-US] WITH CHECK ADD CONSTRAINT [FK_Images_en-US_Images] FOREIGN KEY([ImageFileID])REFERENCES [dbo].[Images] ([ImageFileID])GO

ALTER TABLE [dbo].[Images_en-US] CHECK CONSTRAINT [FK_Images_en-US_Images]

 

View

Create a view that can retrieve an image or be searched. A view may be necessary for each language.

 

/****** Object: View [dbo].[GetImage] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[GetImage]ASSELECT dbo.ImageHierarchy.Sequence, dbo.[Images_en-US].DisplayName, dbo.[Images_en-US].ImageName, dbo.[Images_en-US].Height, dbo.[Images_en-US].Width, dbo.ImageTypes.ImageType, dbo.ImageTypes.Path, dbo.ImageHierarchy.ProductID, dbo.ImageHierarchy.VariantID, dbo.ImageHierarchy.CategoryName, dbo.ImageHierarchy.CatalogName, dbo.Images.ImageFileIDFROM dbo.ImageHierarchy INNER JOINdbo.Images ON dbo.ImageHierarchy.ImageFileID = dbo.Images.ImageFileID INNER JOINdbo.[Images_en-US] ON dbo.Images.ImageFileID = dbo.[Images_en-US].ImageFileID INNER JOINdbo.ImageTypes ON dbo.Images.FeatureID = dbo.ImageTypes.FeatureID

Stored Procedures

We need a stored procedure that searches on either FileName or FileDisplayName field. The stored procedure will also do pagination.

/****** Object: StoredProcedure [dbo].[img_Search] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Max Akbar-- Create date: -- Description: Search Images-- =============================================CREATE PROCEDURE [dbo].[img_Search] -- Add the parameters for the stored procedure here@keyword nvarchar(255) = null,@ImageFileID int = 1,@RecordsReturned intAS

DECLARE @Query nvarchar (4000)BEGIN-- SET NOCOUNT ON added to prevent extra result sets fromSET NOCOUNT ON;

SET @Query = 'SELECT TOP '+ CAST(@RecordsReturned as nvarchar(255)) +' GetImage.*FROM GetImageWHERE ((DisplayName = ''' + @keyword + ''') or (ImageName = ''' + @keyword + '''))and (ImageFileID > ' + CAST(@ImageFileID as nvarchar(255))+ ')'

EXEC sp_executesql @QueryEND

We also need a stored procedure for sequencing and ressequencing images\files.

/****** Object: StoredProcedure [dbo].[img_Resequence] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Max Akbar-- Create date: -- Description: Resequence images-- =============================================CREATE PROCEDURE [dbo].[img_Resequence] -- Add the parameters for the stored procedure here@ImageHierarchyID int,@ImageFileID int, @ProductID nvarchar(255),@Sequence intAS

DECLARE @oldSequence intDECLARE @OldImageHierarchyID intDECLARE @OldImageFileID int

BEGIN-- SET NOCOUNT ON added to prevent extra result sets fromSET NOCOUNT ON;

SELECT @oldSequence = SequenceFROM ImageHierarchyWHERE ImageHierarchyID = @ImageHierarchyIDand ImageFileID = @ImageFileIDSELECT @OldImageHierarchyID = ImageHierarchyID,@OldImageFileID = ImageFileIDFROM ImageHierarchyWHERE ProductID = @ProductIDand Sequence = @Sequence

UPDATE ImageHierarchySET Sequence = @SequenceWHERE ImageHierarchyID = @ImageHierarchyID and ImageFileID = @ImageFileID

IF @@ROWCOUNT > 0BEGIN

UPDATE ImageHierarchySET Sequence = @oldSequenceWHERE ImageHierarchyID = @OldImageHierarchyID and ImageFileID = @OldImageFileIDENDEND

Now we need stored procedures for CRUD operations.

/****** Object: StoredProcedure [dbo].[img_CreateImage] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Max Akbar-- Create date: -- Description: Create a Product Image\File record-- =============================================CREATE PROCEDURE [dbo].[img_CreateImage] -- Add the parameters for the stored procedure here@FeatureID int,@Sequence int,@ProductID nvarchar(255) = null,@VariantID nvarchar(255) = null,@CategoryName nvarchar(255) = null,@CatalogName nvarchar(85),@DisplayName nvarchar(255), @ImageName nvarchar(255), @Height nvarchar(4) = null, @Width nvarchar(4) = null,@language nvarchar(255) = 'en-US'AS

DECLARE @ImageID as nvarchar(255)

BEGIN-- SET NOCOUNT ON added to prevent extra result sets fromSET NOCOUNT ON;

BEGIN TRANSACTION -- insert the record into the image tableINSERT INTO Images(FeatureID, CreateDate)VALUES (@FeatureID, GetDate())

SELECT @ImageID = @@IDENTITY

-- insert a record into the ImageHierarchyINSERT INTO ImageHierarchy (ImageFileID, Sequence, ProductID, VariantID, CategoryName, CatalogName)VALUES (@ImageID, @Sequence, @ProductID, @VariantID, @CategoryName, @CatalogName)

-- insert a record into the language tableIF @language = 'en-US'BEGIN-- this should be stored procedureINSERT INTO [Images_en-US] (DisplayName, ImageName, Height, Width, ImageFileID)VALUES (@DisplayName, @ImageName, @Height, @Width, @ImageID)END

IF(@@Error=0)BEGINCOMMIT TRANSACTIONENDELSEBEGINROLLBACK TRANSACTIONEND

END

GO/****** Object: StoredProcedure [dbo].[img_DeleteCategory] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Max Akbar-- Create date: -- Description: Create a Product Image\File record-- =============================================CREATE PROCEDURE [dbo].[img_DeleteCategory]-- Add the parameters for the stored procedure here@ImageFileID int,@CategoryName nvarchar(255),@language nvarchar(255) = 'en-US'AS

DECLARE @ImageName nvarchar(255)DECLARE @Path nvarchar(255)

BEGIN-- SET NOCOUNT ON added to prevent extra result sets fromSET NOCOUNT ON;

-- check to make sure it's ok to delete the product.SELECT * FROM GetImage WHERE (ImageFileID = @ImageFileID)IF @@ROWCOUNT <> 0BEGINRETURNEND

SELECT @ImageName = ImageName, @Path = Path FROM GetImageWHERE (ImageFileID = @ImageFileID) and (CategoryName = @CategoryName)IF @@ROWCOUNT = 0BEGINRETURNENDBEGIN TRANSACTION -- delete a record into the ImageHierarchyDELETE FROM ImageHierarchyWHERE (ImageFileID = @ImageFileID) and (CategoryName = @CategoryName)

-- delete a record into the language tableIF @language = 'en-US'BEGIN-- this should be stored procedureDELETE FROM [Images_en-US]WHERE (ImageFileID = @ImageFileID)END -- delete the record into the image tableDELETE FROM ImagesWHERE (ImageFileID = @ImageFileID)

INSERT INTO Images_Deleted (ImageFileID, ImageName, ImagePath)VALUES (@ImageFileID, @ImageName, @Path)

IF(@@Error<>0)BEGINCOMMIT TRANSACTIONENDELSEBEGINROLLBACK TRANSACTIONEND

END

GO/****** Object: StoredProcedure [dbo].[img_DeleteProduct] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Max Akbar-- Create date: -- Description: Create a Product Image\File record-- =============================================CREATE PROCEDURE [dbo].[img_DeleteProduct]-- Add the parameters for the stored procedure here@ImageFileID int,@ProductID nvarchar(255),@language nvarchar(255) = 'en-US'AS

DECLARE @ImageName nvarchar(255)DECLARE @Path nvarchar(255)

BEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;

-- check to make sure it's ok to delete the product.SELECT * FROM GetImage WHERE (ImageFileID = @ImageFileID)IF @@ROWCOUNT <> 0BEGINRETURNEND

SELECT @ImageName = ImageName, @Path = Path FROM GetImageWHERE (ImageFileID = @ImageFileID) and (ProductID = @ProductID)IF @@ROWCOUNT = 0BEGINRETURNEND

BEGIN TRANSACTION -- delete a record into the ImageHierarchyDELETE FROM ImageHierarchyWHERE (ImageFileID = @ImageFileID) and (ProductID = @ProductID)

-- delete a record into the language tableIF @language = 'en-US'BEGIN-- this should be stored procedureDELETE FROM [Images_en-US]WHERE (ImageFileID = @ImageFileID)END

-- delete the record into the image tableDELETE FROM ImagesWHERE (ImageFileID = @ImageFileID)

INSERT INTO Images_Deleted (ImageFileID, ImageName, ImagePath)VALUES (@ImageFileID, @ImageName, @Path)

IF(@@Error=0)BEGINCOMMIT TRANSACTIONENDELSEBEGINROLLBACK TRANSACTIONEND

END

GO/****** Object: StoredProcedure [dbo].[img_DeleteVariant] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Max Akbar-- Create date: -- Description: Create a Product Image\File record-- =============================================CREATE PROCEDURE [dbo].[img_DeleteVariant] -- Add the parameters for the stored procedure here@ImageFileID int,@ProductID nvarchar(255),@VariantID nvarchar(255),@language nvarchar(255) = 'en-US'AS

DECLARE @ImageName nvarchar(255)DECLARE @Path nvarchar(255)

BEGIN-- SET NOCOUNT ON added to prevent extra result sets fromSET NOCOUNT ON;

-- check to make sure it's ok to delete the product.SELECT * FROM GetImage WHERE (ImageFileID = @ImageFileID)IF @@ROWCOUNT <> 0BEGINRETURNEND

SELECT @ImageName = ImageName, @Path = Path FROM GetImageWHERE (ImageFileID = @ImageFileID) and (VariantID = @VariantID) and (ProductID = @ProductID)IF @@ROWCOUNT = 0BEGINRETURNEND

BEGIN TRANSACTION-- delete a record into the ImageHierarchyDELETE FROM ImageHierarchyWHERE (ImageFileID = @ImageFileID) and (VariantID = @VariantID) and (ProductID = @ProductID)

-- delete a record into the language tableIF @language = 'en-US'BEGIN-- this should be stored procedureDELETE FROM [Images_en-US]WHERE (ImageFileID = @ImageFileID)END

-- delete the record into the image tableDELETE FROM ImagesWHERE (ImageFileID = @ImageFileID)

INSERT INTO Images_Deleted (ImageFileID, ImageName, ImagePath)VALUES (@ImageFileID, @ImageName, @Path)

IF(@@Error<>0)BEGINCOMMIT TRANSACTIONENDELSEBEGINROLLBACK TRANSACTIONEND

END

GO/****** Object: StoredProcedure [dbo].[img_GetImagecategory] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Max Akbar-- Create date: -- Description: Get Image based on CategoryName-- =============================================CREATE PROCEDURE [dbo].[img_GetImagecategory]-- Add the parameters for the stored procedure here@CategoryName nvarchar(255) = nullASBEGIN-- SET NOCOUNT ON added to prevent extra result sets fromSET NOCOUNT ON;

SELECT Sequence, DisplayName, ImageName, Height, Width, ImageType, Path, ProductID, VariantID, CategoryNameFROM GetImageWHERE (CategoryName = @CategoryName)

END

GO/****** Object: StoredProcedure [dbo].[img_GetImageProduct] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Max Akbar-- Create date: -- Description: Get Image based on ProductID-- =============================================CREATE PROCEDURE [dbo].[img_GetImageProduct] -- Add the parameters for the stored procedure here@ProductID nvarchar(255) = nullASBEGIN-- SET NOCOUNT ON added to prevent extra result sets fromSET NOCOUNT ON;

SELECT Sequence, DisplayName, ImageName, Height, Width, ImageType, Path, ProductID, VariantIDFROM GetImageWHERE (ProductID = @ProductID)

END

GO/****** Object: StoredProcedure [dbo].[img_GetImageProductVariant] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Max Akbar-- Create date: -- Description: Get Image based on VariantID-- =============================================CREATE PROCEDURE [dbo].[img_GetImageProductVariant]-- Add the parameters for the stored procedure here@ProductID nvarchar(255) = null,@VariantID nvarchar(255) = nullASBEGIN-- SET NOCOUNT ON added to prevent extra result sets fromSET NOCOUNT ON;

SELECT Sequence, DisplayName, ImageName, Height, Width, ImageType, Path, ProductID, VariantIDFROM GetImageWHERE (ProductID = @ProductID) and (VariantID = @VariantID)

END

GO/****** Object: StoredProcedure [dbo].[img_Resequence] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Max Akbar-- Create date: -- Description: Create a Product Image\File record-- =============================================CREATE PROCEDURE [dbo].[img_updateImageHierarchytbl] -- Add the parameters for the stored procedure here@ImageFileID int, @Sequence int, @ProductID nvarchar (255), @VariantID nvarchar(255), @CategoryName nvarchar(255), @CatalogName nvarchar(85),@ImageHierarchyID intAS

BEGIN-- SET NOCOUNT ON added to prevent extra result sets fromSET NOCOUNT ON;

BEGIN TRANSACTION -- insert a record into the ImageHierarchyUPDATE ImageHierarchySET ImageFileID = @ImageFileID, Sequence = @Sequence, ProductID = @ProductID, VariantID = @VariantID, CategoryName = @CategoryName, CatalogName = @CatalogNameWHERE (ImageHierarchyID = @ImageHierarchyID)

IF(@@Error=0)BEGINCOMMIT TRANSACTIONENDELSEBEGINROLLBACK TRANSACTIONEND

END

GO/****** Object: StoredProcedure [dbo].[img_UpdateImagetbl] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Max Akbar-- Create date: -- Description: Create a Product Image\File record-- =============================================CREATE PROCEDURE [dbo].[img_UpdateImagetbl] -- Add the parameters for the stored procedure here@FeatureID int,@ImageFileID intAS

BEGIN-- SET NOCOUNT ON added to prevent extra result sets fromSET NOCOUNT ON;

BEGIN TRANSACTION -- insert the record into the image tableUPDATE ImagesSET FeatureID = @FeatureIDWHERE (ImageFileID = @ImageFileID)

IF(@@Error=0)BEGINCOMMIT TRANSACTIONENDELSEBEGINROLLBACK TRANSACTIONEND

END

GO/****** Object: StoredProcedure [dbo].[img_UpdateLanguagetbl] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Max Akbar-- Create date: -- Description: Create a Product Image\File record-- =============================================CREATE PROCEDURE [dbo].[img_UpdateLanguagetbl]-- Add the parameters for the stored procedure here@ImageFileID nvarchar(255),@DisplayName nvarchar(255), @ImageName nvarchar(255), @Height int, @Width int,@language nvarchar(255) = 'en-US'AS

BEGIN-- SET NOCOUNT ON added to prevent extra result sets fromSET NOCOUNT ON;

BEGIN TRANSACTION -- insert the record into the image tableIF @language = 'en-US'BEGIN-- this should be stored procedureUPDATE [Images_en-US]SET DisplayName = @DisplayName,ImageName = @ImageName,Height = @Height,Width = @WidthWHERE (ImageFileID = @ImageFileID)END

IF(@@Error=0)BEGINCOMMIT TRANSACTIONENDELSEBEGINROLLBACK TRANSACTIONEND

END

Data file to update image types.
Note: You may want to add this as a requirement so that your business users can perform CRUD operations. For this example we are going to create several image file types using T-SQL. A PDF file type, an image file type for Product and Categories and finally EndCaps. I will explain EndCaps in another blog post basically an EndCap is a retail industry word describing the display fixture located at the end of an aisle. So imagine having your product listing page as an aisle with an EndCap. Next time you go to a drug store check out the end of the aisle for an EndCap for promotional purposes.

We are going to keep all product images in one file but if you would like to keep multilingual products separate then I would suggest adding a column to the images table and concatenate ImageTypes column with Image column to get the full image path.

INSERT INTO ImageTypes (ImageType, Path)VALUES ('Product', 'Images\Products')GO

INSERT INTO ImageTypes (ImageType, Path)VALUES ('Product', 'Images\Categories')GO

INSERT INTO ImageTypes (ImageType, Path)VALUES ('PDF', 'Images\PDFs')GOINSERT INTO ImageTypes (ImageType, Path)VALUES ('EndCap', 'Images\EndCaps')GO

Database Performance Considerations

If you have multiple catalogs then you may want to create a set of image tables for each catalogs. Only do this if you have over 100K products per catalog (I am flying by the seat of my pants) but you need to decide what the threshold is by running performance tests. All productID, VariantID, CategoryName and CatalogNames should be the same value as the Product Catalog and must have indexes. Make sure to test the stored procedures under performance peak operations and collect performance data to be analyzed by your DBA. You must test the performance under realistic values for example if your Product Catalog has 100K SKU's and each SKU has an average of two images then you must pre fill the image tables with that amount of data before running performance tests. Make sure to run the test with comparable hardware as your production. Make sure to run database consistency checks and reindexing procedures during non peak hours.

Staging Consideration

SQL replication may require database changes. If you are using Commerce Server Staging then there may be a chance that you will have out of synch issues for short periods of time between the image table and Product Catalog data. Make sure that you have code in your site that checks to see if there is an image associated with the product if not display no image found dummy image. You will need to create a dummy image with the text no image found for the use of dummy image. At a later post I will show you how to extend the Commerce Server Staging to give you finer control of Product Catalog and any other associated tables. Keep your stored procedures simple and put most of your business logic into the API, this will make feature addins much easier.

Design Problems

I have already described a scenario where you will have out of synch issues of Images\files and Product Catalog Data. So I will solve this in another post about extensibility of Commerce Server Staging.

Test your Database Design

Call your stored procedures to make sure that they execute within your design. I have created some sample T-SQL Scripts that you can use for your testing.

Summary

So we now have a database design that will be the foundation for our Development Design. We may have to come back and revisit and tweak the database objects based on our Development Design. After reading this post it would be a good idea to learn about patterns specifically the Bridge , Factory Pattern and Agent Design. Don't forget to read up on UIP. Here are some links that will help you:

https://www.dofactory.com/Patterns/PatternBridge.aspx

https://www.dofactory.com/Patterns/PatternAbstract.aspx

https://www.dofactory.com/Patterns/PatternFactory.aspx

https://msdn.microsoft.com/practices/

I have also posted some test scripts see attachment.

Test Scripts.zip

Comments

  • Anonymous
    November 01, 2006
    Absolutely awesome Max!

  • Anonymous
    November 01, 2006
    You are too kind Jeff and stop making me blush...:)

  • Anonymous
    March 02, 2007
    How about storing the images in the database instead of keeping them on file system? Do you think that would be decreasing perfomance somehow? In a large e-commerce store where there may be hundreds of thousands of product images, Organizing this much number of images in filesystem may be somewhat difficult. In such case, which approach do you recommend?

  • Anonymous
    March 05, 2007
    Depends on what you mean by performance. There are two kinds of image keeping here. One Centralized keeping the images in the database. Yes, you will impact a slight performance hit but you would have to do a proof of concept to validate if you can live with the performance issues. Two decentralized keeping images on the files system. So both have pros and cons you have to look at your requirements and see which is the best fit. I recomend the file system. -Max