商城系统开发从0到1（sku之设计与实现）

最新推荐文章于 2024-05-24 18:24:15 发布

叫我坚哥

最新推荐文章于 2024-05-24 18:24:15 发布

阅读量985

点赞数

分类专栏： Java sku 商城系统文章标签： sku

本文链接：https://blog.csdn.net/qq_28406419/article/details/105871613

版权

商城系统的一个难点就是商品规格的设计，如何设计一个可拓展，灵活性高的呢，这是一个难点和痛点。下面直接上干货！

首先是数据库设计

1.SKU表

CREATE TABLE "TB_PEM_PRODUCT_SKU" (
"ID" VARCHAR2(32 BYTE) NOT NULL ,
"PRODUCT_ID" VARCHAR2(32 BYTE) NOT NULL ,
"ATTRS_VALUE_ID" VARCHAR2(200 BYTE) NULL ,
"ATTRS_VALUE" VARCHAR2(512 BYTE) NULL ,
"PROMOTION_PRICE" NUMBER(14) DEFAULT 0  NULL ,
"ORIGINAL_PRICE" NUMBER(14) DEFAULT 0  NULL ,
"LIMIT_COUNT" NUMBER(22) DEFAULT 0  NULL ,
"STOCK" NUMBER(22) DEFAULT 0  NULL ,
"PRICE" NUMBER(14) DEFAULT 0  NULL ,
"DELETE_STATUS" NUMBER(2) DEFAULT 1  NULL ,
"CREATE_TIME" DATE NULL ,
"CREATE_ID" VARCHAR2(32 BYTE) NULL ,
"CREATE_NAME" VARCHAR2(32 BYTE) NULL ,
"UPDATE_TIME" DATE NULL ,
"UPDATE_ID" VARCHAR2(32 BYTE) NULL ,
"UPDATE_NAME" VARCHAR2(32 BYTE) NULL ,
"EXCHANGE_AMOUNT" NUMBER(14) DEFAULT 0  NULL ,
"EXCHANGE_INTEGRAL" NUMBER(14) DEFAULT 0  NULL ,
"GOODS_STOCK" NUMBER(22) DEFAULT 0  NULL ,
"STORE_ID" VARCHAR2(32 BYTE) DEFAULT 11183  NULL ,
"INTEGRAL_USE_TYPE" NUMBER(2) DEFAULT 1  NULL ,
"SORT" NUMBER(5) DEFAULT 1  NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON TABLE "TB_PEM_PRODUCT_SKU" IS 'sku表';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."ID" IS 'ID';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."PRODUCT_ID" IS '商品ID';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."ATTRS_VALUE_ID" IS '销售属性ID，用-隔开';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."ATTRS_VALUE" IS '销售属性，用;隔开';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."PROMOTION_PRICE" IS '促销价/当前售价';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."ORIGINAL_PRICE" IS '成本价';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."LIMIT_COUNT" IS '限购数量';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."STOCK" IS '库存';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."PRICE" IS '划线价';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."DELETE_STATUS" IS '0：删除，1：正常';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."CREATE_ID" IS '创建人ID';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."CREATE_NAME" IS '创建人';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."UPDATE_TIME" IS '更新时间';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."UPDATE_ID" IS '更新人ID';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."UPDATE_NAME" IS '更新人';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."EXCHANGE_AMOUNT" IS 'EXCHANGE_TYPE为2时填写';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."EXCHANGE_INTEGRAL" IS '兑换积分';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."GOODS_STOCK" IS '积分商品可兑换数量';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."STORE_ID" IS '商店ID';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."INTEGRAL_USE_TYPE" IS '1：未配置积分商城 2：已配置积分商城';
COMMENT ON COLUMN "TB_PEM_PRODUCT_SKU"."SORT" IS '排序';

-- ----------------------------
-- Indexes structure for table TB_PEM_PRODUCT_SKU
-- ----------------------------
CREATE INDEX "PRODUCT_ID_SKU"
ON "TB_PEM_PRODUCT_SKU" ("PRODUCT_ID" ASC)
LOGGING
VISIBLE;

-- ----------------------------
-- Checks structure for table TB_PEM_PRODUCT_SKU
-- ----------------------------
ALTER TABLE "TB_PEM_PRODUCT_SKU" ADD CHECK ("ID" IS NOT NULL);
ALTER TABLE "TB_PEM_PRODUCT_SKU" ADD CHECK ("PRODUCT_ID" IS NOT NULL);
ALTER TABLE "TB_PEM_PRODUCT_SKU" ADD CHECK ("ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table TB_PEM_PRODUCT_SKU
-- ----------------------------
ALTER TABLE "TB_PEM_PRODUCT_SKU" ADD PRIMARY KEY ("ID");

2.销售属性表

CREATE TABLE "TB_PEM_PRODUCT_ATTR" (
"ID" VARCHAR2(32 BYTE) NOT NULL ,
"NAME" VARCHAR2(32 BYTE) NOT NULL ,
"DELETE_STATUS" NUMBER(2) DEFAULT 1  NULL ,
"CREATE_TIME" DATE NULL ,
"CREATE_ID" VARCHAR2(32 BYTE) NULL ,
"CREATE_NAME" VARCHAR2(32 BYTE) NULL ,
"UPDATE_TIME" DATE NULL ,
"UPDATE_ID" VARCHAR2(32 BYTE) NULL ,
"UPDATE_NAME" VARCHAR2(32 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON TABLE "TB_PEM_PRODUCT_ATTR" IS '销售属性表';
COMMENT ON COLUMN "TB_PEM_PRODUCT_ATTR"."NAME" IS '销售属性名称';
COMMENT ON COLUMN "TB_PEM_PRODUCT_ATTR"."DELETE_STATUS" IS '0：删除，1：正常';
COMMENT ON COLUMN "TB_PEM_PRODUCT_ATTR"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "TB_PEM_PRODUCT_ATTR"."CREATE_ID" IS '创建人ID';
COMMENT ON COLUMN "TB_PEM_PRODUCT_ATTR"."CREATE_NAME" IS '创建人';
COMMENT ON COLUMN "TB_PEM_PRODUCT_ATTR"."UPDATE_TIME" IS '更新时间';
COMMENT ON COLUMN "TB_PEM_PRODUCT_ATTR"."UPDATE_ID" IS '更新人ID';
COMMENT ON COLUMN "TB_PEM_PRODUCT_ATTR"."UPDATE_NAME" IS '更新人';

-- ----------------------------
-- Indexes structure for table TB_PEM_PRODUCT_ATTR
-- ----------------------------

-- ----------------------------
-- Checks structure for table TB_PEM_PRODUCT_ATTR
-- ----------------------------
ALTER TABLE "TB_PEM_PRODUCT_ATTR" ADD CHECK ("ID" IS NOT NULL);
ALTER TABLE "TB_PEM_PRODUCT_ATTR" ADD CHECK ("NAME" IS NOT NULL);
ALTER TABLE "TB_PEM_PRODUCT_ATTR" ADD CHECK ("ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table TB_PEM_PRODUCT_ATTR
-- ----------------------------
ALTER TABLE "TB_PEM_PRODUCT_ATTR" ADD PRIMARY KEY ("ID");

3.销售属性值表

CREATE TABLE "TB_PEM_PRODUCT_ATTR_VALUE" (
"ID" VARCHAR2(32 BYTE) NOT NULL ,
"ATTR_ID" VARCHAR2(32 BYTE) NOT NULL ,
"VALUE" VARCHAR2(50 BYTE) NOT NULL ,
"DELETE_STATUS" NUMBER(2) DEFAULT 1  NULL ,
"CREATE_TIME" DATE NULL ,
"CREATE_ID" VARCHAR2(32 BYTE) NULL ,
"CREATE_NAME" VARCHAR2(32 BYTE) NULL ,
"UPDATE_TIME" DATE NULL ,
"UPDATE_ID" VARCHAR2(32 BYTE) NULL ,
"UPDATE_NAME" VARCHAR2(32 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON TABLE "TB_PEM_PRODUCT_ATTR_VALUE" IS '销售属性值表';
COMMENT ON COLUMN "TB_PEM_PRODUCT_ATTR_VALUE"."ID" IS '销售属性值ID';
COMMENT ON COLUMN "TB_PEM_PRODUCT_ATTR_VALUE"."ATTR_ID" IS '销售属性ID';
COMMENT ON COLUMN "TB_PEM_PRODUCT_ATTR_VALUE"."VALUE" IS '销售属性值';
COMMENT ON COLUMN "TB_PEM_PRODUCT_ATTR_VALUE"."DELETE_STATUS" IS '0：删除，1：正常';
COMMENT ON COLUMN "TB_PEM_PRODUCT_ATTR_VALUE"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "TB_PEM_PRODUCT_ATTR_VALUE"."CREATE_ID" IS '创建人ID';
COMMENT