数据库多级联动表设计

设计社区数据库时，遇到有关

    物业公司 社区 楼栋 单元 房屋 的基础表设计。

    这几张表之间都是包含关系，故设计为此形式。有点类似于链表。为了叙述方便，暂且称大的概念为高级表，小的概念为低级表。低级表关系着一个高级表的主键id。以此可以查询到所有的数据。（此处为了表述简便，删除了其他无关字段）。设计如下：

此设计完全解开了各层之间的耦合性，用类似于链表的形式将多层数据关系起来。可以节约大量的数据库空间。而且如果发生了新增和修改操作时，花费的时间少，因为只要修改与之相关的那个表的主键即可。例如现在有数据如下。

dept	community	buliding	unit	house
id=0	id = 1	id=22	id=33	id=44
	dept_id = 0	community_id=1	buliding_id=22	unit_id=33

如果此时需要修改buliding的id为88时，只需要修改unit的bulidingId，虽然house和buliding间接相关，但是不需要修改。修改后如下：

dept	community	buliding	unit	house
id=0	id = 1	id=88	id=33	id=44
	dept_id = 0	community_id=1	buliding_id=88	unit_id=33

此优势有点类似于java中的LinkedList与比较于ArrayList的优势。

此设计有点如下：

1：节约了数据库空间，在数据库空间有限的情况下，采用此方案可获得空间的优势。

2：便于增加和更新操作，类似于链表的结构，可以最大程度的减少对数据的修改。

但空间的优化，导致的缺点毫无疑问的就是时间的牺牲。以空间换取时间。

试想：若已知若干个房屋，想知道这些房屋到底归属于何物业公司（部门），所要执行的查询时多么复杂。

1：根据houseid遍历unit表，查询出与之相关的unit

2：根据unit查询buliding表，查询出与之相关的buliding

3：根据buliding查询community表，查询出与之相关的community

4：根据community查询dept表，查询出与之相关的dept

由此过程，光是查询一条记录，就需要查询4张表，当多级关系的级数越来越多时，就会变得越复杂。例如房屋与用户产生关系。查询用户所关联的物业公司，就需要查询5张表，而且一个用户可能有n套房产，那么每查询一次一个用户所关联的物业公司时，就需要查询5张数据表n次。

下面是笔者为此付出的代码：有心人可以发现，因为是面向接口编程的，所以如果需要替换实现的话，只需要替换implement就可以啦，故此将接口也粘了上去。

package com.kingen.modules.sys.service;


import java.util.List;
import java.util.Map;
import java.util.Set;

import com.kingen.modules.sys.entity.SysDeptEntity;
import com.kingen.modules.sys.entity.SysUserEntity;
import com.kingen.modules.wy.entity.WyBuildingEntity;
import com.kingen.modules.wy.entity.WyCommunityEntity;
import com.kingen.modules.wy.entity.WyHouseEntity;
import com.kingen.modules.wy.entity.WyUnitEntity;


/**
 * 系统用户
 *
 * @author System
 * @email sunlightcs@gmail.com
 * @date 2016年9月18日 上午9:43:39
 */
public interface SysUserService
{

    /**
     * 查询用户的所有权限
     *
     * @param userId 用户ID
     */
    List<String> queryAllPerms(Long userId);

    /**
     * 查询用户的所有菜单ID
     */
    List<Long> queryAllMenuId(Long userId);

    /**
     * 根据用户名，查询系统用户
     */
    SysUserEntity queryByUserName(String username);

    /**
     * 根据用户ID，查询用户
     *
     * @param userId
     * @return
     */
    SysUserEntity queryObject(Long userId);

    /**
     * 查询用户列表
     */
    List<SysUserEntity> queryList(Map<String, Object> map);

    /**
     * 查询总数
     */
    int queryTotal(Map<String, Object> map);

    /**
     * 保存用户
     */
    void save(SysUserEntity user);

    /**
     * 修改用户
     */
    void update(SysUserEntity user);

    /**
     * 删除用户
     */
    void deleteBatch(Long[] userIds);

    /**
     * 修改密码
     *
     * @param userId      用户ID
     * @param password    原密码
     * @param newPassword 新密码
     */
    int updatePassword(Long userId, String password, String newPassword);

    /**
     * 查询到此用户有关的所有房屋实体
     * @param userId 用户id
     * @return
     */
    Set<WyHouseEntity> queryHaveHouses(Long userId);

