数据库设计，分类项目，子类别和主题

CREATE TABLE Product (ProductID int, Description nvarchar(100))

CREATE TABLE CategoryID (CategoryID int, Description nvarchar(100),ProductID int)
CREATE TABLE SubCategoryID (SubCategoryID int, CategoryID int, Description nvarchar(100),ProductID int)

CREATE TABLE ThemeID (ThemeID int, Description nvarchar(100),ProductID int)

I'm using Laravel ORM

我用Laravel ORM

Product hasMany-> Category
Product hasMany-> SubCategory
Product hasMany-> Theme

Category BelongsTo->Product
SubCategory BelongsTo->Category
Theme BelongsTo -> Product

Each item has got a theme and belongs to multiple category, Sub Category is Optional.

每个项目都有一个主题，属于多个类别，子类别是可选的。

Any advice on this design? Thanks in advance!

对这个设计有什么建议吗?提前谢谢!

Is this best practice? Trying to Start right

这是最佳实践吗?尝试开始吧

1 个解决方案

#1

Let show you an idea which IMHO I think it is good to be used: first create the category table:

让我们向你展示一个我认为使用起来很好的想法:首先创建目录表:

CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `category_father_id` int(11) DEFAULT '0',
  `is_active` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `category_father_id` (`category_father_id`),
  CONSTRAINT `constraint_name` FOREIGN KEY (`category_father_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

then for your product table you can keep it as it is:

那么你的产品表就可以保持原样:

CREATE TABLE Product (ProductID int, Description nvarchar(100));

Now Usually you can have a Product which belongs to several categories. Hence, the correct way to do it is to have m:n relation between Product and Category. and it can be done by adding:

现在通常你可以有一个属于几个类别的产品。因此，正确的方法是在产品和类别之间有m:n的关系。可以这样做:

create table product_category(
ProductId int(11) not null,
CategoryId int(11) not null,
unique (ProductId,CategoryId),
foreign key (ProductId) references Product (ProductID) on update cascade on delete cascade,
foreign key (CategoryId) references category (id) on update cascade on delete cascade
)engine=innodb;

and you can keep the theme as it is.

你可以保持主题不变。

you will see that category table can handles the nesting categories using category_father_id foreign key on it self.

您将看到category表可以使用category_father_id外键来处理嵌套类别。

But a note to keep in mind is, after all, it is always about your domain/business logic.

但要记住的一点是，它始终是关于您的域/业务逻辑的。

#1