Adding Image Management to your Commerce Site Part(III)
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')GOINSERT 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.