PetShop 4.0 数据库明细
来源：互联网 发布：四级长篇阅读知乎 编辑：程序博客网 时间：2024/04/30 20:37
MSPetShop4数据表清单
MSPetShop4Orders 数据表清单
MSPetShop4Profile数据表清单
MSPetShop4Services数据表清单
MSPetShop4
=========================================
=========================================
1.AspNet_SqlCacheTablesForChangeNotification
缓存数据
------------------------------------------------------------------
[tableName] [nvarchar](450) NOT NULL primary key,#表名称
[notificationCreated] [datetime] NOT NULL DEFAULT(getdate()),#创建日期 [changeId] [int] NOT NULLDEFAULT ((0)),#修改id
缓存数据
------------------------------------------------------------------
[tableName] [nvarchar](450) NOT NULL primary key,#表名称
[notificationCreated] [datetime] NOT NULL DEFAULT(getdate()),#创建日期 [changeId] [int] NOT NULLDEFAULT ((0)),#修改id
2.Category目录表
------------------------------------------------------------------
[CategoryId] [varchar](10) NOT NULL primary key, #分类id
[Name] [varchar](80) NULL,#名称
[Descn] [varchar](255) NULL,#说明
------------------------------------------------------------------
[CategoryId] [varchar](10) NOT NULL primary key, #分类id
[Name] [varchar](80) NULL,#名称
[Descn] [varchar](255) NULL,#说明
设置触发器
CREATE TRIGGER [Category_AspNet_SqlCacheNotification_Trigger] ON[dbo].[Category]
FOR INSERT, UPDATE, DELETE AS BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedureN'Category'
END
CREATE TRIGGER [Category_AspNet_SqlCacheNotification_Trigger] ON[dbo].[Category]
FOR INSERT, UPDATE, DELETE AS BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedureN'Category'
END
3.Inventory产品库存
---------------------------------------------------------------
[ItemId] [varchar](10) NOT NULL primary key, #明细id
[Qty] [int] NOT NULL,#数量
---------------------------------------------------------------
[ItemId] [varchar](10) NOT NULL primary key, #明细id
[Qty] [int] NOT NULL,#数量
4.Item产品明细
-------------------------------------------------------------
[ItemId] [varchar](10) NOT NULL primary key,#明细id
[ProductId] [varchar](10) NOT NULL,#产品id
[ListPrice] [decimal](10, 2)NULL, #价格
[UnitCost] [decimal](10, 2)NULL, #单价
[Supplier] [int]NULL, #供应商
[Status] [varchar](2)NULL, #状态
[Name] [varchar](80)NULL, #名称
[Image] [varchar](80)NULL, #图像
创建索引
CREATE NONCLUSTERED INDEX [IxItem] ON[dbo].[Item]
(
[ProductId] ASC,
[ItemId] ASC,
[ListPrice] ASC,
[Name] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
-------------------------------------------------------------
[ItemId] [varchar](10) NOT NULL primary key,#明细id
[ProductId] [varchar](10) NOT NULL,#产品id
[ListPrice] [decimal](10, 2)NULL, #价格
[UnitCost] [decimal](10, 2)NULL, #单价
[Supplier] [int]NULL, #供应商
[Status] [varchar](2)NULL, #状态
[Name] [varchar](80)NULL, #名称
[Image] [varchar](80)NULL, #图像
创建索引
CREATE NONCLUSTERED INDEX [IxItem] ON[dbo].[Item]
(
[ProductId] ASC,
[ItemId] ASC,
[ListPrice] ASC,
[Name] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
创建触发器
CREATE TRIGGER [Item_AspNet_SqlCacheNotification_Trigger] ON[dbo].[Item]
FOR INSERT, UPDATE, DELETE AS BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'Item'
END
CREATE TRIGGER [Item_AspNet_SqlCacheNotification_Trigger] ON[dbo].[Item]
FOR INSERT, UPDATE, DELETE AS BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'Item'
END
5.Product产品信息
---------------------------------------------------
[ProductId] [varchar](10) NOT NULL,#产品id
[CategoryId] [varchar](10) NOT NULL,#分类id
[Name] [varchar](80)NULL, #名称
[Descn] [varchar](255)NULL, #说明
[Image] [varchar](80)NULL, #图像
---------------------------------------------------
[ProductId] [varchar](10) NOT NULL,#产品id
[CategoryId] [varchar](10) NOT NULL,#分类id
[Name] [varchar](80)NULL, #名称
[Descn] [varchar](255)NULL, #说明
[Image] [varchar](80)NULL, #图像
创建索引
CREATE NONCLUSTERED INDEX [IxProduct1] ON [dbo].[Product]
(
[Name] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IxProduct1] ON [dbo].[Product]
(
[Name] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IxProduct2] ON [dbo].[Product]
(
[CategoryId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IxProduct3] ON [dbo].[Product]
(
[CategoryId] ASC,
[Name] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IxProduct4] ON [dbo].[Product]
(
[CategoryId] ASC,
[ProductId] ASC,
[Name] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
创建触发器
CREATE TRIGGER [Product_AspNet_SqlCacheNotification_Trigger] ON[dbo].[Product]
FOR INSERT, UPDATE, DELETE AS BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedureN'Product'
END
CREATE TRIGGER [Product_AspNet_SqlCacheNotification_Trigger] ON[dbo].[Product]
FOR INSERT, UPDATE, DELETE AS BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedureN'Product'
END
6.Supplier供应商信息
--------------------------------------------------------
[SuppId] [int] NOT NULL primary key,#供应商id
[Name] [varchar](80)NULL, #名称
[Status] [varchar](2) NOTNULL, #状态
[Addr1] [varchar](80)NULL, #地址1
[Addr2] [varchar](80)NULL, #地址2
[City] [varchar](80)NULL, #城市
[State] [varchar](80)NULL, #洲省
[Zip] [varchar](5)NULL, #邮编
[Phone] [varchar](40)NULL, #电话
创建过程
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE[dbo].[AspNet_SqlCachePollingStoredProcedure] AS
SELECT tableName, changeId FROMdbo.AspNet_SqlCacheTablesForChangeNotification
RETURN 0'
END
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE[dbo].[AspNet_SqlCachePollingStoredProcedure] AS
SELECT tableName, changeId FROMdbo.AspNet_SqlCacheTablesForChangeNotification
RETURN 0'
END
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE[dbo].[AspNet_SqlCacheRegisterTableStoredProcedure]
@tableName NVARCHAR(450)
AS
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE[dbo].[AspNet_SqlCacheRegisterTableStoredProcedure]
@tableName NVARCHAR(450)
AS
BEGIN
DECLARE @triggerName AS NVARCHAR(3000)
DECLARE @fullTriggerName AS NVARCHAR(3000)
DECLARE @canonTableName NVARCHAR(3000)
DECLARE @quotedTableName NVARCHAR(3000)
DECLARE @fullTriggerName AS NVARCHAR(3000)
DECLARE @canonTableName NVARCHAR(3000)
DECLARE @quotedTableName NVARCHAR(3000)
/* Create the trigger name */
SET @triggerName = REPLACE(@tableName, ''['', ''__o__'')
SET @triggerName = REPLACE(@triggerName, '']'', ''__c__'')
SET @triggerName = @triggerName +''_AspNet_SqlCacheNotification_Trigger''
SET @fullTriggerName = ''dbo.['' + @triggerName + '']''
SET @triggerName = REPLACE(@tableName, ''['', ''__o__'')
SET @triggerName = REPLACE(@triggerName, '']'', ''__c__'')
SET @triggerName = @triggerName +''_AspNet_SqlCacheNotification_Trigger''
SET @fullTriggerName = ''dbo.['' + @triggerName + '']''
/* Create the cannonicalized table name for trigger creation*/
/* Do not touch it if the name contains other delimiters */
IF (CHARINDEX(''.'', @tableName) <> 0 OR
CHARINDEX(''['', @tableName) <> 0 OR
CHARINDEX('']'', @tableName) <> 0)
SET @canonTableName = @tableName
ELSE
SET @canonTableName = ''['' + @tableName + '']''
/* Do not touch it if the name contains other delimiters */
IF (CHARINDEX(''.'', @tableName) <> 0 OR
CHARINDEX(''['', @tableName) <> 0 OR
CHARINDEX('']'', @tableName) <> 0)
SET @canonTableName = @tableName
ELSE
SET @canonTableName = ''['' + @tableName + '']''
/* First make sure the table exists */
IF (SELECT OBJECT_ID(@tableName, ''U'')) IS NULL
BEGIN
RAISERROR (''00000001'', 16, 1)
RETURN
END
IF (SELECT OBJECT_ID(@tableName, ''U'')) IS NULL
BEGIN
RAISERROR (''00000001'', 16, 1)
RETURN
END
BEGIN TRAN
/* Insert the value into the notification table */
IF NOT EXISTS (SELECT tableName FROMdbo.AspNet_SqlCacheTablesForChangeNotification WITH (NOLOCK) WHEREtableName = @tableName)
IF NOT EXISTS (SELECT tableName FROMdbo.AspNet_SqlCacheTablesForChangeNotification WITH (TABLOCKX)WHERE tableName = @tableName)
INSERT dbo.AspNet_SqlCacheTablesForChangeNotification
VALUES (@tableName, GETDATE(), 0)
/* Insert the value into the notification table */
IF NOT EXISTS (SELECT tableName FROMdbo.AspNet_SqlCacheTablesForChangeNotification WITH (NOLOCK) WHEREtableName = @tableName)
IF NOT EXISTS (SELECT tableName FROMdbo.AspNet_SqlCacheTablesForChangeNotification WITH (TABLOCKX)WHERE tableName = @tableName)
INSERT dbo.AspNet_SqlCacheTablesForChangeNotification
VALUES (@tableName, GETDATE(), 0)
/* Create the trigger */
SET @quotedTableName = QUOTENAME(@tableName, '''''''')
IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name= @triggerName AND type = ''TR'')
IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHEREname = @triggerName AND type = ''TR'')
EXEC(''CREATE TRIGGER '' + @fullTriggerName + '' ON '' +@canonTableName +''
FOR INSERT, UPDATE, DELETE AS BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'' +@quotedTableName + ''
END
'')
COMMIT TRAN
END
'
END
SET @quotedTableName = QUOTENAME(@tableName, '''''''')
IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name= @triggerName AND type = ''TR'')
IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHEREname = @triggerName AND type = ''TR'')
EXEC(''CREATE TRIGGER '' + @fullTriggerName + '' ON '' +@canonTableName +''
FOR INSERT, UPDATE, DELETE AS BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'' +@quotedTableName + ''
END
'')
COMMIT TRAN
END
'
END
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE[dbo].[AspNet_SqlCacheUpdateChangeIdStoredProcedure]
@tableName NVARCHAR(450)
AS
BEGIN
UPDATE dbo.AspNet_SqlCacheTablesForChangeNotification WITH(ROWLOCK) SET changeId = changeId + 1
WHERE tableName = @tableName
END
'
END
UPDATE dbo.AspNet_SqlCacheTablesForChangeNotification WITH(ROWLOCK) SET changeId = changeId + 1
WHERE tableName = @tableName
END
'
END
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE[dbo].[AspNet_SqlCacheUnRegisterTableStoredProcedure]
@tableName NVARCHAR(450)
AS
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE[dbo].[AspNet_SqlCacheUnRegisterTableStoredProcedure]
@tableName NVARCHAR(450)
AS
BEGIN
BEGIN TRAN
DECLARE @triggerName AS NVARCHAR(3000)
DECLARE @fullTriggerName AS NVARCHAR(3000)
SET @triggerName = REPLACE(@tableName, ''['', ''__o__'')
SET @triggerName = REPLACE(@triggerName, '']'', ''__c__'')
SET @triggerName = @triggerName +''_AspNet_SqlCacheNotification_Trigger''
SET @fullTriggerName = ''dbo.['' + @triggerName + '']''
DECLARE @triggerName AS NVARCHAR(3000)
DECLARE @fullTriggerName AS NVARCHAR(3000)
SET @triggerName = REPLACE(@tableName, ''['', ''__o__'')
SET @triggerName = REPLACE(@triggerName, '']'', ''__c__'')
SET @triggerName = @triggerName +''_AspNet_SqlCacheNotification_Trigger''
SET @fullTriggerName = ''dbo.['' + @triggerName + '']''
/* Remove the table-row from the notification table */
IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name =''AspNet_SqlCacheTablesForChangeNotification'' AND type =''U'')
IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name =''AspNet_SqlCacheTablesForChangeNotification'' AND type =''U'')
DELETE FROM dbo.AspNet_SqlCacheTablesForChangeNotification WHEREtableName = @tableName
IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name =''AspNet_SqlCacheTablesForChangeNotification'' AND type =''U'')
IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name =''AspNet_SqlCacheTablesForChangeNotification'' AND type =''U'')
DELETE FROM dbo.AspNet_SqlCacheTablesForChangeNotification WHEREtableName = @tableName
/* Remove the trigger */
IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name =@triggerName AND type = ''TR'')
IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name =@triggerName AND type = ''TR'')
EXEC(''DROP TRIGGER '' + @fullTriggerName)
IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name =@triggerName AND type = ''TR'')
IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name =@triggerName AND type = ''TR'')
EXEC(''DROP TRIGGER '' + @fullTriggerName)
COMMIT TRAN
END
'
END
END
'
END
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE[dbo].[AspNet_SqlCacheQueryRegisteredTablesStoredProcedure]
AS
SELECT tableName FROMdbo.AspNet_SqlCacheTablesForChangeNotification '
END
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE[dbo].[AspNet_SqlCacheQueryRegisteredTablesStoredProcedure]
AS
SELECT tableName FROMdbo.AspNet_SqlCacheTablesForChangeNotification '
END
创建外键
ALTER TABLE [dbo].[Item] WITH CHECK ADD FOREIGNKEY([ProductId])
REFERENCES [dbo].[Product] ([ProductId])
ALTER TABLE [dbo].[Item] WITH CHECK ADD FOREIGNKEY([ProductId])
REFERENCES [dbo].[Product] ([ProductId])
ALTER TABLE [dbo].[Item] WITH CHECK ADDFOREIGN KEY([Supplier])
REFERENCES [dbo].[Supplier] ([SuppId])
REFERENCES [dbo].[Supplier] ([SuppId])
ALTER TABLE [dbo].[Product]
WITH CHECK ADDFOREIGN KEY([CategoryId])
REFERENCES [dbo].[Category] ([CategoryId])
REFERENCES [dbo].[Category] ([CategoryId])
MSPetShop4Orders
===========================
1.LineItem订单明细
-------------------------------------------------
[OrderId] [int] NOT NULL,#订单号
[LineNum] [int] NOT NULL,
===========================
1.LineItem订单明细
-------------------------------------------------
[OrderId] [int] NOT NULL,#订单号
[LineNum] [int] NOT NULL,
#明细号
[ItemId] [varchar](10) NOT NULL, #产品明细号
[Quantity] [int] NOTNULL, #数量
[UnitPrice] [decimal](10, 2) NOT NULL, #单价
CONSTRAINT [PkLineItem] PRIMARY KEYCLUSTERED
(
[OrderId] ASC,
[LineNum] ASC
)
[ItemId] [varchar](10) NOT NULL, #产品明细号
[Quantity] [int] NOTNULL, #数量
[UnitPrice] [decimal](10, 2) NOT NULL, #单价
CONSTRAINT [PkLineItem] PRIMARY KEYCLUSTERED
(
[OrderId] ASC,
[LineNum] ASC
)
2.Orders订单
-------------------------------------------------
[OrderId] [int] IDENTITY(1,1) NOT NULL primary key,#订单号
[UserId] [varchar](20) NOTNULL, #用户号
[OrderDate] [datetime] NOTNULL, #订单日期
[ShipAddr1] [varchar](80) NOTNULL, #发货地址1
[ShipAddr2] [varchar](80)NULL, #发货地址2
[ShipCity] [varchar](80) NOTNULL, #发货城市
[ShipState] [varchar](80) NOTNULL, #发货洲省
[ShipZip] [varchar](20) NOTNULL, #发货邮编
[ShipCountry] [varchar](20) NOTNULL, #发货国家地区
[BillAddr1] [varchar](80) NOTNULL, #帐单地址1
[BillAddr2] [varchar](80)NULL, #帐单地址2
[BillCity] [varchar](80) NOTNULL, #帐单城市
[BillState] [varchar](80) NOTNULL, #帐单洲省
[BillZip] [varchar](20) NOTNULL, #帐单邮编
[BillCountry] [varchar](20) NOTNULL, #帐单国家地区
[Courier] [varchar](80) NOTNULL, #货运
[TotalPrice] [decimal](10, 2) NOTNULL, #总金额
[BillToFirstName] [varchar](80) NOTNULL, #帐单姓
[BillToLastName] [varchar](80) NOTNULL, #帐单名
[ShipToFirstName] [varchar](80) NOTNULL, #发货姓
[ShipToLastName] [varchar](80) NOTNULL, #发货名
[AuthorizationNumber] [int] NOTNULL, #验证码
[Locale] [varchar](20) NOTNULL, #语言地区
-------------------------------------------------
[OrderId] [int] IDENTITY(1,1) NOT NULL primary key,#订单号
[UserId] [varchar](20) NOTNULL, #用户号
[OrderDate] [datetime] NOTNULL, #订单日期
[ShipAddr1] [varchar](80) NOTNULL, #发货地址1
[ShipAddr2] [varchar](80)NULL, #发货地址2
[ShipCity] [varchar](80) NOTNULL, #发货城市
[ShipState] [varchar](80) NOTNULL, #发货洲省
[ShipZip] [varchar](20) NOTNULL, #发货邮编
[ShipCountry] [varchar](20) NOTNULL, #发货国家地区
[BillAddr1] [varchar](80) NOTNULL, #帐单地址1
[BillAddr2] [varchar](80)NULL, #帐单地址2
[BillCity] [varchar](80) NOTNULL, #帐单城市
[BillState] [varchar](80) NOTNULL, #帐单洲省
[BillZip] [varchar](20) NOTNULL, #帐单邮编
[BillCountry] [varchar](20) NOTNULL, #帐单国家地区
[Courier] [varchar](80) NOTNULL, #货运
[TotalPrice] [decimal](10, 2) NOTNULL, #总金额
[BillToFirstName] [varchar](80) NOTNULL, #帐单姓
[BillToLastName] [varchar](80) NOTNULL, #帐单名
[ShipToFirstName] [varchar](80) NOTNULL, #发货姓
[ShipToLastName] [varchar](80) NOTNULL, #发货名
[AuthorizationNumber] [int] NOTNULL, #验证码
[Locale] [varchar](20) NOTNULL, #语言地区
3.OrderStatus订单状态
---------------------------------------
[OrderId] [int] NOT NULL, #订单号
[LineNum] [int] NOT NULL, #订单明细号
[Timestamp] [datetime] NOT NULL,#日期
[Status] [varchar](2) NOT NULL,#状态
CONSTRAINT [PkOrderStatus] PRIMARY KEYCLUSTERED
(
[OrderId] ASC,
[LineNum] ASC
)
---------------------------------------
[OrderId] [int] NOT NULL, #订单号
[LineNum] [int] NOT NULL, #订单明细号
[Timestamp] [datetime] NOT NULL,#日期
[Status] [varchar](2) NOT NULL,#状态
CONSTRAINT [PkOrderStatus] PRIMARY KEYCLUSTERED
(
[OrderId] ASC,
[LineNum] ASC
)
创建外键
ALTER TABLE [dbo].[LineItem] WITH CHECK ADDFOREIGN KEY([OrderId])
REFERENCES [dbo].[Orders] ([OrderId])
REFERENCES [dbo].[Orders] ([OrderId])
ALTER TABLE [dbo].[OrderStatus] WITH CHECKADD FOREIGN
KEY([OrderId])
REFERENCES [dbo].[Orders] ([OrderId])
REFERENCES [dbo].[Orders] ([OrderId])
MSPetShop4Profile
============================
1.Account帐号
---------------------------------------------------
[UniqueID] [int] NOT NULL unique,
[Email] [varchar](80) NOT NULL,
[FirstName] [varchar](80) NOT NULL,
[LastName] [varchar](80) NOT NULL,
[Address1] [varchar](80) NOT NULL,
[Address2] [varchar](80) NULL,
[City] [varchar](80) NOT NULL,
[State] [varchar](80) NOT NULL,
[Zip] [varchar](20) NOT NULL,
[Country] [varchar](20) NOT NULL,
[Phone] [varchar](20) NULL
============================
1.Account帐号
---------------------------------------------------
[UniqueID] [int] NOT NULL unique,
[Email] [varchar](80) NOT NULL,
[FirstName] [varchar](80) NOT NULL,
[LastName] [varchar](80) NOT NULL,
[Address1] [varchar](80) NOT NULL,
[Address2] [varchar](80) NULL,
[City] [varchar](80) NOT NULL,
[State] [varchar](80) NOT NULL,
[Zip] [varchar](20) NOT NULL,
[Country] [varchar](20) NOT NULL,
[Phone] [varchar](20) NULL
2.Cart购物车
-------------------------------------------------
[UniqueID] [int] NOT NULL unique,#唯一标示符
[ItemId] [varchar](10) NOT NULL,#产品明细号
[Name] [varchar](80) NOT NULL, #名称
[Type] [varchar](80) NOT NULL, #类型
[Price] [decimal](10, 2) NOT NULL, #价格
[CategoryId] [varchar](10) NOT NULL, #分类号
[ProductId] [varchar](10) NOT NULL, #产品号
[IsShoppingCart] [bit] NOTNULL, #是购物车吗
[Quantity] [int] NOTNULL #数量
-------------------------------------------------
[UniqueID] [int] NOT NULL unique,#唯一标示符
[ItemId] [varchar](10) NOT NULL,#产品明细号
[Name] [varchar](80) NOT NULL, #名称
[Type] [varchar](80) NOT NULL, #类型
[Price] [decimal](10, 2) NOT NULL, #价格
[CategoryId] [varchar](10) NOT NULL, #分类号
[ProductId] [varchar](10) NOT NULL, #产品号
[IsShoppingCart] [bit] NOTNULL, #是购物车吗
[Quantity] [int] NOTNULL #数量
索引
CREATE NONCLUSTERED INDEX [IX_SHOPPINGCART] ON [dbo].[Cart]
(
[IsShoppingCart] ASC
)
CREATE NONCLUSTERED INDEX [IX_SHOPPINGCART] ON [dbo].[Cart]
(
[IsShoppingCart] ASC
)
3.Profiles配置
-----------------------------------------------
[UniqueID] [int] IDENTITY(1,1) NOT NULL primarykey,#唯一标示符
[Username] [varchar](256) NOT NULLunique, #用户名
[ApplicationName] [varchar](256) NOT NULLunique, #应用名
[IsAnonymous] [bit]NULL, #是否匿名用户
[LastActivityDate] [datetime]NULL, #最后活动日期
[LastUpdatedDate] [datetime]NULL, #最后更新日期
-----------------------------------------------
[UniqueID] [int] IDENTITY(1,1) NOT NULL primarykey,#唯一标示符
[Username] [varchar](256) NOT NULLunique, #用户名
[ApplicationName] [varchar](256) NOT NULLunique, #应用名
[IsAnonymous] [bit]NULL, #是否匿名用户
[LastActivityDate] [datetime]NULL, #最后活动日期
[LastUpdatedDate] [datetime]NULL, #最后更新日期
外键
ALTER TABLE [dbo].[Cart] WITH CHECKADD CONSTRAINT
ALTER TABLE [dbo].[Cart] WITH CHECKADD CONSTRAINT
[FK_Cart_Profiles] FOREIGN KEY([UniqueID])
REFERENCES [dbo].[Profiles] ([UniqueID])
REFERENCES [dbo].[Profiles] ([UniqueID])
ALTER TABLE [dbo].[Account] WITH CHECKADD CONSTRAINT
[FK_Account_Profiles] FOREIGN KEY([UniqueID])
REFERENCES [dbo].[Profiles] ([UniqueID])
MSPetShop4Services
============================================
1.aspnet_Applications应用
---------------------------------------------------------------
[ApplicationName] [nvarchar](256) NOT NULL unique,
[LoweredApplicationName] [nvarchar](256) NOT NULL unique,
[ApplicationId] [uniqueidentifier] NOT NULL DEFAULT(newid())
primary key,
[Description] [nvarchar](256) NULL,
CREATE CLUSTERED INDEX [aspnet_Applications_Index] ON [dbo].
[Description] [nvarchar](256) NULL,
CREATE CLUSTERED INDEX [aspnet_Applications_Index] ON [dbo].
[aspnet_Applications]
(
[LoweredApplicationName] ASC
)
(
[LoweredApplicationName] ASC
)
2.aspnet_Membership成员
----------------------------------------------
[ApplicationId] [uniqueidentifier] NOT NULL primary key,
[UserId] [uniqueidentifier] NOT NULL,
[Password] [nvarchar](128) NOT NULL,
[PasswordFormat] [int] NOT NULL DEFAULT ((0)),
[PasswordSalt] [nvarchar](128) NOT NULL,
[MobilePIN] [nvarchar](16) NULL,
[Email] [nvarchar](256) NULL,
[LoweredEmail] [nvarchar](256) NULL,
[PasswordQuestion] [nvarchar](256) NULL,
[PasswordAnswer] [nvarchar](128) NULL,
[IsApproved] [bit] NOT NULL,
[IsLockedOut] [bit] NOT NULL,
[CreateDate] [datetime] NOT NULL,
[LastLoginDate] [datetime] NOT NULL,
[LastPasswordChangedDate] [datetime] NOT NULL,
[LastLockoutDate] [datetime] NOT NULL,
[FailedPasswordAttemptCount] [int] NOT NULL,
[FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
[FailedPasswordAnswerAttemptCount] [int] NOT NULL,
[FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,
[Comment] [ntext] NULL,
CREATE CLUSTERED INDEX [aspnet_Membership_index] ON [dbo].
----------------------------------------------
[ApplicationId] [uniqueidentifier] NOT NULL primary key,
[UserId] [uniqueidentifier] NOT NULL,
[Password] [nvarchar](128) NOT NULL,
[PasswordFormat] [int] NOT NULL DEFAULT ((0)),
[PasswordSalt] [nvarchar](128) NOT NULL,
[MobilePIN] [nvarchar](16) NULL,
[Email] [nvarchar](256) NULL,
[LoweredEmail] [nvarchar](256) NULL,
[PasswordQuestion] [nvarchar](256) NULL,
[PasswordAnswer] [nvarchar](128) NULL,
[IsApproved] [bit] NOT NULL,
[IsLockedOut] [bit] NOT NULL,
[CreateDate] [datetime] NOT NULL,
[LastLoginDate] [datetime] NOT NULL,
[LastPasswordChangedDate] [datetime] NOT NULL,
[LastLockoutDate] [datetime] NOT NULL,
[FailedPasswordAttemptCount] [int] NOT NULL,
[FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
[FailedPasswordAnswerAttemptCount] [int] NOT NULL,
[FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,
[Comment] [ntext] NULL,
CREATE CLUSTERED INDEX [aspnet_Membership_index] ON [dbo].
[aspnet_Membership]
(
[ApplicationId] ASC,
[LoweredEmail] ASC
)
(
[ApplicationId] ASC,
[LoweredEmail] ASC
)
3.aspnet_Paths路径
----------------------------------------
[ApplicationId] [uniqueidentifier] NOT NULL,
[PathId] [uniqueidentifier] NOT NULL DEFAULT (newid()) primarykey,
[Path] [nvarchar](256) NOT NULL,
[LoweredPath] [nvarchar](256) NOT NULL,
CREATE UNIQUE CLUSTERED INDEX [aspnet_Paths_index] ON [dbo].
----------------------------------------
[ApplicationId] [uniqueidentifier] NOT NULL,
[PathId] [uniqueidentifier] NOT NULL DEFAULT (newid()) primarykey,
[Path] [nvarchar](256) NOT NULL,
[LoweredPath] [nvarchar](256) NOT NULL,
CREATE UNIQUE CLUSTERED INDEX [aspnet_Paths_index] ON [dbo].
[aspnet_Paths]
(
[ApplicationId] ASC,
[LoweredPath] ASC
)
(
[ApplicationId] ASC,
[LoweredPath] ASC
)
4.aspnet_PersonalizationAllUsers个性化所有用户
------------------------------------------------------------
[PathId] [uniqueidentifier] NOT NULL primary key,
[PageSettings] [image] NOT NULL,
[LastUpdatedDate] [datetime] NOT NULL,
------------------------------------------------------------
[PathId] [uniqueidentifier] NOT NULL primary key,
[PageSettings] [image] NOT NULL,
[LastUpdatedDate] [datetime] NOT NULL,
5.aspnet_PersonalizationPerUser个性化每个用户
----------------------------------------------
[Id] [uniqueidentifier] NOT NULL DEFAULT (newid()) primarykey,
[PathId] [uniqueidentifier] NULL,
[UserId] [uniqueidentifier] NULL,
[PageSettings] [image] NOT NULL,
[LastUpdatedDate] [datetime] NOT NULL,
CREATE UNIQUE CLUSTERED INDEX[aspnet_PersonalizationPerUser_index1]
----------------------------------------------
[Id] [uniqueidentifier] NOT NULL DEFAULT (newid()) primarykey,
[PathId] [uniqueidentifier] NULL,
[UserId] [uniqueidentifier] NULL,
[PageSettings] [image] NOT NULL,
[LastUpdatedDate] [datetime] NOT NULL,
CREATE UNIQUE CLUSTERED INDEX[aspnet_PersonalizationPerUser_index1]
ON [dbo].[aspnet_PersonalizationPerUser]
(
[PathId] ASC,
[UserId] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX
(
[PathId] ASC,
[UserId] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX
[aspnet_PersonalizationPerUser_ncindex2] ON [dbo].
[aspnet_PersonalizationPerUser]
(
[UserId] ASC,
[PathId] ASC
)
(
[UserId] ASC,
[PathId] ASC
)
6.aspnet_Profile配置
------------------------------------------------
[UserId] [uniqueidentifier] NOT NULL primary key,
[PropertyNames] [ntext] NOT NULL,
[PropertyValuesString] [ntext] NOT NULL,
[PropertyValuesBinary] [image] NOT NULL,
[LastUpdatedDate] [datetime] NOT NULL,
------------------------------------------------
[UserId] [uniqueidentifier] NOT NULL primary key,
[PropertyNames] [ntext] NOT NULL,
[PropertyValuesString] [ntext] NOT NULL,
[PropertyValuesBinary] [image] NOT NULL,
[LastUpdatedDate] [datetime] NOT NULL,
7.aspnet_Roles角色
-------------------------------------------
[ApplicationId] [uniqueidentifier] NOT NULL,
[RoleId] [uniqueidentifier] NOT NULL DEFAULT (newid()) primarykey,
[RoleName] [nvarchar](256) NOT NULL,
[LoweredRoleName] [nvarchar](256) NOT NULL,
[Description] [nvarchar](256) NULL,
CREATE UNIQUE CLUSTERED INDEX [aspnet_Roles_index1] ON [dbo].
-------------------------------------------
[ApplicationId] [uniqueidentifier] NOT NULL,
[RoleId] [uniqueidentifier] NOT NULL DEFAULT (newid()) primarykey,
[RoleName] [nvarchar](256) NOT NULL,
[LoweredRoleName] [nvarchar](256) NOT NULL,
[Description] [nvarchar](256) NULL,
CREATE UNIQUE CLUSTERED INDEX [aspnet_Roles_index1] ON [dbo].
[aspnet_Roles]
(
[ApplicationId] ASC,
[LoweredRoleName] ASC
)
(
[ApplicationId] ASC,
[LoweredRoleName] ASC
)
8.aspnet_SchemaVersions版本
-----------------------------------------
[Feature] [nvarchar](128) NOT NULL,
[CompatibleSchemaVersion] [nvarchar](128) NOT NULL,
[IsCurrentVersion] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Feature] ASC,
[CompatibleSchemaVersion] ASC
)
-----------------------------------------
[Feature] [nvarchar](128) NOT NULL,
[CompatibleSchemaVersion] [nvarchar](128) NOT NULL,
[IsCurrentVersion] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Feature] ASC,
[CompatibleSchemaVersion] ASC
)
9.aspnet_Users用户
------------------------------------------
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL DEFAULT (newid()) primarykey,
[UserName] [nvarchar](256) NOT NULL,
[LoweredUserName] [nvarchar](256) NOT NULL,
[MobileAlias] [nvarchar](16) NULL DEFAULT (NULL),
[IsAnonymous] [bit] NOT NULL DEFAULT ((0)),
[LastActivityDate] [datetime] NOT NULL,
CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo].
------------------------------------------
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL DEFAULT (newid()) primarykey,
[UserName] [nvarchar](256) NOT NULL,
[LoweredUserName] [nvarchar](256) NOT NULL,
[MobileAlias] [nvarchar](16) NULL DEFAULT (NULL),
[IsAnonymous] [bit] NOT NULL DEFAULT ((0)),
[LastActivityDate] [datetime] NOT NULL,
CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo].
[aspnet_Users]
(
[ApplicationId] ASC,
[LoweredUserName] ASC
)
CREATE NONCLUSTERED INDEX [aspnet_Users_Index2] ON [dbo].
(
[ApplicationId] ASC,
[LoweredUserName] ASC
)
CREATE NONCLUSTERED INDEX [aspnet_Users_Index2] ON [dbo].
[aspnet_Users]
(
[ApplicationId] ASC,
[LastActivityDate] ASC
)
(
[ApplicationId] ASC,
[LastActivityDate] ASC
)
10.aspnet_UsersInRoles用户角色
-------------------------------------------
[UserId] [uniqueidentifier] NOT NULL,
[RoleId] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[RoleId] ASC
)
CREATE NONCLUSTERED INDEX [aspnet_UsersInRoles_index] ON[dbo].
-------------------------------------------
[UserId] [uniqueidentifier] NOT NULL,
[RoleId] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[RoleId] ASC
)
CREATE NONCLUSTERED INDEX [aspnet_UsersInRoles_index] ON[dbo].
[aspnet_UsersInRoles]
(
[RoleId] ASC
)
(
[RoleId] ASC
)
11.aspnet_WebEvent_Events 站点事件
------------------------------------------------------
[EventId] [char](32) NOT NULL primary key,
[EventTimeUtc] [datetime] NOT NULL,
[EventTime] [datetime] NOT NULL,
[EventType] [nvarchar](256) NOT NULL,
[EventSequence] [decimal](19, 0) NOT NULL,
[EventOccurrence] [decimal](19, 0) NOT NULL,
[EventCode] [int] NOT NULL,
[EventDetailCode] [int] NOT NULL,
[Message] [nvarchar](1024) NULL,
[ApplicationPath] [nvarchar](256) NULL,
[ApplicationVirtualPath] [nvarchar](256) NULL,
[MachineName] [nvarchar](256) NOT NULL,
[RequestUrl] [nvarchar](1024) NULL,
[ExceptionType] [nvarchar](256) NULL,
[Details] [ntext] NULL,
------------------------------------------------------
[EventId] [char](32) NOT NULL primary key,
[EventTimeUtc] [datetime] NOT NULL,
[EventTime] [datetime] NOT NULL,
[EventType] [nvarchar](256) NOT NULL,
[EventSequence] [decimal](19, 0) NOT NULL,
[EventOccurrence] [decimal](19, 0) NOT NULL,
[EventCode] [int] NOT NULL,
[EventDetailCode] [int] NOT NULL,
[Message] [nvarchar](1024) NULL,
[ApplicationPath] [nvarchar](256) NULL,
[ApplicationVirtualPath] [nvarchar](256) NULL,
[MachineName] [nvarchar](256) NOT NULL,
[RequestUrl] [nvarchar](1024) NULL,
[ExceptionType] [nvarchar](256) NULL,
[Details] [ntext] NULL,
外键
ALTER TABLE [dbo].[aspnet_Membership] WITH CHECKADD FOREIGN
ALTER TABLE [dbo].[aspnet_Membership] WITH CHECKADD FOREIGN
KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
ALTER TABLE [dbo].[aspnet_Membership] WITHCHECK ADD FOREIGN
KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
ALTER TABLE [dbo].[aspnet_Profile] WITHCHECK ADD FOREIGN
KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
ALTER TABLE [dbo].[aspnet_UsersInRoles] WITHCHECK ADD FOREIGN
KEY([RoleId])
REFERENCES [dbo].[aspnet_Roles] ([RoleId])
REFERENCES [dbo].[aspnet_Roles] ([RoleId])
ALTER TABLE [dbo].[aspnet_UsersInRoles] WITHCHECK ADD FOREIGN
KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
ALTER TABLE[dbo].[aspnet_PersonalizationPerUser] WITH CHECKADD
FOREIGN KEY([PathId])
REFERENCES [dbo].[aspnet_Paths] ([PathId])
REFERENCES [dbo].[aspnet_Paths] ([PathId])
ALTER TABLE[dbo].[aspnet_PersonalizationPerUser] WITH CHECKADD
FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
ALTER TABLE[dbo].[aspnet_PersonalizationAllUsers] WITH CHECKADD
FOREIGN KEY([PathId])
REFERENCES [dbo].[aspnet_Paths] ([PathId])
REFERENCES [dbo].[aspnet_Paths] ([PathId])
ALTER TABLE [dbo].[aspnet_Roles] WITH CHECKADD FOREIGN
KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
ALTER TABLE [dbo].[aspnet_Paths] WITH CHECK ADDFOREIGN
KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
ALTER TABLE [dbo].[aspnet_Users] WITH CHECKADD FOREIGN
KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
- PetShop 4.0 数据库明细
- PetShop 4.0数据库
- 【数据库】DSN明细账目
- .Net PetShop 4.0的分布式数据库设计
- .Net PetShop 4.0的分布式数据库设计
- .Net PetShop 4.0的分布式数据库设计
- .Net PetShop 4.0的分布式数据库设计
- .Net PetShop 4.0的分布式数据库设计
- PetShop 4.0的4个数据库说明
- PetShop 4.0
- PetShop 数据库 连接类
- PetShop数据库解读
- petshop数据库解析
- 分析PetShop 4.0 的项目架构和分布式数据库设计
- PetShop 4.0 MSPetShop4Orders数据库中LineItem表各项解释
- PetShop学习记录----数据库访问
- PetShop 4.0架构分析
- PetShop 4.0架构分析
- json简介
- MFC注册窗口类
- 创业成功的36条定律
- Do You Have These Core Human Skills?你拥有这些核心人类技能吗？
- 去除python list中的重复值
- PetShop 4.0 数据库明细
- POJ 2186 Popular Cows
- 通用的XMLHttpRequest对象的创建方法
- [转]CentOS5.5使用smbfs文件系统
- Winform和WebForm的异同
- Android opengles 2D 优化
- 唐僧是位好领导
- poj 2251 Dungeon Master
- Android 中使用JUnit进行自动测试
