Max Akbar : Adding Image Management to your Commerce Site Part(III)

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 ON
GO
SET QUOTED_IDENTIFIER ON
GO

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]

/****** Object: Table [dbo].[Images] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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]

/****** Object: Table [dbo].[Images_Deleted] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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]

/****** Object: Table [dbo].[Images_en-US] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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]

/****** Object: Table [dbo].[ImageTypes] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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]

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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[GetImage]
AS
SELECT 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.ImageFileID
FROM dbo.ImageHierarchy INNER JOIN
dbo.Images ON dbo.ImageHierarchy.ImageFileID = dbo.Images.ImageFileID INNER JOIN
dbo.[Images_en-US] ON dbo.Images.ImageFileID = dbo.[Images_en-US].ImageFileID INNER JOIN
dbo.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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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 int
AS

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

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

EXEC sp_executesql @Query
END

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

/****** Object: StoredProcedure [dbo].[img_Resequence] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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 int
AS

DECLARE @oldSequence int
DECLARE @OldImageHierarchyID int
DECLARE @OldImageFileID int

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

SELECT @oldSequence = Sequence
FROM ImageHierarchy
WHERE ImageHierarchyID = @ImageHierarchyID
and ImageFileID = @ImageFileID
SELECT @OldImageHierarchyID = ImageHierarchyID,
@OldImageFileID = ImageFileID
FROM ImageHierarchy
WHERE ProductID = @ProductID
and
Sequence = @Sequence

UPDATE ImageHierarchy
SET Sequence = @Sequence
WHERE ImageHierarchyID = @ImageHierarchyID
and
ImageFileID = @ImageFileID

IF @@ROWCOUNT > 0
BEGIN

UPDATE ImageHierarchy
SET Sequence = @oldSequence
WHERE ImageHierarchyID = @OldImageHierarchyID
and
ImageFileID = @OldImageFileID
END
END

Now we need stored procedures for CRUD operations.

/****** Object: StoredProcedure [dbo].[img_CreateImage] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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 from
SET NOCOUNT ON;

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

SELECT @ImageID = @@IDENTITY

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

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

IF(@@Error=0)
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END

END

GO
/****** Object: StoredProcedure [dbo].[img_DeleteCategory] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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 from
SET NOCOUNT ON;

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

SELECT @ImageName = ImageName, @Path = Path FROM GetImage
WHERE (ImageFileID = @ImageFileID) and (CategoryName = @CategoryName)
IF @@ROWCOUNT = 0
BEGIN
RETURN
END
BEGIN TRANSACTION
-- delete a record into the ImageHierarchy
DELETE FROM ImageHierarchy
WHERE (ImageFileID = @ImageFileID) and (CategoryName = @CategoryName)

-- delete a record into the language table
IF @language = 'en-US'
BEGIN
-- this should be stored procedure
DELETE FROM [Images_en-US]
WHERE (ImageFileID = @ImageFileID)
END
-- delete the record into the image table
DELETE FROM Images
WHERE (ImageFileID = @ImageFileID)

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

IF(@@Error<>0)
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END

END

GO
/****** Object: StoredProcedure [dbo].[img_DeleteProduct] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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 <> 0
BEGIN
RETURN
END

SELECT @ImageName = ImageName, @Path = Path FROM GetImage
WHERE (ImageFileID = @ImageFileID) and (ProductID = @ProductID)
IF @@ROWCOUNT = 0
BEGIN
RETURN
END

BEGIN TRANSACTION
-- delete a record into the ImageHierarchy
DELETE FROM ImageHierarchy
WHERE (ImageFileID = @ImageFileID) and (ProductID = @ProductID)

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

-- delete the record into the image table
DELETE FROM Images
WHERE (ImageFileID = @ImageFileID)

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

IF(@@Error=0)
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END

END

GO
/****** Object: StoredProcedure [dbo].[img_DeleteVariant] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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 from
SET NOCOUNT ON;

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

SELECT @ImageName = ImageName, @Path = Path FROM GetImage
WHERE (ImageFileID = @ImageFileID) and (VariantID = @VariantID) and (ProductID = @ProductID)
IF @@ROWCOUNT = 0
BEGIN
RETURN
END

BEGIN TRANSACTION
-- delete a record into the ImageHierarchy
DELETE FROM ImageHierarchy
WHERE (ImageFileID = @ImageFileID) and (VariantID = @VariantID) and (ProductID = @ProductID)

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

-- delete the record into the image table
DELETE FROM Images
WHERE (ImageFileID = @ImageFileID)

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

IF(@@Error<>0)
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END

END

GO
/****** Object: StoredProcedure [dbo].[img_GetImagecategory] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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) = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;

SELECT Sequence, DisplayName, ImageName, Height, Width, ImageType, Path, ProductID, VariantID, CategoryName
FROM GetImage
WHERE (CategoryName = @CategoryName)

END

GO
/****** Object: StoredProcedure [dbo].[img_GetImageProduct] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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) = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;

SELECT Sequence, DisplayName, ImageName, Height, Width, ImageType, Path, ProductID, VariantID
FROM GetImage
WHERE (ProductID = @ProductID)

END

GO
/****** Object: StoredProcedure [dbo].[img_GetImageProductVariant] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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) = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;

SELECT Sequence, DisplayName, ImageName, Height, Width, ImageType, Path, ProductID, VariantID
FROM GetImage
WHERE (ProductID = @ProductID) and (VariantID = @VariantID)

END

GO
/****** Object: StoredProcedure [dbo].[img_Resequence] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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 int
AS

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

BEGIN TRANSACTION
-- insert a record into the ImageHierarchy
UPDATE ImageHierarchy
SET ImageFileID = @ImageFileID,
Sequence = @Sequence,
ProductID = @ProductID,
VariantID = @VariantID,
CategoryName = @CategoryName,
CatalogName = @CatalogName
WHERE (ImageHierarchyID = @ImageHierarchyID)

IF(@@Error=0)
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END

END

GO
/****** Object: StoredProcedure [dbo].[img_UpdateImagetbl] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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 int
AS

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

BEGIN TRANSACTION
-- insert the record into the image table
UPDATE Images
SET FeatureID = @FeatureID
WHERE (ImageFileID = @ImageFileID)

IF(@@Error=0)
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END

END

GO
/****** Object: StoredProcedure [dbo].[img_UpdateLanguagetbl] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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 from
SET NOCOUNT ON;

BEGIN TRANSACTION
-- insert the record into the image table
IF @language = 'en-US'
BEGIN
-- this should be stored procedure
UPDATE [Images_en-US]
SET DisplayName = @DisplayName,
ImageName = @ImageName,
Height = @Height,
Width = @Width
WHERE (ImageFileID = @ImageFileID)
END

IF(@@Error=0)
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END

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')
GO
INSERT 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:

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

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

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

http://msdn.microsoft.com/practices/

I have also posted some test scripts see attachment.

Published Tuesday, October 31, 2006 7:07 PM by Max Akbar

Filed under: Catalog System, Architectural and Design

Attachment(s): Test Scripts.zip

Comments

New Comments to this post are disabled

Max Akbar

This Blog

Syndication

Search

Tags

News

Archives

Commerce Server Bloggers

Cool Commerce Resources

Tips and Tricks

WPT Bloggers

Adding Image Management to your Commerce Site Part(III)

Product Catalog Database

Tables

T-SQL Code for Tables

View

Stored Procedures

Database Performance Considerations

Staging Consideration

Design Problems

Test your Database Design

Summary

Comments