设计社区数据库时，遇到有关
物业公司 社区 楼栋 单元 房屋 的基础表设计。
这几张表之间都是包含关系，故设计为此形式。有点类似于链表。为了叙述方便，暂且称大的概念为高级表，小的概念为低级表。低级表关系着一个高级表的主键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：新增和删除操作复杂，操作缓慢。 |
具体使用哪一种表的设计方案，请结合项目情况选择。
文中有不当之处，还请各位多多指正！！