建立购物网站数据库 需要哪些表和字段 越详细越好

SQL SERVRE 2000 测试通过
CREATE DATABASE shop
GO
use shop
/ 用户信息 /
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMATABLES
WHERE TABLE_NAME = 'UserInfo_table')
DROP TABLE UserInfo_table
GO
CREATE TABLE UserInfo_table
(
userId smallint /用户编号/
IDENTITY(1,1),
loginName varchar(20) not null, /登陆名称/
userName varchar(20) not null, /用户名称/
userPwd varchar(10) not null, /用户密码/
userType varchar(20) not null, /用户类型/
userSex varchar(2), /用户性别/
userPhone varchar(20), /用户电话/
userEmail varchar(40), /用户邮件/
userAddress varchar(200), /用户地址/
userZip varchar(10), /用户邮编/
createTime datetime default getdate(), /注册时间/
updateTime datetime, /更新时间/
userStatus varchar(4) not null, /用户状态/
userLevel int, /用户级别/
constraint pk_userinfo primary key(userId)
)
/ 系统代码表 /
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMATABLES
WHERE TABLE_NAME = 'CommonCode_table')
DROP TABLE CommonCode_table
GO
CREATE TABLE CommonCode_table
(
codeType varchar(20) not null, /代码类型/
codeName varchar(20) not null, /代码名称/
codeValue varchar(100) not null, /代码值/
constraint pk_commoncode primary key(codeType, codeName)
)
/ 菜单信息 /
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMATABLES
WHERE TABLE_NAME = 'MenuShop_table')
DROP TABLE MenuShop_table
GO
CREATE TABLE MenuShop_table
(
menuId varchar(50) not null,
menuName varchar(50),
menuImg varchar(50),
menuSelImg varchar(50),
menuAction varchar(50),
menuLevel smallint not null,
parentMenuId varchar(50),
menuLine smallint not null,
isUserMenu bit not null,
constraint pk_menushop primary key(menuId)
)
/ 用户订单 /
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMATABLES
WHERE TABLE_NAME = 'UserOrder_table')
DROP TABLE UserOrder_table
GO
CREATE TABLE UserOrder_table
(
orderId varchar(50) not null, /订单号/
userId smallint not null, /订购人ID/
orderTime datetime not null, /订单产生日期/
orderStatus char(2) not null, /订单是否确认,0/1/
orderPassTime datetime, /确认时间/
orderPassId smallint, /订单处理人/
orderSendState char(2), /订单发送状态/
orderRecName varchar(20), /订单接收人姓名/
orderRecMail varchar(20),
orderRecAddress varchar(200), /订单接收地址/
orderRecZip varchar(10), /订单接受地址邮编/
orderTotalPrice decimal(10,2), /订单总价/
lineIndexNext int,
constraint pk_userorder primary key(orderId)
)
/ 订单中项目信息 /
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMATABLES
WHERE TABLE_NAME = 'LineItem_table')
DROP TABLE LineItem_table
GO
CREATE TABLE LineItem_table
(
orderId varchar(50) not null, /订单号/
lineIndex int not null, /订单索引/
itemId varchar(50) not null,
productId int not null, /产品ID/
quantity int not null, /订单项数量/
unitPrice decimal(10, 2) not null, /该订单项的价格/
orderStatus int not null,
constraint pk_lineitem primary key(orderId, lineIndex)
)
/ 商品类别信息 /
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMATABLES
WHERE TABLE_NAME = 'ProductCategory_table')
DROP TABLE ProductCategory_table
GO
CREATE TABLE ProductCategory_table
(
catId int
IDENTITY(1,1), /类别编号/
catName varchar(100) not null, /类别名称/
parentId int, /父级类别ID/
catHaveChild varchar(2) not null, /是否有子类别Y/N/
sort int not null, /排序标志/
inputdate datetime default getdate(), /建立时间/
isValid varchar(2), /此类别是否有效/
decs varchar(255), /说明/
constraint pk_productcategory primary key(catId)
)
/ 产品信息 /
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMATABLES
WHERE TABLE_NAME = 'ProductInfo_table')
DROP TABLE ProductInfo_table
GO
CREATE TABLE ProductInfo_table
(
productId int
IDENTITY(1,1), /编号/
catId int not null, /类别ID/
productName varchar(100), /物品名称/
productContent varchar(4000),
productDesc varchar(1000), /物品简介/
isPrompt bit default 0, /是否优惠/
registerTime datetime default getdate(), /上架日期/
listPrice decimal(10, 2), /物品价格/
unitPrice decimal(10, 2), /会员价格/
orderDesc varchar(1000), /订购说明/
productImgUrl varchar(200), /物品/
sort int, /排序标记/
productCount int, /库存量/
isValid bit not null,
constraint pk_productInfo primary key(productId),
constraint fk_product foreign key(catId)
references ProductCategory_table(catId)
)
/ /
create index ProductCategory on ProductInfo_table(catId);
create index ProdcutName on ProductInfo_table(productName);
/ 公告信息 /
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMATABLES
WHERE TABLE_NAME = 'BulletinInfo_table')
DROP TABLE BulletinInfo_table
GO
CREATE TABLE BulletinInfo_table
(
bulletinId int
IDENTITY(1,1), /编号/
bulletinTitle varchar(100) not null, /公告板标题/
bulletinBody varchar(4000), /公告板内容/
inputDate datetime default getdate(), /添加日期/
updateDate datetime, /更新日期/
inputUserId smallint, /添加管理员ID/
bulletinPoint int, /浏览量/
bulletinSort int, /排序标记/
isValid char(2) default 1, /是否有效/
constraint pk_bulletinInfo primary key(bulletinId)
)
/ 公告信息 /
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMATABLES
WHERE TABLE_NAME = 'ItemInfo_table')
DROP TABLE ItemInfo_table
GO
CREATE TABLE ItemInfo_table
(
itemId varchar(50), /项目ID/
productId int not null, /项目产品ID/
quantity int not null,
listPrice decimal(10,2), /物品价格/
unitPrice decimal(10,2), /会员价格/
status varchar(2), /更新日期/
constraint pk_iteminfo primary key(itemId)
)
/ /
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMATABLES
WHERE TABLE_NAME = 'Serial_Number')
DROP TABLE Serial_Number
GO
CREATE TABLE Serial_Number
(
serialId varchar(50) not null,
SerialNumber int,
constraint pk_SerialNumber primary key(serialId)
)

以上就是关于建立购物网站数据库 需要哪些表和字段 越详细越好全部的内容，包括:建立购物网站数据库 需要哪些表和字段 越详细越好、、等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！