常见电商项目的数据库表设计(MySQL版)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了常见电商项目的数据库表设计(MySQL版),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含13095字,纯文字阅读大概需要19分钟。
内容图文
![常见电商项目的数据库表设计(MySQL版)](/upload/InfoBanner/zyjiaocheng/1188/9759155ec57248a7907ec8dd62a6c977.jpg)
用户模块
用户登录表(customer_login)
CREATE TABLE customer_login( customer_id INT UNSIGNED AUTO_INCREMENT NOTNULL COMMENT ‘用户ID‘, login_name VARCHAR(20) NOTNULL COMMENT ‘用户登录名‘, password CHAR(32) NOTNULL COMMENT ‘md5加密的密码‘, user_stats TINYINTNOTNULLDEFAULT1 COMMENT ‘用户状态‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_customerid(customer_id) ) ENGINE = innodb COMMENT ‘用户登录表‘
用户信息表(customer_inf)
CREATE TABLE customer_inf( customer_inf_id INT UNSIGNED AUTO_INCREMENT NOTNULL COMMENT ‘自增主键ID‘, customer_id INT UNSIGNED NOTNULL COMMENT ‘customer_login表的自增ID‘, customer_name VARCHAR(20) NOTNULL COMMENT ‘用户真实姓名‘, identity_card_type TINYINTNOTNULLDEFAULT1 COMMENT ‘证件类型:1 身份证,2 军官证,3 护照‘, identity_card_no VARCHAR(20) COMMENT ‘证件号码‘, mobile_phone INT UNSIGNED COMMENT ‘手机号‘, customer_email VARCHAR(50) COMMENT ‘邮箱‘, gender CHAR(1) COMMENT ‘性别‘, user_point INTNOTNULLDEFAULT0 COMMENT ‘用户积分‘, register_time TIMESTAMPNOTNULL COMMENT ‘注册时间‘, birthday DATETIME COMMENT ‘会员生日‘, customer_level TINYINTNOTNULLDEFAULT1 COMMENT ‘会员级别:1 普通会员,2 青铜,3白银,4黄金,5钻石‘, user_money DECIMAL(8,2) NOTNULLDEFAULT0.00 COMMENT ‘用户余额‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_customerinfid(customer_inf_id) ) ENGINE = innodb COMMENT ‘用户信息表‘;
用户级别表(customer_level_inf)
CREATE TABLE customer_level_inf( customer_level TINYINT NOT NULL AUTO_INCREMENT COMMENT ‘会员级别ID‘, level_name VARCHAR(10) NOTNULL COMMENT ‘会员级别名称‘, min_point INT UNSIGNED NOTNULLDEFAULT0 COMMENT ‘该级别最低积分‘, max_point INT UNSIGNED NOTNULLDEFAULT0 COMMENT ‘该级别最高积分‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_levelid(customer_level) ) ENGINE = innodb COMMENT ‘用户级别信息表‘;
用户地址表(customer_addr)
CREATE TABLE customer_addr( customer_addr_id INT UNSIGNED AUTO_INCREMENT NOTNULL COMMENT ‘自增主键ID‘, customer_id INT UNSIGNED NOTNULL COMMENT ‘customer_login表的自增ID‘, zip SMALLINTNOTNULL COMMENT ‘邮编‘, province SMALLINTNOTNULL COMMENT ‘地区表中省份的ID‘, city SMALLINTNOTNULL COMMENT ‘地区表中城市的ID‘, district SMALLINTNOTNULL COMMENT ‘地区表中的区ID‘, address VARCHAR(200) NOTNULL COMMENT ‘具体的地址门牌号‘, is_default TINYINTNOTNULL COMMENT ‘是否默认‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_customeraddid(customer_addr_id) ) ENGINE = innodb COMMENT ‘用户地址表‘;
用户积分日志表(customer_point_log)
CREATE TABLE customer_point_log( point_id INT UNSIGNED NOTNULL AUTO_INCREMENT COMMENT ‘积分日志ID‘, customer_id INT UNSIGNED NOTNULL COMMENT ‘用户ID‘, source TINYINT UNSIGNED NOTNULL COMMENT ‘积分来源:0订单,1登陆,2活动‘, refer_number INT UNSIGNED NOTNULLDEFAULT0 COMMENT ‘积分来源相关编号‘, change_point SMALLINTNOTNULLDEFAULT0 COMMENT ‘变更积分数‘, create_time TIMESTAMPNOTNULL COMMENT ‘积分日志生成时间‘, PRIMARYKEY pk_pointid(point_id) ) ENGINE = innodb COMMENT ‘用户积分日志表‘;
用户余额变动表(customer_balance_log)
CREATE TABLE customer_balance_log( balance_id INT UNSIGNED NOTNULL AUTO_INCREMENT COMMENT ‘余额日志ID‘, customer_id INT UNSIGNED NOTNULL COMMENT ‘用户ID‘, source TINYINT UNSIGNED NOTNULLDEFAULT1 COMMENT ‘记录来源:1订单,2退货单‘, source_sn INT UNSIGNED NOTNULL COMMENT ‘相关单据ID‘, create_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP COMMENT ‘记录生成时间‘, amount DECIMAL(8,2) NOTNULLDEFAULT0.00 COMMENT ‘变动金额‘, PRIMARYKEY pk_balanceid(balance_id) ) ENGINE = innodb COMMENT ‘用户余额变动表‘;
用户登陆日志表(customer_login_log)
CREATE TABLE customer_login_log( login_id INT UNSIGNED NOTNULL AUTO_INCREMENT COMMENT ‘登陆日志ID‘, customer_id INT UNSIGNED NOTNULL COMMENT ‘登陆用户ID‘, login_time TIMESTAMPNOTNULL COMMENT ‘用户登陆时间‘, login_ip INT UNSIGNED NOTNULL COMMENT ‘登陆IP‘, login_type TINYINTNOTNULL COMMENT ‘登陆类型:0未成功,1成功‘, PRIMARYKEY pk_loginid(login_id) ) ENGINE = innodb COMMENT ‘用户登陆日志表‘;
商品模块
品牌信息表(brand_info)
CREATE TABLE brand_info( brand_id SMALLINT UNSIGNED AUTO_INCREMENT NOTNULL COMMENT ‘品牌ID‘, brand_name VARCHAR(50) NOTNULL COMMENT ‘品牌名称‘, telephone VARCHAR(50) NOTNULL COMMENT ‘联系电话‘, brand_web VARCHAR(100) COMMENT ‘品牌网络‘, brand_logo VARCHAR(100) COMMENT ‘品牌logo URL‘, brand_desc VARCHAR(150) COMMENT ‘品牌描述‘, brand_status TINYINTNOTNULLDEFAULT0 COMMENT ‘品牌状态,0禁用,1启用‘, brand_order TINYINTNOTNULLDEFAULT0 COMMENT ‘排序‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_brandid (brand_id) )ENGINE=innodb COMMENT ‘品牌信息表‘;
分类信息表(product_category)
CREATE TABLE product_category( category_id SMALLINT UNSIGNED AUTO_INCREMENT NOTNULL COMMENT ‘分类ID‘, category_name VARCHAR(10) NOTNULL COMMENT ‘分类名称‘, category_code VARCHAR(10) NOTNULL COMMENT ‘分类编码‘, parent_id SMALLINT UNSIGNED NOTNULLDEFAULT0 COMMENT ‘父分类ID‘, category_level TINYINTNOTNULLDEFAULT1 COMMENT ‘分类层级‘, category_status TINYINTNOTNULLDEFAULT1 COMMENT ‘分类状态‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_categoryid(category_id) )ENGINE=innodb COMMENT ‘商品分类表‘
供应商信息表(supplier_info)
CREATE TABLE supplier_info( supplier_id INT UNSIGNED AUTO_INCREMENT NOTNULL COMMENT ‘供应商ID‘, supplier_code CHAR(8) NOTNULL COMMENT ‘供应商编码‘, supplier_name CHAR(50) NOTNULL COMMENT ‘供应商名称‘, supplier_type TINYINTNOTNULL COMMENT ‘供应商类型:1.自营,2.平台‘, link_man VARCHAR(10) NOTNULL COMMENT ‘供应商联系人‘, phone_number VARCHAR(50) NOTNULL COMMENT ‘联系电话‘, bank_name VARCHAR(50) NOTNULL COMMENT ‘供应商开户银行名称‘, bank_account VARCHAR(50) NOTNULL COMMENT ‘银行账号‘, address VARCHAR(200) NOTNULL COMMENT ‘供应商地址‘, supplier_status TINYINTNOTNULLDEFAULT0 COMMENT ‘状态:0禁止,1启用‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_supplierid(supplier_id) ) ENGINE = innodb COMMENT ‘供应商信息表‘;
商品信息表(product_info)
CREATE TABLE product_info( product_id INT UNSIGNED AUTO_INCREMENT NOTNULL COMMENT ‘商品ID‘, product_core CHAR(16) NOTNULL COMMENT ‘商品编码‘, product_name VARCHAR(20) NOTNULL COMMENT ‘商品名称‘, bar_code VARCHAR(50) NOTNULL COMMENT ‘国条码‘, brand_id INT UNSIGNED NOTNULL COMMENT ‘品牌表的ID‘, one_category_id SMALLINT UNSIGNED NOTNULL COMMENT ‘一级分类ID‘, two_category_id SMALLINT UNSIGNED NOTNULL COMMENT ‘二级分类ID‘, three_category_id SMALLINT UNSIGNED NOTNULL COMMENT ‘三级分类ID‘, supplier_id INT UNSIGNED NOTNULL COMMENT ‘商品的供应商ID‘, price DECIMAL(8,2) NOTNULL COMMENT ‘商品销售价格‘, average_cost DECIMAL(18,2) NOTNULL COMMENT ‘商品加权平均成本‘, publish_status TINYINTNOTNULLDEFAULT0 COMMENT ‘上下架状态:0下架1上架‘, audit_status TINYINTNOTNULLDEFAULT0 COMMENT ‘审核状态:0未审核,1已审核‘, weight FLOAT COMMENT ‘商品重量‘, length FLOAT COMMENT ‘商品长度‘, height FLOAT COMMENT ‘商品高度‘, width FLOAT COMMENT ‘商品宽度‘, color_type ENUM(‘红‘,‘黄‘,‘蓝‘,‘黑‘), production_date DATETIMENOTNULL COMMENT ‘生产日期‘, shelf_life INTNOTNULL COMMENT ‘商品有效期‘, descript TEXTNOTNULL COMMENT ‘商品描述‘, indate TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP COMMENT ‘商品录入时间‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_productid(product_id) ) ENGINE = innodb COMMENT ‘商品信息表‘;
商品图片表(product_pic_info)
CREATE TABLE product_pic_info( product_pic_id INT UNSIGNED AUTO_INCREMENT NOTNULL COMMENT ‘商品图片ID‘, product_id INT UNSIGNED NOTNULL COMMENT ‘商品ID‘, pic_desc VARCHAR(50) COMMENT ‘图片描述‘, pic_url VARCHAR(200) NOTNULL COMMENT ‘图片URL‘, is_master TINYINTNOTNULLDEFAULT0 COMMENT ‘是否主图:0.非主图1.主图‘, pic_order TINYINTNOTNULLDEFAULT0 COMMENT ‘图片排序‘, pic_status TINYINTNOTNULLDEFAULT1 COMMENT ‘图片是否有效:0无效 1有效‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_picid(product_pic_id) )ENGINE=innodb COMMENT ‘商品图片信息表‘;
商品评论表(product_comment)
CREATE TABLE product_comment( comment_id INT UNSIGNED AUTO_INCREMENT NOTNULL COMMENT ‘评论ID‘, product_id INT UNSIGNED NOTNULL COMMENT ‘商品ID‘, order_id BIGINT UNSIGNED NOTNULL COMMENT ‘订单ID‘, customer_id INT UNSIGNED NOTNULL COMMENT ‘用户ID‘, title VARCHAR(50) NOTNULL COMMENT ‘评论标题‘, content VARCHAR(300) NOTNULL COMMENT ‘评论内容‘, audit_status TINYINTNOTNULL COMMENT ‘审核状态:0未审核,1已审核‘, audit_time TIMESTAMPNOTNULL COMMENT ‘评论时间‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_commentid(comment_id) ) ENGINE = innodb COMMENT ‘商品评论表‘;
订单模块
订单主表(order_master)
CREATE TABLE order_master( order_id INT UNSIGNED NOTNULL AUTO_INCREMENT COMMENT ‘订单ID‘, order_sn BIGINT UNSIGNED NOTNULL COMMENT ‘订单编号 yyyymmddnnnnnnnn‘, customer_id INT UNSIGNED NOTNULL COMMENT ‘下单人ID‘, shipping_user VARCHAR(10) NOTNULL COMMENT ‘收货人姓名‘, province SMALLINTNOTNULL COMMENT ‘省‘, city SMALLINTNOTNULL COMMENT ‘市‘, district SMALLINTNOTNULL COMMENT ‘区‘, address VARCHAR(100) NOTNULL COMMENT ‘地址‘, payment_method TINYINTNOTNULL COMMENT ‘支付方式:1现金,2余额,3网银,4支付宝,5微信‘, order_money DECIMAL(8,2) NOTNULL COMMENT ‘订单金额‘, district_money DECIMAL(8,2) NOTNULLDEFAULT0.00 COMMENT ‘优惠金额‘, shipping_money DECIMAL(8,2) NOTNULLDEFAULT0.00 COMMENT ‘运费金额‘, payment_money DECIMAL(8,2) NOTNULLDEFAULT0.00 COMMENT ‘支付金额‘, shipping_comp_name VARCHAR(10) COMMENT ‘快递公司名称‘, shipping_sn VARCHAR(50) COMMENT ‘快递单号‘, create_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP COMMENT ‘下单时间‘, shipping_time DATETIME COMMENT ‘发货时间‘, pay_time DATETIME COMMENT ‘支付时间‘, receive_time DATETIME COMMENT ‘收货时间‘, order_status TINYINTNOTNULLDEFAULT0 COMMENT ‘订单状态‘, order_point INT UNSIGNED NOTNULLDEFAULT0 COMMENT ‘订单积分‘, invoice_time VARCHAR(100) COMMENT ‘发票抬头‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_orderid(order_id) )ENGINE = innodb COMMENT ‘订单主表‘;
订单详情表(order_detail)
CREATE TABLE order_detail( order_detail_id INT UNSIGNED NOTNULL AUTO_INCREMENT COMMENT ‘订单详情表ID‘, order_id INT UNSIGNED NOTNULL COMMENT ‘订单表ID‘, product_id INT UNSIGNED NOTNULL COMMENT ‘订单商品ID‘, product_name VARCHAR(50) NOTNULL COMMENT ‘商品名称‘, product_cnt INTNOTNULLDEFAULT1 COMMENT ‘购买商品数量‘, product_price DECIMAL(8,2) NOTNULL COMMENT ‘购买商品单价‘, average_cost DECIMAL(8,2) NOTNULL COMMENT ‘平均成本价格‘, weight FLOAT COMMENT ‘商品重量‘, fee_money DECIMAL(8,2) NOTNULLDEFAULT0.00 COMMENT ‘优惠分摊金额‘, w_id INT UNSIGNED NOTNULL COMMENT ‘仓库ID‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_orderdetailid(order_detail_id) )ENGINE = innodb COMMENT ‘订单详情表‘
购物车表(order_cart)
CREATE TABLE order_cart( cart_id INT UNSIGNED NOTNULL AUTO_INCREMENT COMMENT ‘购物车ID‘, customer_id INT UNSIGNED NOTNULL COMMENT ‘用户ID‘, product_id INT UNSIGNED NOTNULL COMMENT ‘商品ID‘, product_amount INTNOTNULL COMMENT ‘加入购物车商品数量‘, price DECIMAL(8,2) NOTNULL COMMENT ‘商品价格‘, add_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP COMMENT ‘加入购物车时间‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_cartid(cart_id) ) ENGINE = innodb COMMENT ‘购物车表‘;
仓库信息表(warehouse_info)
CREATE TABLE warehouse_info( w_id SMALLINT UNSIGNED NOTNULL AUTO_INCREMENT COMMENT ‘仓库ID‘, warehouse_sn CHAR(5) NOTNULL COMMENT ‘仓库编码‘, warehoust_name VARCHAR(10) NOTNULL COMMENT ‘仓库名称‘, warehouse_phone VARCHAR(20) NOTNULL COMMENT ‘仓库电话‘, contact VARCHAR(10) NOTNULL COMMENT ‘仓库联系人‘, province SMALLINTNOTNULL COMMENT ‘省‘, city SMALLINTNOTNULL COMMENT ‘市‘, distrct SMALLINTNOTNULL COMMENT ‘区‘, address VARCHAR(100) NOTNULL COMMENT ‘仓库地址‘, warehouse_status TINYINTNOTNULLDEFAULT1 COMMENT ‘仓库状态:0禁用,1启用‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_wid(w_id) )ENGINE = innodb COMMENT ‘仓库信息表‘;
商品库存表(warehouse_product)
CREATE TABLE warehouse_product( wp_id INT UNSIGNED NOTNULL AUTO_INCREMENT COMMENT ‘商品库存ID‘, product_id INT UNSIGNED NOTNULL COMMENT ‘商品ID‘, w_id SMALLINT UNSIGNED NOTNULL COMMENT ‘仓库ID‘, current_cnt INT UNSIGNED NOTNULLDEFAULT0 COMMENT ‘当前商品数量‘, lock_cnt INT UNSIGNED NOTNULLDEFAULT0 COMMENT ‘当前占用数据‘, in_transit_cnt INT UNSIGNED NOTNULLDEFAULT0 COMMENT ‘在途数据‘, average_cost DECIMAL(8,2) NOTNULLDEFAULT0.00 COMMENT ‘移动加权成本‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_wpid(wp_id) )ENGINE = innodb COMMENT ‘商品库存表‘
物流公司信息表(shipping_info)
CREATE TABLE shipping_info( ship_id TINYINT UNSIGNED NOTNULL AUTO_INCREMENT COMMENT ‘主键ID‘, ship_name VARCHAR(20) NOTNULL COMMENT ‘物流公司名称‘, ship_contact VARCHAR(20) NOTNULL COMMENT ‘物流公司联系人‘, telephone VARCHAR(20) NOTNULL COMMENT ‘物流公司联系电话‘, price DECIMAL(8,2) NOTNULLDEFAULT0.00 COMMENT ‘配送价格‘, modified_time TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT ‘最后修改时间‘, PRIMARYKEY pk_shipid(ship_id) )ENGINE = innodb COMMENT ‘物流公司信息表‘;
用户数据库(mc_customerdb)
- customer_inf:用户信息
- customer_login:用户登录
- customer_level_inf:用户级别
- customer_login_log:用户登录日志
- customer_point_log:用户积分日志
- customer_balance_log:用户余额变动表
商品数据库(mc_productdb)
-
- product_info:商品信息表
- product_pic_info:商品图片
- product_category:分类信息表
- product_supplier_info:供应商信息
- product_comment:商品评论
- product_brand_info:商品风类
订单数据库(mc_orderdb)
- order_master:订单主表
- order_detail:订单详情
- order_customer_addr:用户地址
- order_cart:购物车
- shipping_info:物流公司
- warehouse_info:仓库信息
- warehouse_product:商品库存
参考:https://www.jianshu.com/p/b89127a415df
- 订单详情表(order_detail)
原文:https://www.cnblogs.com/FondWang/p/12550956.html
内容总结
以上是互联网集市为您收集整理的常见电商项目的数据库表设计(MySQL版)全部内容,希望文章能够帮你解决常见电商项目的数据库表设计(MySQL版)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。