Ecommerce Database Design

After getting my feet wet yesterday with V6 I wanted to step back a bit and look at the design for the database. We will be using SQL Server 2005, you should find the project works with SQLExpress as required.

Schema

The database design is very import as far as ISD is concerned. It relies on the table relationships in order to determine how best to provide the quickest and most efficient experience. What I mean by that is that when creating pages with bound data you tend to find on the whole that you want to see data that is related to some other data on the page. For example, when creating a SalesOrder page you will probably want to see related data, the obvious one being SalesOrderDetails. Not so obvious would be the customer this order is for and the status of the order. By linking these in the db, ISD will know how best to create the page layout for you using the wizard, it will use dropdown lists of information that saves you a lot of work. If you see what you consider a FK item that is rendered as a text box rather than a dropdown its a fair bet you do not have a relationship set up in the db. Go back and create the relationship before trying to move on with ISD is my advice.

You can of course create a virtual FK relationship, a relationship that exists only in ISD, no changes are made to your db schema.

SQL Server Good Design Practices

 Over the years I have seen many different designs, some I though were awful some I thought Wow "I must use that in my design". This list is my based on my personal preferences and may well differ from yours - it probably does.

• Use well defined names for your tables and fields ie SalesOrder, not order
• Use the singular ie SalesOrder not SalesOrders
• Use Leading capitals for name ie SalesOrder not salesorder
• Use words that describe what it is ie SalesOrder
• Avoid spaces and other punctuation ie SalesOrder not sales order or tblSales order, avoids having to put [] around the name all the time
• Use a single field PK, even though you may have other fields that could compound to give you the PK I find it better not to
• Use the name of the table appended with ID as the name of the PK field where possible ie SalesOrderID
• Make the first field the PK field
• Follow the PK with the FK fields
• Use bit fields to store bool yes/no values and start the name with Is ie IsEnabled
• For tables that store type information ie a lookuplist append the word Type ie CurrencyType or CountryType
• Avoid using table/field names that you know clash with reserved words ie avoid a table called transaction, instead call in SalesTransaction
• Add audit fields so you know who created and when and who edited and when. I add these as the last 4 fields.
• Add a sort order field so you can order the items, great for when alphanumeric is not good enough
• Use the Database diagrams to visualise your schema and makes creating relationships a breeze.
• Use Indexes to speed up queries, use the Profile Analyser to make suggestions. Not so good for ISD as the sps's use temp tables which the analyser doesn't seem to like.
• Use unique constraints/indexes. Avoids having duplicate data in the db where it shouldn't be.
• Use GUID's (uniqueidentifiers) for key fields. Many reasons for it which can be debated at length, I find they work, are globally unique, more secure than ints, avoids errors when all other PK start at 1 and increment by 1, hard to see the wood for the trees when this happends, allows you to set it in code or use sql server default (newid())
• Add a DisplayText field that is a computed column that you can define as required, I point them to other fields in the table and then in ISD always use this field as the Display Field As for dropdowns etc. If I need to change it later I just change the formula in the computed field in SQL Server
• It is not always possible to delete records, instead use a IsDeleted bit field to mark the field as deleted. Ensure all data is filtered by this.
• I also add a couple of extra fields where needed, IsHidden and IsEnabled that allow the row to be filtered by the admin and the user, ie if as an admin I don't want a user to login any more I set the IsEnabled to false, if later they pay the subs I simply flip the flag. The IsHidden can be used to filter items that appear in dropdown lists for example without deleteing it.
• Add a autoincrement field but not for use as the PK, can be useful when creating invoices for example and you need a number to increment.
• Use nvarchar fields, ensures all languages are catered for
• Use varchar(max) when needed, avoid ntext or image unless really needed.
• Try to offload any image fields (for documents or images you want to  store in the db) in a seperate database or table and just have a pointer to them. That way you can reduce server load as you don't always want the image data coming back when selecting data, only read the image data as and when needed, avoids select * bringing it back. You can also backup the image db separately. (case in point I have an exisitng client that has over 6GB of data in a database, 5.6 GB of that is PDF and word documents. If that was in a separate db downloading the main db would take only 10 minutes not 10 hours)
• Use triggers only when needed and insure you really understand them. I have had many head scratching experiences with them, code them carefully and test them well.
• Use views, I use them a lot with ISD when I just want to show read only data with multiple joins, ensure you can determine a virtual PK. I also use them to create a dummy table just so I can get ISD to create the page for me, ie a Report template.
• Use functions for repeated code, ie I have a suite of functions that return 1st day of month, 1st day of quarter, 1st day of year etc
• Keep user permissions to a minimum, its easy to give the user dbo permissions, try not to!
• When you have some functionality that requires speed put in in the db. Move it out of the app to a sp, you gain speed but loose the strongly typed environment
• Write your stored procs so it can be tested and benchmarked, use SQL profile analyser and multiple users, it's funny how it always works with just 1 user!
• Use Red Gate SQL toolbelt for syncing your changes, its money well spent.

Sample Address Schema

USE [LSITSECommerce]
GO
/****** Object:  Table [dbo].[Address]    Script Date: 03/06/2009 11:21:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Address](
    [AddressID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Table_1_ProductTypeID_1]  DEFAULT (newid()),
    [DisplayText]  AS ((((((isnull([Address1]+', ','')+isnull([Address2]+', ',''))+isnull([Address3]+', ',''))+isnull([Town]+', ',''))+isnull([Region]+', ',''))+isnull([Postcode]+', ',''))+[dbo].[fnGetCountryName]([CountryTypeID])),
    [Address1] [nvarchar](80) NOT NULL,
    [Address2] [nvarchar](80) NULL,
    [Address3] [nvarchar](80) NULL,
    [Town] [nvarchar](80) NOT NULL,
    [Region] [nvarchar](80) NULL,
    [Postcode] [nvarchar](20) NOT NULL,
    [CountryTypeID] [uniqueidentifier] NOT NULL,
    [Telephone] [nvarchar](20) NULL,
    [Fax] [nvarchar](20) NULL,
    [GeoMapFix] [nvarchar](100) NULL,
    [SortOrder] [smallint] NULL,
    [IsDeleted] [bit] NOT NULL CONSTRAINT [DF_Address_IsDeleted]  DEFAULT ((0)),
    [IsHidden] [bit] NOT NULL CONSTRAINT [DF_Address_IsHidden]  DEFAULT ((0)),
    [DateCreated] [datetime] NOT NULL CONSTRAINT [DF_Address_DateCreated]  DEFAULT (getdate()),
    [CreatedBy] [nvarchar](30) NOT NULL CONSTRAINT [DF_Address_CreatedBy]  DEFAULT (suser_sname()),
    [DateModified] [datetime] NULL CONSTRAINT [DF_Address_DateModified]  DEFAULT (getdate()),
    [ModifiedBy] [nvarchar](30) NULL CONSTRAINT [DF_Address_ModifiedBy]  DEFAULT (suser_sname()),
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
    [AddressID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]