常用数据库设计

[TOC]

设计规范

ID都采用的是bigIncrements

用户模块

用户表

Schema::create('user', function (Blueprint $table) {
     $table->bigIncrements('id');
     $table->string('avatar')->nullable()->comment('头像');
     $table->string('nickname')->nullable()->comment('昵称');
     $table->string('code', 10)->comment('邀请码');
     $table->integer('pid') ->default(0)->comment('上级用户');
     $table->string('tree', 1000) ->default(0) ->comment('用户族谱 邀请关系');
     $table->string('phone', 20)->nullable()->comment('手机号');
     $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
     $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));
     $table->timestamp('deleted_at')->nullable()->comment('删除时间');
     $table->engine = 'InnoDB';
    });

用户登录授权表

Schema::create('user_auth', function (Blueprint $table) {
     $table->bigIncrements('id');
     $table->integer('user_id');
     $table->string('method')->default(1)->comment('登录类型 如：1=手机号码、QQ号码、微信号、微博号、邮箱地址、登录账号');
     $table->string('openid', 50)->comment('账号或openid');
     $table->string('password', 50)->comment('密码');
     $table->string('token', 50)->comment('通行口令 用于账户一致性检测');
     $table->tinyInteger('status')->default(1)->comment('账号状态:1=有效 0=禁用');
     $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
     $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));
     $table->timestamp('deleted_at')->nullable()->comment('删除时间');
    $table->engine = 'InnoDB';
    });

用户联合平台账号信息表

Schema::create('user_union_platform', function (Blueprint $table) {
     $table->bigIncrements('id');
     $table->integer('user_id');
    $table->string('platform_name', 50)->nullable()->comment('平台名称 支付宝/微信/某某银行');

    $table->string('user_name', 50)->nullable()->comment('账户用户名');
     $table->string('account', 50)->nullable()->comment('账户账号');
     $table->string('img',191)->nullable()->comment('图片');
     $table->string('note',191)->nullable()->comment('备注信息');
     $table->tinyInteger('status')->default(1)->comment('账号状态:1=有效 0=禁用');
     $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
     $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));
     $table->timestamp('deleted_at')->nullable()->comment('删除时间');
    $table->engine = 'InnoDB';
    });

登录日志表

Schema::create('login_log', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->integer('user_id')->unsigned()->nullable()->comment('用户id');
            $table->string('openid', 20)  ->nullable() ->comment('输入的账号');
            $table->string('password')  ->nullable() ->comment('输入的密码');
            $table->string('ip', 20)->nullable()->comment('登录ip');
            $table->string('dev', 50)->nullable()->comment('登录设备');
            $table->string('dev_name', 191)->nullable()->comment('设备名称');
            $table->tinyInteger('status')->default(1)->comment('状态 0=失败 1=成功');
            $table->string('location', 50) ->nullable()  ->comment('所在地点');
            $table->string('longitude', 20)->nullable()->comment('活动地址经度');
            $table->string('latitude', 20)->nullable()->comment('活动地址纬度');
            $table->string('geohash', 50)->nullable()->comment('二维地址转一维');
            $table->string('note', 10)->nullable()->comment('备注');
            $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
            $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));
            $table->timestamp('deleted_at')->nullable()->comment('删除时间');
            $table->engine = 'InnoDB';
        });

电商模块

店铺表

 Schema::create('ec_shop', function (Blueprint $table) {

 $table->bigIncrements('id');

 $table->integer('user_id')->comment('绑定店铺的用户id');

 $table->string('name')->comment('店铺名称');

 $table->text('desc')->comment('店铺介绍');

 $table->string('avatar')->nullable()->comment('店铺头像');

 $table->string('phone', 32)->nullable()->comment('店铺联系人电话');

 $table->string('address')->nullable()->comment('店铺联系地址');

 $table->integer('status')->default(0)->comment('审核是否通过 0为不通过，1为通过');

 $table->integer('return_address')->nullable()->comment('退货地址');

 $table->string('longitude')->nullable()->comment('经度');

 $table->string('latitude')->nullable()->comment('纬度');

 $table->string('geohash')->nullable()->comment('二维空间的一维映射');

 $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));

 $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));

 $table->timestamp('deleted_at')->nullable()->comment('删除时间');
$table->comment = '店铺表';
        });

