Sql Server Shopping Cart - Identity Column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sql Server Shopping Cart - Identity Column

Author

Topic

love
Starting Member

2 Posts

Posted - 2005-10-26 : 12:46:39

Shopping cart is for Confectionary Items:

1) Product belongs to 1 or more Category
2) Products can have zero, one or more toppings
3) Products can only be delivered in certain zip codes
4) All products can be picked up from Store Location (only one store location)
5) Only Certain product can be shipped in continental US

Now in our database design All Primary Keys are Identity Columns, i.e.
CategoryID
ProductID
ToppingID
OrderID
ItemID
PaymentID
RecordID etc etc

Do we really need to have Identity Column as Primary key in all these tables? By eliminating Identity Column as Primary Key in certain table do we get any benefit?

I am attaching E-R Diagram for this.

CREATE TABLE [dbo].[AddressBook] (
[AddressBookID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddressType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Customers] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DeliveryZip] (
[ZipCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Location] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DeliveryRate] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Log] (
[LogID] [int] IDENTITY (1, 1) NOT NULL ,
[EventID] [int] NULL ,
[Category] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Priority] [int] NOT NULL ,
[Severity] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Timestamp] [datetime] NOT NULL ,
[MachineName] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AppDomainName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProcessID] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProcessName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ThreadName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32ThreadId] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Message] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FormattedMessage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrderDetails] (
[ItemID] [int] NOT NULL ,
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[UnitCost] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Order_Toppings] (
[ItemID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentID] [int] NOT NULL ,
[ShipDate] [datetime] NOT NULL ,
[ShipMethod] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ShipRate] [money] NOT NULL ,
[TaxAmount] [money] NOT NULL ,
[OrderTotal] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Payments] (
[PaymentID] [int] IDENTITY (1, 1) NOT NULL ,
[CardType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreditCardNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExpMonth] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExpYear] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddressBookID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Prod_Toppings] (
[ProductID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Products] (
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NULL ,
[ModelNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModelName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProductImage] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitCost] [money] NOT NULL ,
[Description] [varchar] (3800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CanDeliver] [bit] NOT NULL ,
[CanPickUP] [bit] NOT NULL ,
[CanShip] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ShoppingCart] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[CartID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ShoppingCart_Toppings] (
[RecordID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Toppings] (
[ToppingID] [int] NOT NULL ,
[ToppingName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 00:58:42

Hi love, Welcome to SQL Team! - I *so* enjoyed typing that, as a Brit!

"Do we really need to have Identity Column as Primary key in all these tables? By eliminating Identity Column as Primary Key in certain table do we get any benefit?"

Well ... a PK has got to be unique, have you got a "natural" key that's unique instead? If so why are you using Identity at all? (The debate over Surrogate v. Natural keys will now begin, or continue elsewhere!)

Performance has probably got more to do with what you put the Clustered Index on - by default a PK will use a Clustered Index. You probably want to make sure that is used for columns that you JOIN on, or that you select a RANGE from.

I'll make some other observations if that's OK (if its homework I'll take any marks you gain, thanks!)

CREATE TABLE [dbo].[Orders]
CREATE TABLE [dbo].[OrderDetails]
CREATE TABLE [dbo].[Order_Toppings]

Sloppy naming conventions ...

CREATE TABLE [dbo].[OrderDetails] (
[ItemID] [int] NOT NULL ,
[OrderID] [int] NOT NULL ,

You didn't post the PKs on each table - is ItemID a 1,2,3 number PER order? If not it needs to be an IDENTITY right? If it's not an IDENTITY you might want to call is something that does not end in "ID"

And if its not an identity then this guy:

CREATE TABLE [dbo].[Order_Toppings] (
[ItemID] [int] NOT NULL ,

is going to need the OrderID too

CREATE TABLE [dbo].[Payments] (
...
[CardType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreditCardNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExpMonth] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExpYear] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

Too many varchar(50) defaults ... ExpMonth and ExpYear should be numeric, CardType ought to be in a lookup table. God Help Us if credit card numbers get to be 50 digits long! There are plenty of other varchar(50) that you should take a hard look at.

What stuck the LOG table in there? Is that really needed?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources