【校园商铺SSM-1】实体类设计与数据库创建

文章目录

- - 1. 区域实体类设计与对应数据库创建
  - 2. 用户信息实体类设计与对应数据库创建
  - 3. 微信账号，本地账号实体类的设计与对应数据库创建
  - 4. 头条实体类设计和数据库创建
  - 5. 店铺类别实体列设计和数据库创建
  - 6. 店铺实体类设计和数据库创建
  - 7. 商品类别实体类设计和数据库设计
  - 8. 商品详情图片实体类设计和数据库创建
  - 9. 商品实体类设计和数据库创建
  - 10. 头条

在这里插入图片描述

1. 区域实体类设计与对应数据库创建

在这里插入图片描述

@Data
public class Area {
    //ID
    private Integer areaId;
    //名称
    private String areaName;
    //权重
    private Integer priority;
    //创建时间
    private Date createTime;
    //更新时间
    private Date lastEditTime;
}

CREATE TABLE tb_area(
	area_id INT(2) NOT NULL AUTO_INCREMENT,
	area_name VARCHAR(200) NOT NULL,
	priority INT(2) NOT NULL DEFAULT 0,
	create_time DATETIME DEFAULT NULL,
	last_edit_time DATETIME DEFAULT NULL,
	PRIMARY KEY(area_id),
	UNIQUE KEY uk_area(area_name)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2. 用户信息实体类设计与对应数据库创建

在这里插入图片描述

@Data
public class PersonInfo {
    private Integer userId;
    private String name;
    private String profileImg;
    private String email;
    private String gender;
    private Integer enableStatus;
    
    //1.顾客 2.店家  3.超级管理员
    private Integer userType;
    private Date createTime;
    private Date lastEditTime;
}

CREATE TABLE tb_person_info(
	user_id INT(10) NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(32) DEFAULT NULL,
	profile_img VARCHAR(1024) DEFAULT NULL,
	email VARCHAR(2) DEFAULT NULL,
	enable_status INT(2) NOT NULL DEFAULT '0' COMMENT '0:禁止使用本商城，1:允许使用本商城',
	user_type INT(2) NOT NULL DEFAULT '1' COMMENT '1:顾客，2:店家，3:超级管理员',
	last_edit_time DATETIME DEFAULT NULL,
	PRIMARY KEY(user_id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

3. 微信账号，本地账号实体类的设计与对应数据库创建

在这里插入图片描述

@Data
public class WechatAuth {
    private Long wechatAuthId;
    private String openId;
    private Date createTime;
    private PersonInfo personInfo;
}

@Data
public class LocalAuth {
    private long localAuthId;
    private String username;
    private String password;
    private Date createTime;
    private Date lastEditTime;
    private PersonInfo personInfo;
}

CREATE TABLE tb_wechat_auth(
	wechat_auth_id INT(10) NOT NULL AUTO_INCREMENT,
	user_id INT(10) NOT NULL,
	open_id VARCHAR(1024) NOT NULL,
	create_time DATETIME DEFAULT NULL,
	PRIMARY KEY(wechat_auth_id),
	CONSTRAINT fk_wechatauth_profile FOREIGN KEY(user_id) REFERENCES tb_person_info(user_id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE tb_local_auth(
	local_auth_id INT(10) NOT NULL AUTO_INCREMENT,
	user_id INT(10) NOT NULL,
	username VARCHAR(128) NOT NULL,
	PASSWORD VARCHAR(128) NOT NULL,
	create_time DATETIME DEFAULT NULL,
	last_edit_time DATETIME DEFAULT NULL,
	PRIMARY KEY(local_auth_id),
	UNIQUE KEY uk_local_profile(username),
	CONSTRAINT fk_localauth_profile FOREIGN KEY(user_id) REFERENCES tb_person_info(user_id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

4. 头条实体类设计和数据库创建

在这里插入图片描述

@Data
public class HeadLine {
    private Long lineId;
    private String lineName;
    private String lineLink;
    private String lineImg;
    private Integer priority;
    private Integer enableStatus;
    private Date createTime;
    private Date lastEditTime;
}

CREATE TABLE tb_head_line(
	line_id INT(100) NOT NULL AUTO_INCREMENT,
	line_name VARCHAR(1000) DEFAULT NULL,
	line_link VARCHAR(2000) NOT NULL,
	line_img VARCHAR(2000) NOT NULL,
	priority INT(2) DEFAULT NULL,
	enable_status INT(2) NOT NULL DEFAULT '0',
	create_time DATETIME DEFAULT NULL,
	PRIMARY KEY(line_id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

5. 店铺类别实体列设计和数据库创建

在这里插入图片描述

@Data
public class ShopCategory {
    private Long shopCategoryId;
    private String shopCategoryName;
    private String shopCategoryDesc;
    private String shopCategoryImg;
    private Integer priority;
    private Date createTime;
    private Date lastEditTime;
    private ShopCategory parent;
}

CREATE TABLE tb_shop_category(
	shop_category_id INT(11) NOT NULL AUTO_INCREMENT,
	shop_category_name VARCHAR(100) NOT NULL DEFAULT '',
	shop_category_desc VARCHAR(1000) DEFAULT '',
	shop_category_img VARCHAR(2000) DEFAULT '0',
	priority INT(2) NOT NULL DEFAULT '0',
	create_time DATETIME DEFAULT NULL,
	last_edit_time DATETIME DEFAULT NULL,
	parent_id INT(11) DEFAULT NULL,
	PRIMARY KEY(shop_category_id),
	CONSTRAINT fk_shop_category_self FOREIGN KEY(parent_id) REFERENCES tb_shop_category(shop_category_id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

6. 店铺实体类设计和数据库创建

在这里插入图片描述

@Data
public class Shop {
    private Long shopId;
    private String shopDesc;
    private String shopAddr;
    private String phone;
    private String shopImg;
    private Integer priority;
    private Date createTime;
    private Date lastEditTime;
    private Integer enableStatus;
    private String advice;
    private Area area;
    private PersonInfo owner;
    private ShopCategory shopCategory;
}

CREATE TABLE tb_shop(
	shop_id INT(10) NOT NULL AUTO_INCREMENT,
	owner_id INT(10) NOT NULL COMMENT '店铺创建人',
	area_id INT(5) DEFAULT NULL,
	shop_category_id INT(11) DEFAULT NULL,
	shop_name VARCHAR(256) NOT NULL,
	shop_desc VARCHAR(1024) DEFAULT NULL,
	shop_addr VARCHAR(200) DEFAULT NULL,
	phone VARCHAR(128) DEFAULT NULL,
	shop_img VARCHAR(1024) DEFAULT NULL,
	priority INT(3) DEFAULT '0',
	create_time DATETIME DEFAULT NULL,
	last_edit_time DATETIME DEFAULT NULL,
	enable_status INT(2) NOT NULL DEFAULT '0',
	advice VARCHAR(255) DEFAULT NULL,
	PRIMARY KEY(shop_id),
	CONSTRAINT fk_shop_area FOREIGN KEY(area_id) REFERENCES tb_area(area_id),
	CONSTRAINT fk_shop_profile FOREIGN KEY(owner_id) REFERENCES tb_person_info(user_id),
	CONSTRAINT fk_shop_shopcate FOREIGN KEY(shop_category_id) REFERENCES tb_shop_category(shop_category_id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

7. 商品类别实体类设计和数据库设计

在这里插入图片描述

@Data
public class ProductCategory {
    private Long productCategoryId;
    private Long shopId;
    private String productCategoryName;
    private Integer priority;
    private Date createTime;
}

CREATE TABLE tb_product_category(
	product_category_id INT(11) NOT NULL AUTO_INCREMENT,
	product_category_name VARCHAR(100) NOT NULL,
	priority INT(2) DEFAULT '0',
	create_time DATETIME DEFAULT NULL,
	shop_id INT(20) NOT NULL DEFAULT '0',
	PRIMARY KEY(product_category_id),
	CONSTRAINT fk_procate_shop FOREIGN KEY (shop_id) REFERENCES tb_shop(shop_id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

8. 商品详情图片实体类设计和数据库创建

@Data
public class ProductImg {
	private Long productImgId;
	private String imgAddr;
	private String imgDesc;
	private Integer priority;
	private Date createTime;
	private Long productId;
}

CREATE TABLE `tb_product_img` (
  `product_img_id` int(20) NOT NULL AUTO_INCREMENT,
  `img_addr` varchar(2000) NOT NULL,
  `img_desc` varchar(2000) DEFAULT NULL,
  `priority` int(2) DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `product_id` int(20) DEFAULT NULL,
  PRIMARY KEY (`product_img_id`),
  KEY `fk_proimg_product` (`product_id`),
  CONSTRAINT `fk_proimg_product` FOREIGN KEY (`product_id`) REFERENCES `tb_product` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8

9. 商品实体类设计和数据库创建

在这里插入图片描述

@Data
public class Product implements Serializable{
	private static final long serialVersionUID = -349433539553804024L;
	private Long productId;
	private String productName;
	private String productDesc;
	private String imgAddr;// 简略图
	private String normalPrice;
	private String promotionPrice;
	private Integer priority;
	private Date createTime;
	private Date lastEditTime;
	private Integer enableStatus;
	private Integer point;

	private List<ProductImg> productImgList;
	private ProductCategory productCategory;
	private Shop shop;
}

CREATE TABLE `tb_product` (
  `product_id` int(100) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(100) NOT NULL,
  `product_desc` varchar(2000) DEFAULT NULL,
  `img_addr` varchar(2000) DEFAULT '',
  `normal_price` varchar(100) DEFAULT NULL,
  `promotion_price` varchar(100) DEFAULT NULL,
  `priority` int(2) NOT NULL DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `last_edit_time` datetime DEFAULT NULL,
  `enable_status` int(2) NOT NULL DEFAULT '0',
  `point` int(10) DEFAULT NULL,
  `product_category_id` int(11) DEFAULT NULL,
  `shop_id` int(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_id`),
  KEY `fk_product_shop` (`shop_id`),
  KEY `fk_product_procate` (`product_category_id`),
  CONSTRAINT `fk_product_procate` FOREIGN KEY (`product_category_id`) REFERENCES `tb_product_category` (`product_category_id`),
  CONSTRAINT `fk_product_shop` FOREIGN KEY (`shop_id`) REFERENCES `tb_shop` (`shop_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

10. 头条

@Data
public class HeadLine {
    private Long lineId;
    private String lineName;
    private String lineLink;
    private String lineImg;
    private Integer priority;
    //0：表示不可用，1：可用
    private Integer enableStatus;
    private Date createTime;
    private Date lastEditTime;
}

CREATE TABLE `tb_head_line` (
  `line_id` INT(100) NOT NULL AUTO_INCREMENT,
  `line_name` VARCHAR(1000) DEFAULT NULL,
  `line_link` VARCHAR(2000) NOT NULL,
  `line_img` VARCHAR(2000) NOT NULL,
  `priority` INT(2) DEFAULT NULL,
  `enable_status` INT(2) NOT NULL DEFAULT '0' COMMENT '0:不可用,1:可用',
  `create_time` DATETIME DEFAULT NULL,
  `last_edit_time` DATETIME DEFAULT NULL,
  PRIMARY KEY (`line_id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

关联关系

在这里插入图片描述

DROP DATABASE o2o
CREATE DATABASE o2o
USE o2o
CREATE TABLE tb_area(
	area_id INT(2) NOT NULL AUTO_INCREMENT,
	area_name VARCHAR(200) NOT NULL,
	priority INT(2) NOT NULL DEFAULT 0,
	create_time DATETIME DEFAULT NULL,
	last_edit_time DATETIME DEFAULT NULL,
	PRIMARY KEY(area_id),
	UNIQUE KEY uk_area(area_name)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE tb_person_info(
	user_id INT(10) NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(32) DEFAULT NULL,
	profile_img VARCHAR(1024) DEFAULT NULL,
	email VARCHAR(200) DEFAULT NULL,
	gender VARCHAR(2) DEFAULT NULL,
	enable_status INT(2) NOT NULL DEFAULT '0' COMMENT '0:禁止使用本商城，1:允许使用本商城',
	user_type INT(2) NOT NULL DEFAULT '1' COMMENT '1:顾客，2:店家，3:超级管理员',
	create_time DATETIME DEFAULT NULL,
	last_edit_time DATETIME DEFAULT NULL,
	PRIMARY KEY(user_id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE tb_wechat_auth(
	wechat_auth_id INT(10) NOT NULL AUTO_INCREMENT,
	user_id INT(10) NOT NULL,
	open_id VARCHAR(1024) NOT NULL,
	create_time DATETIME DEFAULT NULL,
	PRIMARY KEY(wechat_auth_id),
	CONSTRAINT fk_wechatauth_profile FOREIGN KEY(user_id) REFERENCES tb_person_info(user_id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE tb_local_auth(
	local_auth_id INT(10) NOT NULL AUTO_INCREMENT,
	user_id INT(10) NOT NULL,
	username VARCHAR(128) NOT NULL,
	PASSWORD VARCHAR(128) NOT NULL,
	create_time DATETIME DEFAULT NULL,
	last_edit_time DATETIME DEFAULT NULL,
	PRIMARY KEY(local_auth_id),
	UNIQUE KEY uk_local_profile(username),
	CONSTRAINT fk_localauth_profile FOREIGN KEY(user_id) REFERENCES tb_person_info(user_id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE tb_shop_category(
	shop_category_id INT(11) NOT NULL AUTO_INCREMENT,
	shop_category_name VARCHAR(100) NOT NULL DEFAULT '',
	shop_category_desc VARCHAR(1000) DEFAULT '',
	shop_category_img VARCHAR(2000) DEFAULT '0',
	priority INT(2) NOT NULL DEFAULT '0',
	create_time DATETIME DEFAULT NULL,
	parent_id INT(11) DEFAULT NULL,
	PRIMARY KEY(shop_category_id),
	CONSTRAINT fk_shop_category_self FOREIGN KEY(parent_id) REFERENCES tb_shop_category(shop_category_id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE tb_shop(
	shop_id INT(10) NOT NULL AUTO_INCREMENT,
	owner_id INT(10) NOT NULL COMMENT '店铺创建人',
	area_id INT(5) DEFAULT NULL,
	shop_category_id INT(11) DEFAULT NULL,
	shop_name VARCHAR(256) NOT NULL,
	shop_desc VARCHAR(1024) DEFAULT NULL,
	shop_addr VARCHAR(200) DEFAULT NULL,
	phone VARCHAR(128) DEFAULT NULL,
	shop_img VARCHAR(1024) DEFAULT NULL,
	priority INT(3) DEFAULT '0',
	create_time DATETIME DEFAULT NULL,
	last_edit_time DATETIME DEFAULT NULL,
	enable_status INT(2) NOT NULL DEFAULT '0',
	advice VARCHAR(255) DEFAULT NULL,
	PRIMARY KEY(shop_id),
	CONSTRAINT fk_shop_area FOREIGN KEY(area_id) REFERENCES tb_area(area_id),
	CONSTRAINT fk_shop_profile FOREIGN KEY(owner_id) REFERENCES tb_person_info(user_id),
	CONSTRAINT fk_shop_shopcate FOREIGN KEY(shop_category_id) REFERENCES tb_shop_category(shop_category_id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE tb_product_category(
	product_category_id INT(11) NOT NULL AUTO_INCREMENT,
	product_category_name VARCHAR(100) NOT NULL,
	priority INT(2) DEFAULT '0',
	create_time DATETIME DEFAULT NULL,
	shop_id INT(20) NOT NULL DEFAULT '0',
	PRIMARY KEY(product_category_id),
	CONSTRAINT fk_procate_shop FOREIGN KEY (shop_id) REFERENCES tb_shop(shop_id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE `tb_product` (
  `product_id` INT(100) NOT NULL AUTO_INCREMENT,
  `product_name` VARCHAR(100) NOT NULL,
  `product_desc` VARCHAR(2000) DEFAULT NULL,
  `img_addr` VARCHAR(2000) DEFAULT '',
  `normal_price` VARCHAR(100) DEFAULT NULL,
  `promotion_price` VARCHAR(100) DEFAULT NULL,
  `priority` INT(2) NOT NULL DEFAULT '0',
  `create_time` DATETIME DEFAULT NULL,
  `last_edit_time` DATETIME DEFAULT NULL,
  `enable_status` INT(2) NOT NULL DEFAULT '0',
  `point` INT(10) DEFAULT NULL,
  `product_category_id` INT(11) DEFAULT NULL,
  `shop_id` INT(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_id`),
  KEY `fk_product_shop` (`shop_id`),
  KEY `fk_product_procate` (`product_category_id`),
  CONSTRAINT `fk_product_procate` FOREIGN KEY (`product_category_id`) REFERENCES `tb_product_category` (`product_category_id`),
  CONSTRAINT `fk_product_shop` FOREIGN KEY (`shop_id`) REFERENCES `tb_shop` (`shop_id`)
) ENGINE=INNODB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

CREATE TABLE `tb_product_img` (
  `product_img_id` INT(20) NOT NULL AUTO_INCREMENT,
  `img_addr` VARCHAR(2000) NOT NULL,
  `img_desc` VARCHAR(2000) DEFAULT NULL,
  `priority` INT(2) DEFAULT '0',
  `create_time` DATETIME DEFAULT NULL,
  `product_id` INT(20) DEFAULT NULL,
  PRIMARY KEY (`product_img_id`),
  KEY `fk_proimg_product` (`product_id`),
  CONSTRAINT `fk_proimg_product` FOREIGN KEY (`product_id`) REFERENCES `tb_product` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8;

【校园商铺SSM-1】实体类设计与数据库创建

文章目录

1. 区域实体类设计与对应数据库创建

2. 用户信息实体类设计与对应数据库创建

3. 微信账号，本地账号实体类的设计与对应数据库创建

4. 头条实体类设计和数据库创建

5. 店铺类别实体列设计和数据库创建

6. 店铺实体类设计和数据库创建

7. 商品类别实体类设计和数据库设计

8. 商品详情图片实体类设计和数据库创建

9. 商品实体类设计和数据库创建

10. 头条

相关推荐

一个分享Java & Python知识的社区