地址表

        Schema::create('ec_address', function (Blueprint $table) {

 $table->bigIncrements('id');

 $table->integer('user_id')->comment('用户id');

 $table->string('name')->comment('收货人姓名');

 $table->string('phone', 32)->nullable()->comment('联系人电话');

 $table->string('province')->nullable()->comment('省');

 $table->string('city')->nullable()->comment('市');

 $table->string('district')->nullable()->comment('区');

 $table->string('details')->nullable()->comment('详细地址');

 $table->string('zip_code')->nullable()->comment('邮政编码');

 $table->tinyInteger('is_default')->default(0)->comment('1为默认  是否为默认地址');

 $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));

 $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));

 $table->timestamp('deleted_at')->nullable()->comment('删除时间');

 //$table->comment = '地址表';

 $table->engine = 'InnoDB';

        });

商品表

Schema::create('ec_goods', function (Blueprint $table) {

 $table->bigIncrements('id');

 $table->integer('store_id')->nullable()->comment('商铺id');

 $table->string('name')->nullable()->comment('商品名称');

 $table->string('caption')->nullable()->comment('副标题');

 $table->string('logo')->nullable()->comment('商品封面图');

 $table->string('image')->nullable()->comment('商品轮播图');

 $table->text('introduction')->comment('商品介绍');

 $table->string('sn')->comment('商品编号');

 $table->string('sales')->comment('销售数');

 $table->integer('hits')->comment('点击数');

 $table->tinyInteger('status')->default(0)->comment('商品状态(0待审核, 1审核通过, 2不通过)');

 $table->tinyInteger('is_market_able')->comment('是否上架 0不上架, 1上架');

 $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));

 $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));

 $table->timestamp('deleted_at')->nullable()->comment('删除时间');

 $table->engine = 'InnoDB';

 $table->comment = '商品表';

        });

商品规格表

        Schema::create('ec_goods_sku', function (Blueprint $table) {

 $table->bigIncrements('id');

 $table->integer('goods_id')->nullable()->comment('商品ID');

 $table->string('name')->comment('规格名称');

 $table->decimal('cost_price', 12, 2)->nullable()->unsigned()->comment('成本价');

 $table->decimal('sell_price', 12, 2)->nullable()->unsigned()->comment('销售价');

 $table->decimal('market_price', 12, 2)->nullable()->unsigned()->comment('市场价');

 $table->decimal('fee', 12, 2)->nullable()->unsigned()->comment('运费');

 $table->integer('inventory')->nullable()->comment('库存');

 $table->tinyInteger('status')->default(0)->comment('商品状态(0不展示, 1展示)');

 $table->tinyInteger('extra_price_sn' )->nullable()->unsigned()->comment('额外的收费 币种');

 $table->decimal('extra_price', 12, 2)->nullable()->unsigned()->comment('额外的收费 其他币种（积分/宝石）');

 $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));

 $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));

 $table->timestamp('deleted_at')->nullable()->comment('删除时间');

 $table->engine = 'InnoDB';

 $table->comment = '商品规格表';

        });

商品分类表

        Schema::create('ec_category', function (Blueprint $table) {

 $table->bigIncrements('id');

 $table->string('name')->comment('分类名称');

 $table->integer('pid')->nullable()->comment('父级id');

 $table->string('logo')->nullable()->comment('分类封面图');

 $table->integer('sort')->nullable()->comment('排序');

 $table->integer('status')->default(1)->comment('分类状态  0为未开启 1为开启');

 $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));

 $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));

 $table->timestamp('deleted_at')->nullable()->comment('删除时间');

 $table->engine = 'InnoDB';

 $table->comment = '商品分类表';

        });

商品订单表

        Schema::create('ec_order', function (Blueprint $table) {

 $table->increments('id')->comment('订单表');

 $table->integer('user_id')->comment('用户ID');

 $table->string('sn')->comment('订单流水号');

 $table->string('out_trade_no', 50)->comment('第三方订单号');

 $table->decimal('price', 16, 2)->comment('单价');

 $table->decimal('quantity', 16, 2)->comment('数量');

 $table->decimal('total_price', 16, 2)->comment('总价');

 $table->string('area_name', 50)->comment('地区名称');

 $table->string('address', 50)->comment('详细地址');

 $table->string('consignee', 50)->comment('收货人');

 $table->string('phone', 50)->comment('电话');

 $table->string('zip_code', 50)->comment('邮政编码');

 $table->string('note', 50)->comment('备注');

 $table->tinyInteger('status')->comment('订单状态 (-3退款成功 -2申请退款  -1取消订单 0待付款 1待收货 2待评价 3已完成)');

 $table->timestamp('completed_at')->nullable()->comment('订单完成时间');

 $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));

 $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));

 $table->timestamp('deleted_at')->nullable()->comment('删除时间');

 $table->engine = 'InnoDB';

 $table->comment = '商品订单表';

        });

