商品分类表的设计

闲来无事思考了下电子商务中最常遇到的商品分类表的设计问题，思考结果如下，欢迎大家来评。

商品分类表的设计

1 数据库设计

   最常用的2个表，简单字段

1.1     分类表

            

   Code编码规则

第一层2位10-99

其他层3位  100-999

1.2 商品信息表

            

Keywords字段用于商品的搜索

Belong_category用于点击分类后的商品查找，由于其外键是分类表的code字段，故很容易搜索某个大类下的所有商品，且根据belong_category的值可以很容易的指导该商品属于哪个大类。

2 构造数据

2.1 构造树形的分类测试数据

 

构造好的数据如下

 

 

 代码

@Test

@Rollback(false)

public void testSave() {

insertTestData(4, 5);

 

}

 

private void insertTestData(int totalLevel, int brotherCount) {

 

for (int i = 0; i < totalLevel; i++) {

// First Layer

if (i == 0) {

insertFirstLayer(brotherCount);

} else {

insertOtherLayer(brotherCount, i + 1);

}

}

 

}

 

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(int brothersCount, int layerLevel) {

for (int i = 0; i < brothersCount; i++) {

long parent_code = 10 + i;

long code_tmp_1 = 0;

for (int j = (layerLevel - 2); j > 0; j--) {

code_tmp_1 = code_tmp_1*10+(long) Math.pow(100, j); 

}

if (code_tmp_1 != 0)

parent_code = Integer.parseInt("" + parent_code + code_tmp_1);

logger.debug("parent_code==" + parent_code);

 

for (int k = 0; k < brothersCount; k++) {

GoodsCategory otherlayer = new GoodsCategory();

long code_tmp = 100;

 

otherlayer.setName("The " + layerLevel + " layer");

otherlayer.setCode(String.valueOf(parent_code) + String.valueOf(code_tmp + k));

otherlayer.setParentCode(String.valueOf(parent_code));

this.categoryDao.save(otherlayer);

}

}

}

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;

 

}

 

}

 

public void printRoot() {

printInfoTreeData(root);

}

 

private void printInfoTreeData(TreeData treeData) {

System.out.println(treeData.getName() + " code:" + treeData.getCodes());

if (treeData.getChildren().size() != 0) {

System.out.println("                its children:");

for (TreeData treeDatatmp : treeData.getChildren()) {

printInfoTreeData(treeDatatmp);

}

}

}

}

 

class TreeData {

private String name;

private String codes;

 

private TreeData parent;

 

private List<TreeData> children = new ArrayList<TreeData>();

 

public TreeData getParent() {

return parent;

}

 

public void setParent(TreeData parent) {

this.parent = parent;

}

 

public String getName() {

return name;

}

 

public void setName(String name) {

this.name = name;

}

 

public String getCodes() {

return codes;

}

 

public void setCodes(String codes) {

this.codes = codes;

}

 

public List<TreeData> getChildren() {

return children;

}

 

public void setChildren(List<TreeData> children) {

this.children = children;

}

 

public void addChild(TreeData child) {

this.children.add(child);

}

 

}