&l
商品分类表的设计
闲来无事思考了下电子商务中最常遇到的商品分类表的设计问题,思考结果如下,欢迎大家来评。
商品分类表的设计
1 数据库设计
最常用的2个表,简单字段
1.1 分类表
Code编码规则
第一层2位10-99
其他层3位 100-999
1.2 商品信息表
Keywords字段用于商品的搜索
Belong_category用于点击分类后的商品查找,由于其外键是分类表的code字段,故很容易搜索某个大类下的所有商品,且根据belong_category的值可以很容易的指导该商品属于哪个大类。
2 构造数据
2.1 构造树形的分类测试数据
构造好的数据
代码
private int totalLevel = 5;
private int brotherCount=10;
@Before
public void setUp() throws Exception {
}
@After
public void tearDown() throws Exception {
}
@Test
@Rollback(false)
public void testSave() {
insertTestData(brotherCount);
}
private void insertTestData(int brotherCount) {
insertFirstLayer(brotherCount);
for (int i = 0; i < brotherCount; i++)
insertOtherLayer(10 + i, brotherCount, 2);
}
private void insertFirstLayer(int brothersCount) {
for (int i = 0; i < brothersCount; i++) {
GoodsCategory first = new GoodsCategory();
first.setName("The first layer" + i);
first.setCode(String.valueOf(10 + i));
first.setParentCode(String.valueOf(0));
this.categoryDao.save(first);
}
}
private void insertOtherLayer(long parent_code, int brothersCount, final int currentLevel) {
int currentLevel_tmp = currentLevel;
if (currentLevel > totalLevel)
return;
logger.debug("parent_code==" + parent_code);
for (int k = 0; k < brothersCount; k++) {
GoodsCategory otherlayer = new GoodsCategory();
long code_tmp = 100;
String current_code = String.valueOf(parent_code) + String.valueOf(code_tmp + k);
otherlayer.setName("The " + currentLevel_tmp + " layer");
otherlayer.setCode(current_code);
otherlayer.setParentCode(String.valueOf(parent_code));
this.categoryDao.save(otherlayer);
insertOtherLayer(Long.parseLong(current_code), brothersCount, currentLevel_tmp + 1);
}
}
@Test
public void testGetAllCategorys() {
// fail("Not yet implemented");
long totalRecordCount=0;
for (int i = 1; i < totalLevel+1; i++) {
totalRecordCount+=Math.pow(brotherCount, i);
}
System.out.println("totalRecordCount========"+totalRecordCount);
List<GoodsCategory> goodCategories = this.categoryDao.getAll();
System.out.println("list size======"+goodCategories.size());
TreeHelper treeHelper = new TreeHelper(goodCategories);
System.out.println("print tree");
//treeHelper.printRoot();
}
3 测试用例
3.1 测试并构造树形
public class TreeHelper {
- TreeData root = new TreeData();
- protected final transient Logger log = LoggerFactory.getLogger(getClass());
- public TreeHelper(List<GoodsCategory> goodCategories) {
- buildTree(goodCategories);
- }
- public TreeData getRoot() {
- return root;
- }
- private void buildTree(List<GoodsCategory> goodCategories) {
- root.setCodes("0");
- root.setName("Root");
- TreeData td_pre = root;
- int codelenth_pre = 0;
- String code_pre="";
- for (GoodsCategory goodsCategory : goodCategories) {
- String code = goodsCategory.getCode();
- int codelenth_current = code.length();
- TreeData td_current = new TreeData();
- td_current.setCodes(code);
- td_current.setName(goodsCategory.getName());
- log.info("codelenth_last="+codelenth_pre+" codelenth_current="+codelenth_current);
- log.info("code_pre="+code_pre+" code_current="+code);
- // create new child
- if (codelenth_pre < codelenth_current) {
- td_pre.addChild(td_current);
- td_current.setParent(td_pre);
- td_pre = td_current;
- code_pre = code;
- }
- // add child to parent
- else if (codelenth_pre == codelenth_current) {
- td_pre.getParent().addChild(td_current);
- td_current.setParent(td_pre.getParent());
- }
- // add child continue
- else {
- int dilevel = (int) Math.ceil(new Double(codelenth_pre - codelenth_current)/ 3);
- log.info("dilevel="+dilevel);
- for (int i = 0; i < dilevel; i++) {
- td_pre = td_pre.getParent();
- log.info("td_pre code="+td_pre.getCodes());
- }
- code_pre = td_pre.getCodes();
- td_pre.getParent().addChild(td_current);
- td_current.setParent(td_pre.getParent());
- td_pre = td_current;
- }
- codelenth_pre = codelenth_current;