&l

商品分类表的设计

 

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

商品分类表的设计

1 数据库设计

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

1.1     分类表

           

   Code编码规则

第一层2位10-99

其他层3位  100-999

1.2 商品信息表

           

Keywords字段用于商品的搜索

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

2 构造数据

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

 

构造好的数据

 

 

 代码

 

 

Java代码 

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;

评论

1 楼 spyker 2010-01-29  

如果商品的种类不多的话 这样基本够用
如果多的话
就要考虑其他方式