类别，子类别和相关图书的设计数据库 [英] Design database for category, subcategory and associated books

问题描述

我知道有几个问题类似于一个问题的答案。但是他们的方法并不令人信服。

我的问题是如何构建一个类别的数据库，该类别可以包含书籍和子类别，也可以有图书？ / p>

我已经设计了一个数据库，但并不相信这种方法。所以我真的很感激，如果有经验的人给了我一些建议或改进或完全不同的方法。

这样我的桌子看起来像这样（不介意这不是实际代码）

  TABLE类别
 ID 
 user_id  - 用户的外键
名称
 
 TABLE子类别
 ID 
 user_id 
 category_id 
名称
  

该书的表具有相同的设计。

解决方案

无论是类别还是多个表格，无论是顶级类还是子类-类别。他们只是类别。

所以，有一个名为类别的表，一个 parent_id 字段：

  //类别表
 id 
名称
 user_id 
 parent_id 
  

当您要拉取所有顶级类别时，只需对类别表格，条件是 parent_id 为null。

然后，当您想要拉子类别，只需对类别表执行查询，条件是 parent_id = 123 （或无论如何）。

这不仅使所有内容都更加清洁，而且还可以扩展，以防您想要继续添加子子子类别。 ..etc。

---

另一个选项是使用 CakePHP的TreeBehavior 。

我个人只是宁愿使用我的方式上面提到，但可能只是因为我没有花时间真正了解这个行为。

I know there has been a few answer to a couple of questions similar to the one am asking. But their approach didn't look convincing.

My question is how would I structure a database for a category that can have books and a subcategory which can also have books?

I have already design a database, but am not convinced by the approach. So I would really appreciate if the guys with experience gave me some advice or improvement or a completely different approach.

This how my tables look like (bare in mind this is not the actual code)

TABLE Category
    ID
    user_id -- Foreign key from user
    name

TABLE SubCategory
    ID
    user_id
    category_id
    name

The table for the book have the same design.

解决方案

There's no reason to have more than one table for "categories", whether it be a top-level category or a sub-category. They're all just "categories".

So, have a single table called "categories", with a parent_id field:

// categories table
id
name
user_id
parent_id

When you want to pull all top level categories, just run your query against the categories table with a condition that parent_id is null.

Then, when you want to pull sub categories, just run the query against the categories table with a condition that parent_id = 123 (or whatever).

Not only does this keep everything a lot cleaner, but it also allows for expansion in case you want to continue adding sub-sub-sub-sub categories...etc.

---

Another option is to use CakePHP's TreeBehavior.

I personally just rather use the way I suggested above, but might just be because I haven't taken the time to really understand this behavior enough.

这篇关于类别，子类别和相关图书的设计数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！