设计具有几种不同产品的数据库？ [英] Designing a database with several different kinds of products?

问题描述

作为我最近开始计划的一部分，我被要求建立一个数据库结构，其中将包含多个产品。例如，考虑一下亚马逊的结构方式。它有几个类别，在这些类别中，有几个子类别。

As part of a recent project I have started planning out, I am required to build the structure of a database which will contain several products. As an example, think of the way Amazon is structured. It has several categories and within those categories, several sub-categories.

我的问题是，从概念上讲，我不确定如何构建数据库表。我曾考虑过为类别和子类别创建一个自引用表，但是由于我确实打算在数据库中使用多种产品，因此我不知道是否应该将它们分组到一个名为产品或将它们全部放在单独的表格中。

My problem is that conceptually I am unsure on how to build the database tables. I have thought of creating a self-referencing table for the categories and sub-categories, but since I do plan to have a wide variety of products within the database, I don't know if I should just group them into one table called "Products" or put them all in separate tables.

例如，厕所是一种产品，而电视机可能是另一种产品。即使它们具有不同的类别/子类别，它们都是产品。通过将它们放在一个产品表中，它们将共享对它们都没有意义的属性。洗手间不需要分辨率或显示尺寸的属性（除非这是一个非常特殊的洗手间？），而电视则不需要座椅尺寸属性。

For example, a toilet would be one product while a television could be another. Even though they have different categories/sub-categories, they are both products. By placing them in one "Products" table, they would share attributes that would make no sense for both of them. A toilet would not need an attribute for resolution or display size(unless it is a very special toilet?) and a television wouldn't need a seat size attribute.

我想解决这个问题并仍然将所有内容保留在一个表中的方法是创建一堆NOT NULL属性，如果某些属性不存在，可能会丢失这些属性不一定，但是常识告诉我，这可能不是解决问题的最佳方法。

I thought that one to get around this and still keep everything in one table would be to create a bunch of NOT NULL attributes that could be missing for certain items if they weren't necessary, but common sense is telling me that this is probably not the best way to go about things.

因此，在这一点上，我觉得我真正的问题是弄清楚如何用几个类别/子类别以及不同种类的项目来构造该数据库及其表。我会为电视创建桌子还是为厕所创建桌子？这一切将如何构成？

So at this point, I feel that my real problem is figuring out how to structure this database and its tables with several categories/sub-categories and different kinds of items. Would I create a table for televisions and a table for toilets? How would this all be structured? How are these sort of problems normally planned out?

谢谢

推荐答案

这是我将表格分开的方式：

This is how I would separate the tables:

类别（例如家庭）

子类别（例如浴室是家庭的外键）

sub_categories (e.g. bathroom is a foreign key of household)

产品（例如陶瓷厕所）

至于其他属性，您可以将这些属性直接存储在产品表中，也可以创建另一个名为 products_extra_attributes 的表，然后将可选的NULL值存储在 products 表是指向单个产品附加属性的外键。

As for the extra attributes, you can either store these directly within the products table or create another table called products_extra_attributes and store an optional NULL value within the products table which would be a foreign key pointing toward the additional attributes for the individual product.

有道理吗？我稍后将通过手机回答此问题，否则将进行编辑。

Make sense? I'll make an edit later on if not as I'm answering this question from my phone.

这篇关于设计具有几种不同产品的数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！