商品购物车表

        Schema::create('ec_cart', function (Blueprint $table) {

 $table->bigIncrements('id');

 $table->integer('goods_id')->nullable()->comment('商品ID');

 $table->integer('quantity')->nullable()->comment('商品数量');

 $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));

 $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));

 $table->timestamp('deleted_at')->nullable()->comment('删除时间');

 $table->engine = 'InnoDB';

 $table->comment = '商品购物车表';

        });

资产模块

资产表

Schema::create('assets', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name', 20)->nullable()->comment('资产名称');
            $table->string('sn', 20)->nullable()->comment('资产编号');
            $table->string('note') ->nullable()->comment('备注');
            $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
            $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));
            $table->timestamp('deleted_at')->nullable()->comment('删除时间');
            //$table->comment = '资产表';
            $table->engine = 'InnoDB';
        });

资产用户表

 Schema::create('assets_user', function (Blueprint  $table) {

 $table->bigIncrements('id');

 $table->integer('user_id') ->unsigned() ->nullable() ->comment('用户id');

 $table->string('sn')->nullable()->comment('资产编号');

 $table->decimal('latest', 16, 2)->default(0)->comment('最新资产总额');

 $table->decimal('total', 16, 2)->default(0)->comment('累计资产总额');

 $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));

 $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));

 $table->timestamp('deleted_at')->nullable()->comment('删除时间');

 //$table->comment = '资产用户表';

 $table->engine = 'InnoDB';

        });

资产用户日志表

 Schema::create('assets_user_log', function (Blueprint  $table) {

 $table->bigIncrements('id');

 $table->integer('user_id') ->unsigned() ->nullable() ->comment('用户id');

 $table->string('sn')->nullable()->comment('资产编号');

 $table->decimal('origin', 16, 2) ->nullable()  ->comment('原始资产'); //资产可以是积分，宝石，现金，次数等一切跟用户有关的数字数据

 $table->decimal('change', 16, 2) ->nullable()  ->comment('改变资产');

 $table->decimal('latest', 16, 2)->nullable()->comment('最新资产');

 $table->tinyInteger('type')->nullable()->comment('提现状态:0=支出 1=收入');  

 $table->string('note') ->comment('来源');

 $table->string('sign') ->comment('安全校验');

 $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));

 $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));

 $table->timestamp('deleted_at')->nullable()->comment('删除时间');

 //$table->comment = '资产用户日志表';

 $table->engine = 'InnoDB';

        });

本作品采用《CC 协议》，转载必须注明作者和本文链接

三餐四季

88 声望

暂无个人描述~

2 人点赞

从零开发一个电商项目，功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等

从小程序个人账户申请开始，带你一步步进行开发一个微信小程序，直到提交微信控制台上线发布。

讨论数量: 3

Hello_Smile

见习版主 26 声望

有一个问题？为什么你的主健id 是bigint 但是外健id 又是int类型？？？

1年前评论

三餐四季（楼主）

感谢指正，这个问题我也有过考虑，但是基于实际情况考虑，外键实在是没必要设计成 bigint；主键之所以设计成bigint，是考虑到比如日志表，交易明细表这些表很容易就超出int类型的范围，为了统一所以都用了bigint，但外键一般是userid，shopid等，不太可能会超出范围，总的来说还是遵循了约定大于规范的原则。还是有些迷茫的，一方面想着一劳永逸，一方面又想着最小可用性

PHPer技术栈

课程读者 822 声望 / PHP&&Java @ sinosoft

这写了个啥，登录日志完全可以通过event(new login_log)实现

1年前评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容，与人为善，比聪明更重要！

帮助

常用数据库设计

设计规范

用户模块

用户表

用户登录授权表

用户联合平台账号信息表

登录日志表

电商模块

店铺表

地址表

商品表

商品规格表

商品分类表

商品订单表

商品购物车表

资产模块

资产表

资产用户表

资产用户日志表

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

常用数据库设计

设计规范

用户模块

用户表

用户登录授权表

用户联合平台账号信息表

登录日志表

电商模块

店铺表

地址表

商品表

商品规格表

商品分类表

商品订单表

商品购物车表

资产模块

资产表

资产用户表

资产用户日志表

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

请登录