[day20]谈购物流程设计

本来想除了管理功能外全部都在Line介面里面解决，但做了一阵子觉得越想越不对劲，重新考量了一下思路，所以今天鸽了，说是这麽说，但还是拉了几个表格，分别是产品(products)、购物车(shopping_cart)、购物车明细(cart_items)，说一下现在的流程想法

CREATE TABLE IF NOT EXISTS public.shopping_cart
(
    scid bigint NOT NULL DEFAULT nextval('shopping_cart_scid_seq'::regclass),
    uid text COLLATE pg_catalog."default" NOT NULL,
    createddate timestamp with time zone,
    CONSTRAINT shopping_cart_pkey PRIMARY KEY (scid),
    CONSTRAINT uid FOREIGN KEY (uid)
        REFERENCES public.customers (uid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

CREATE TABLE IF NOT EXISTS public.cart_items
(
    id bigint NOT NULL DEFAULT nextval('cart_items_id_seq'::regclass),
    scid bigint NOT NULL,
    productid integer,
    quantity integer,
    CONSTRAINT cart_items_pkey PRIMARY KEY (id),
    CONSTRAINT pid FOREIGN KEY (productid)
        REFERENCES public.products (pid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT scid FOREIGN KEY (scid)
        REFERENCES public.shopping_cart (scid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

CREATE TABLE IF NOT EXISTS public.products
(
    pid integer NOT NULL DEFAULT nextval('products_pid_seq'::regclass),
    product_name text COLLATE pg_catalog."default" NOT NULL,
    quantity integer NOT NULL,
    product_decp text COLLATE pg_catalog."default",
    createddate timestamp with time zone,
    expireddate timestamp with time zone,
    CONSTRAINT products_pkey PRIMARY KEY (pid)
)

CREATE TABLE IF NOT EXISTS public.orders
(
    oid bigint NOT NULL DEFAULT nextval('orders_oid_seq'::regclass),
    uid text COLLATE pg_catalog."default" NOT NULL,
    scid bigint NOT NULL,
    createddate timestamp with time zone,
    paid bigint NOT NULL,
    ostatus integer NOT NULL DEFAULT 0,
    CONSTRAINT orders_pkey PRIMARY KEY (oid),
    CONSTRAINT paid FOREIGN KEY (paid)
        REFERENCES public.payment_log (paid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT scid FOREIGN KEY (scid)
        REFERENCES public.shopping_cart (scid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT uid FOREIGN KEY (uid)
        REFERENCES public.customers (uid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

CREATE TABLE IF NOT EXISTS public.payment_log
(
    paid bigint NOT NULL DEFAULT nextval('payment_log_paid_seq'::regclass),
    type text COLLATE pg_catalog."default",
    ispaid boolean DEFAULT false,
    CONSTRAINT payment_log_pkey PRIMARY KEY (paid)
)

建立订单资料库流程

1. 建立购物车shopping_cart纪录
2. 依据products内产品类别、库存加入购物车纪录到cart_items
3. 使用者点选建立订单时，payment_log建立付款纪录
4. 付款成功後，订单所消耗的库存反映到products
5. 变更orders订单状态为待出货
6. 出货後变更订单状态为已出货
7. 下次使用者进行新增购物车时，分配一个新的购物车

原本想说购物车与购物明细放同一个表格，但实作到一半测试起来碍手碍脚，要检索内容，再更新，於是拉倒重新设计资料库，改为一个购物车ID，再用这个购物车ID连结购物车明细表格，这样购物车的内容与购物车本身分开纪录，更容易修改内容，订单与付款纪录也是差不多的原因拆成两半，今天先把大致上的流程盘点一遍确认，之後再滚动修正0rz