数据库-库表设计 【分享一些库表设计经验】

--教师表

CREATE TABLE #Teacher(TeacherId int, Name nvarchar(20));

INSERT INTO #Teacher VALUES(1, '张老师'), (2, '王老师');

--学生表

CREATE TABLE #Student(StudentId int, Name nvarchar(20));

INSERT INTO #Student VALUES(1, '小张'), (2, '小赵');

--老师学生关系表
CREATE TABLE #Teacher_Student(StudentId int, TeacherId int);

INSERT INTO #Teacher_Student VALUES(1, 1), (1, 2),(2, 1), (2, 2);

select typeid,typename,parentid,countnumber 
from t_booktype   where parentid='0'

select  child.typeid,child.typename,
child.parentid,child.countnumber 		
from t_booktype child ,t_booktype  parent 
where child.parentid=parent.typeid

id
parent_id
deep     //当前树的深度
is_leaf  //是否叶子节点

select * from tree where deep=1

select * from tree where parent_id="" 

select * from tree where path like '1/4/%'

id
parent_id
deep     //当前树的深度
path     //根路径
is_leaf  //是否叶子节点

id
user_id //用户Id
item_id //道具ID
flag    //是否触发过特效 0-1
add_time
update_time

public enum ItemOnceFlagEnum {
    
    NONE(0),
    
    ITEM_ONE(1),//道具1
    
    ITEM_TWO(1 << 1), //道具2
    
    ITEM_Three(1 << 2), //道具3
    
    ;
    
    private int code;
    
    ItemOnceFlagEnum(int code) {
        this.code = code;
    }
    public int getCode() {
        return code;
    }
    
    public static ItemOnceFlagEnum valueOf(int code) {
        ItemOnceFlagEnum[] values = ItemOnceFlagEnum.values();
        for (ItemOnceFlagEnum flag : values) {
            if (flag.getCode() == code) {
                return flag;
            }
        }
        return NONE;
    }
}

id
user_id //用户Id
flag    //是否触发过特效 2的0次幂、2的1次幂。。。
add_time
update_time

user_id设置为唯一索引

    //判断是否使用过某项道具
    public static boolean isHasThisFlag(long flag, ItemOnceFlagEnum itemOnceFlagEnum) {
        return (flag & itemOnceFlagEnum.getCode()) > 0;
    }

    //添加道具使用记录
    public Result<Boolean> addOnceFlag(long userId, itemOnceFlagEnum flag) {
         ItemOnceFlagDO itemOnceFlagDO = itmeOnceFlagService.getOnceFlagMap(Collections.singletonList(userId));
         long calculateFlag = (Objects.isNull(blockOnceFlagDO) ? 0L : itemOnceFlagDO.getFlag()) | flag.getCode();
         itemOnceFlag.setFlag(calculateFlag);
         boolean res = itemOnceFlagService.addOrUpdateOnceFlag(itemOnceFlag);
    }

"INSERT INTO xxxx ( ) VALUES ( :1.userId,  :1.flag ) " +
            " ON DUPLICATE KEY UPDATE flag = :1.flag "

	//添加商品Action
	public String addToCar() throws Exception{
		//尝试从Session取出购物车
		ValueStack vs=ActionContext.getContext().getValueStack();
		Map<String,Goods> car=(Map<String,Goods>)vs.findValue("#session.car");
		
		BookService service=new BookServiceImpl();
		
		if(car==null){	
			//判断购物车是否存在
			//不存在则创建,将用户添加的商品加入Map 存入Session
			Map<String,Goods> map=new HashMap<String,Goods>();
			//查询数据将商品详情查询出出来，把我们感兴趣的属性封装到Goods实体中
			Book book=service.getSingleBook(bookid);
			Goods goods=new Goods();
			goods.setBookid(book.getBookid());
			goods.setCount(1);
			goods.setPrice(book.getPrice());
			goods.setCurrentprice(book.getCurrentprice());
			goods.setProductname(book.getProductname());
			
			map.put(bookid, goods);
			vs.setValue("#session.car", map);
		}else{		
			//判断用户添加的商品是否已经存在于购物车里
			//若存在根据key取出Goods->修改商品选购的数量
			//不存在则是首次添加，数量默认为1 ，将商品Put入map
			if(car.containsKey(bookid)){
				Goods product=car.get(bookid);
				product.setCount(product.getCount()+1);
			}else{			
				Book book=service.getSingleBook(bookid);
				Goods goods=new Goods();
				goods.setBookid(book.getBookid());
				goods.setCount(1);
				goods.setPrice(book.getPrice());
				goods.setCurrentprice(book.getCurrentprice());
				goods.setProductname(book.getProductname());				
				car.put(bookid, goods);
			}
		}	
		return Action.SUCCESS;
	}