类别,子类别和相关图书的设计数据库 [英] 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屋!