    /**
     * 查询到此用户有关的所有单元实体
     * @param userId
     * @return
     */
    Set<WyUnitEntity> queryHaveUnit(Long userId);

    /**
     * 查询到此用户有关的所有楼栋实体
     * @param userId
     * @return
     */
    Set<WyBuildingEntity> queryHaveBuilding(Long userId);

    /**
     * 查询到此用户有关的所有社区实体
     * @param userId
     * @return
     */
    Set<WyCommunityEntity> queryHaveCommunity(Long userId);

    /**
     * 注意！此方法查询到的是此用户的所拥有的部门id，即物业公司的id。
     * 即此人的信息被那些物业公司所持有。
     * 并不是指此人所属的部门，用户所属的部门即系统级别的部门，不归属与任何一个小区。
     * @param userId
     * @return
     */
    Set<SysDeptEntity> queryHaveDept(Long userId);
}

package com.kingen.modules.sys.service.impl;


import com.kingen.modules.sys.entity.SysDeptEntity;
import com.kingen.modules.sys.service.SysDeptService;
import com.kingen.modules.wy.entity.*;
import com.kingen.modules.wy.service.*;
import org.apache.commons.lang.RandomStringUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.kingen.common.annotation.DataFilter;
import com.kingen.modules.sys.dao.SysUserDao;
import com.kingen.modules.sys.entity.SysUserEntity;
import com.kingen.modules.sys.service.SysUserRoleService;
import com.kingen.modules.sys.service.SysUserService;
import com.kingen.modules.sys.shiro.ShiroUtils;

import java.util.*;


/**
 * 系统用户
 *
 * @author System
 * @email sunlightcs@gmail.com
 * @date 2016年9月18日 上午9:46:09
 */
@Service("sysUserService" )
public class SysUserServiceImpl implements SysUserService
{
    @Autowired
    private SysUserDao sysUserDao;
    @Autowired
    private SysUserRoleService sysUserRoleService;
    @Autowired
    private WyHouseService wyHouseService;
    @Autowired
    private WyHouseUserService wyHouseUserService;
    @Autowired
    private WyUnitService wyUnitService;
    @Autowired
    private WyBuildingService wyBuildingService;
    @Autowired
    private WyCommunityService wyCommunityService;
    @Autowired
    private SysDeptService sysDeptService;
    @Override
    public List<String> queryAllPerms(Long userId)
    {
        return sysUserDao.queryAllPerms(userId);
    }

    @Override
    public List<Long> queryAllMenuId(Long userId)
    {
        return sysUserDao.queryAllMenuId(userId);
    }

    @Override
    public SysUserEntity queryByUserName(String username)
    {
        return sysUserDao.queryByUserName(username);
    }

    @Override
    public SysUserEntity queryObject(Long userId)
    {
        return sysUserDao.queryObject(userId);
    }

    @Override
    @DataFilter(tableAlias = "u", user = false)
    public List<SysUserEntity> queryList(Map<String, Object> map)
    {
        return sysUserDao.queryList(map);
    }

    @Override
    @DataFilter(tableAlias = "u", user = false)
    public int queryTotal(Map<String, Object> map)
    {
        return sysUserDao.queryTotal(map);
    }

    @Override
    @Transactional
    public void save(SysUserEntity user)
    {
        user.setCreateTime(new Date());
        //sha256加密
        String salt = RandomStringUtils.randomAlphanumeric(20);
        user.setSalt(salt);
        user.setPassword(ShiroUtils.sha256(user.getPassword(), user.getSalt()));
        sysUserDao.save(user);

        //保存用户与角色关系
        sysUserRoleService.saveOrUpdate(user.getUserId(), user.getRoleIdList());
    }

    @Override
    @Transactional
    public void update(SysUserEntity user)
    {
        if (StringUtils.isBlank(user.getPassword()))
        {
            user.setPassword(null);
        } else
        {
            user.setPassword(ShiroUtils.sha256(user.getPassword(), user.getSalt()));
        }
        sysUserDao.update(user);

        //保存用户与角色关系
        sysUserRoleService.saveOrUpdate(user.getUserId(), user.getRoleIdList());
    }

    @Override
    @Transactional
    public void deleteBatch(Long[] userId)
    {
        sysUserDao.deleteBatch(userId);
    }

    @Override
    public int updatePassword(Long userId, String password, String newPassword)
    {
        Map<String, Object> map = new HashMap<>();
        map.put("userId", userId);
        map.put("password", password);
        map.put("newPassword", newPassword);
        return sysUserDao.updatePassword(map);
    }

    @Override
    public Set<WyHouseEntity> queryHaveHouses(Long userId)
    {
        Set<WyHouseEntity> houses = new LinkedHashSet<>();
        Set<Long> houseIds = new HashSet<>();

        //先筛选一次房屋的id，使用Set确保无重复，减少查询操作,以及查询到的对象不一致的问题
        for (WyHouseUserEntity wyHouseUserEntity: wyHouseUserService.queryListByUserId(userId))
            houseIds.add(wyHouseUserEntity.getHouseId());

        //执行查询房屋，塞入结果中
        for (Long houseId : houseIds)
            houses.add(wyHouseService.queryObject(houseId));
        return houses;
    }

    @Override
    public Set<WyUnitEntity> queryHaveUnit(Long userId)
    {
        Set<WyUnitEntity> units = new LinkedHashSet<>();
        Set<Long> unitIds = new HashSet<>();

        Set<WyHouseEntity> houses = queryHaveHouses(userId);
        for(WyHouseEntity house : houses)
            unitIds.add(house.getUnitId());

        for (Long unitId : unitIds)
            units.add(wyUnitService.queryObject(unitId));

        return units;
    }

    @Override
    public Set<WyBuildingEntity> queryHaveBuilding(Long userId)
    {
        Set<WyBuildingEntity> buildings = new LinkedHashSet<>();
        Set<Long> buildingIds = new HashSet<>();

        Set<WyUnitEntity> units = queryHaveUnit(userId);
        for(WyUnitEntity unit : units)
            buildingIds.add(unit.getBulidingId());

        for (Long buildingId : buildingIds)
            buildings.add(wyBuildingService.queryObject(buildingId));

        return buildings;
    }

    @Override
    public Set<WyCommunityEntity> queryHaveCommunity(Long userId)
    {
        Set<WyCommunityEntity> communitys = new LinkedHashSet<>();
        Set<Long> communityIds = new HashSet<>();

        Set<WyBuildingEntity> buildings = queryHaveBuilding(userId);
        for(WyBuildingEntity building : buildings)
            communityIds.add(building.getCommunityId());

        for (Long communityId : communityIds)
            communitys.add(wyCommunityService.queryObject(communityId));

        return communitys;
    }

    @Override
    public Set<SysDeptEntity> queryHaveDept(Long userId)
    {
        Set<SysDeptEntity> depts = new LinkedHashSet<>();
        Set<Long> deptIds = new HashSet<>();

        Set<WyCommunityEntity> communitys = queryHaveCommunity(userId);
        for(WyCommunityEntity community : communitys)
            deptIds.add(community.getDeptId());

        for (Long deptId : deptIds)
            depts.add(sysDeptService.queryObject(deptId));

        return depts;
    }

}

由于严重的查询效率问题，以及成本问题考虑（毕竟数据库容量的增加，只需要增加硬盘，而服务器的性能增加，则需要cpu的投入，两者成本高低，不言而喻）。再者，基于用户友好的对待。也不应该牺牲用户的查询体验，使数据库的空间优化。

改进后的数据库设计如下。

以每一个低级持有到所有高级表的主键，从而获得一个查询性能上的提升。

此处还有一个缺陷，都为设计外键约束，需要时添加。

此设计在级数，也就是关联表数极多的情况下会多出极多的字段出来。可以使用此种设计时与第一种设计交互使用。但此操作会导致表的结构混乱，而实际操作中也很少出现级数很大的情况。

注意：

1：此设计会导致数据库有冗余字段，但是优化查询。

2：切记不可添加除外键之外的其他字段。例如不可在house这张表中添加community_name。只可添加community_id。不符合三范式。房屋可以有所在的社区，但不可有所在社区的名字，这是社区表的字段，请联表查询。

3：新增和修改操作，都要保持多表同步，请设立事务管理。导致新增和修改操作时间长。

此时的查询显而易见的得到了巨大的提升。

此两种设计方案，对比如下;

	优点	缺点
前者	1：数据库所占空间小，空间最大优化。 2：新增和修改速度快，影响小。	1：查询速度慢
后者	1：查询速度快	1：数据库的字段增多，空间牺牲 2：新增和删除操作复杂，操作缓慢。

具体使用哪一种表的设计方案，请结合项目情况选择。

文中有不当之处，还请各位多多指正！！