So because I have 4 different product types (books, magazines, gifts, food) I can't just put all products in one "products" table without having a bunch of null values. So I decided to break each product up into their own tables but I know this is just wrong (https://c1.staticflickr.com/1/742/23126857873_438655b10f_b.jpg).
因为我有4种不同的产品类型(书籍，杂志，礼物，食物)我不能只把所有的产品放在一个“产品”表中而没有一堆空值。所以我决定将每个产品分解成各自的表，但我知道这是错误的(https://c1.staticfliccflickr.com/1/742 / 236857873_438655b10f_b.jpg)。
I also tried creating an EAV model for this (https://c2.staticflickr.com/6/5734/23479108770_8ae693053a_b.jpg), but I got stuck as I'm not sure how to link the publishers and authors tables.
我还尝试为此创建一个EAV模型(https://c2.staticflickr.com/6/5734/23479108770_8ae693053a_b.jpg)，但是我不知道如何链接发布者和作者表，所以被卡住了。
I know this question has been asked a lot but I don't understand ANY of the answer's I've seen. I think this is because I'm a very visual learner and this makes it hard to understand what's being talked about when not a lot of information is given.
我知道这个问题已经被问过很多次了，但是我不明白我所见过的答案。我认为这是因为我是一个视觉型的学习者，这让我很难理解当没有太多信息的时候人们在谈论什么。
3 个解决方案
#1
2
Your model is on the right track, except that the product name should be sufficient you don't need Gift name, book name etc. What you put in those tables is the information that is specific to the type of product that the other products don't need. The Product table contains all the common fields. I would use productid in the child tables rather than renaming it giftID, magazineID etc. It is easier to remember what things are celled when you are consistent in nameing them.
你的模型在正确的轨道上，除了产品名应该足够你不需要礼物名，书名等等。你放在那些表格里的是特定于其他产品不需要的产品类型的信息。Product表包含所有公共字段。我将在子表中使用productid，而不是将它重命名为giftID、magazineID等。
Now to be practical, you put as much as you can into the product table especially if you are going to do calculations. I prefer the child tables in this specific case to have what is mostly display information. So product contains the product name, the cost, the type of product, the units the product is sold in etc. The stuff that generally is needed to calculate the cost of an order or to have a report of what was ordered. There may be one or two fields that can contain nulls, but it simplifies the calculation type queries so much it might be worth it.
现在为了实际，你要尽可能多地放入产品表特别是如果你要做计算的话。我希望在这种特定情况下的子表具有主要显示信息。产品包含产品名称，成本，产品类型，产品的销售单位等等，通常需要计算订单成本或报告订单成本。可能有一个或两个字段可以包含nulls，但它简化了计算类型查询，因此可能值得。
The meat of the descriptive details though would go in the child table for the type of product. These would usually only be referenced when displaying the product in the shopping area and only one at a time, so you can use the product type to let you only join to the one child table you need for display. So while the order cares about the product number and name and cost calculations, it probably doesn't need to go line by line describing the book ISBN number or the megapixels in a camera. But the description page of the product does need those things.
描述性细节的肉会在子表中显示产品的类型。这些通常只在在购物区域中显示产品时才会被引用，而且每次只显示一个，所以您可以使用产品类型来让您只连接到需要显示的一个子表。因此，虽然订单关心产品编号和名称和成本计算，但它可能不需要按一行一行地描述图书的ISBN号或相机中的megapixels。但是产品的描述页面确实需要这些东西。
This approach is not purely relational, although it mostly is, but it does group the information by the meanings of the data and how they will be used which will make the database easier to understand and query. I am a big fan of relational tables because database just work better when they hit at least the third normal form but sometimes you can go too far for practicality, so the meaning of the data and the way you are grouping to use the data (and not just for the user interface, but for later reporting as well) is almost always one of my considerations in design.
这种方法不是纯关系的，虽然主要是这样的，但是它通过数据的含义和如何使用它们来对信息进行分组，从而使数据库更容易理解和查询。我喜欢关系表,因为数据库只是更好地工作时,至少达到第三范式为实用性但是有时你可以走得太远,所以数据的意义和分组的方式使用数据(而不仅仅是用户界面,但后来报道)几乎总是我考虑在设计之一。
#2
0
Breaking each product type into its own table is fine - let the child tables use the same id as the parent Product table, and create views for the child tables that join with Product
将每个产品类型分解为自己的表是可以的——让子表使用与父产品表相同的id，并为与product结合的子表创建视图
#3
0
Your case is a classic case of types and subtypes. This is often called class/subclass in object modeling and generalization/specialization in ER modeling. It's a well understood pattern. There are known techniques for dealing with this pattern.
您的案例是典型的类型和子类型。这通常在对象建模和ER建模中的泛化/专门化中称为类/子类。这是一个很容易理解的模式。有一些已知的处理这种模式的技术。
Visit the following tabs, and read the description under the info tab (presented as "learn more"). Also look over the questions grouped under these tags.
访问以下选项卡，并在info选项卡下阅读描述(以“学习更多”表示)。还可以查看这些标签下的问题。
single-table-inheritance class-table-inheritance shared-primary-key
single-table-inheritance class-table-inheritance shared-primary-key
If you want to rean in more depth use these buzzwords to search for articles on the web.
如果你想更深入地了解rean，请使用这些流行语在网上搜索文章。
You've already discovered and discarded single table inheritance on your own. Other answers have pointed you at shared primary key. Class table inheritance involves a single table for generalized data as well as the four specialized tables. Shared primary key is generally used in conjunction with class table inheritance.
您已经发现并丢弃了单独的表继承。其他答案已经指向了共享主键。类表继承涉及一个通用数据表以及四个专用表。共享主键通常与类表继承一起使用。
#1
2
Your model is on the right track, except that the product name should be sufficient you don't need Gift name, book name etc. What you put in those tables is the information that is specific to the type of product that the other products don't need. The Product table contains all the common fields. I would use productid in the child tables rather than renaming it giftID, magazineID etc. It is easier to remember what things are celled when you are consistent in nameing them.
你的模型在正确的轨道上，除了产品名应该足够你不需要礼物名，书名等等。你放在那些表格里的是特定于其他产品不需要的产品类型的信息。Product表包含所有公共字段。我将在子表中使用productid，而不是将它重命名为giftID、magazineID等。
Now to be practical, you put as much as you can into the product table especially if you are going to do calculations. I prefer the child tables in this specific case to have what is mostly display information. So product contains the product name, the cost, the type of product, the units the product is sold in etc. The stuff that generally is needed to calculate the cost of an order or to have a report of what was ordered. There may be one or two fields that can contain nulls, but it simplifies the calculation type queries so much it might be worth it.
现在为了实际，你要尽可能多地放入产品表特别是如果你要做计算的话。我希望在这种特定情况下的子表具有主要显示信息。产品包含产品名称，成本，产品类型，产品的销售单位等等，通常需要计算订单成本或报告订单成本。可能有一个或两个字段可以包含nulls，但它简化了计算类型查询，因此可能值得。
The meat of the descriptive details though would go in the child table for the type of product. These would usually only be referenced when displaying the product in the shopping area and only one at a time, so you can use the product type to let you only join to the one child table you need for display. So while the order cares about the product number and name and cost calculations, it probably doesn't need to go line by line describing the book ISBN number or the megapixels in a camera. But the description page of the product does need those things.
描述性细节的肉会在子表中显示产品的类型。这些通常只在在购物区域中显示产品时才会被引用，而且每次只显示一个，所以您可以使用产品类型来让您只连接到需要显示的一个子表。因此，虽然订单关心产品编号和名称和成本计算，但它可能不需要按一行一行地描述图书的ISBN号或相机中的megapixels。但是产品的描述页面确实需要这些东西。
This approach is not purely relational, although it mostly is, but it does group the information by the meanings of the data and how they will be used which will make the database easier to understand and query. I am a big fan of relational tables because database just work better when they hit at least the third normal form but sometimes you can go too far for practicality, so the meaning of the data and the way you are grouping to use the data (and not just for the user interface, but for later reporting as well) is almost always one of my considerations in design.
这种方法不是纯关系的，虽然主要是这样的，但是它通过数据的含义和如何使用它们来对信息进行分组，从而使数据库更容易理解和查询。我喜欢关系表,因为数据库只是更好地工作时,至少达到第三范式为实用性但是有时你可以走得太远,所以数据的意义和分组的方式使用数据(而不仅仅是用户界面,但后来报道)几乎总是我考虑在设计之一。
#2
0
Breaking each product type into its own table is fine - let the child tables use the same id as the parent Product table, and create views for the child tables that join with Product
将每个产品类型分解为自己的表是可以的——让子表使用与父产品表相同的id，并为与product结合的子表创建视图
#3
0
Your case is a classic case of types and subtypes. This is often called class/subclass in object modeling and generalization/specialization in ER modeling. It's a well understood pattern. There are known techniques for dealing with this pattern.
您的案例是典型的类型和子类型。这通常在对象建模和ER建模中的泛化/专门化中称为类/子类。这是一个很容易理解的模式。有一些已知的处理这种模式的技术。
Visit the following tabs, and read the description under the info tab (presented as "learn more"). Also look over the questions grouped under these tags.
访问以下选项卡，并在info选项卡下阅读描述(以“学习更多”表示)。还可以查看这些标签下的问题。
single-table-inheritance class-table-inheritance shared-primary-key
single-table-inheritance class-table-inheritance shared-primary-key
If you want to rean in more depth use these buzzwords to search for articles on the web.
如果你想更深入地了解rean，请使用这些流行语在网上搜索文章。
You've already discovered and discarded single table inheritance on your own. Other answers have pointed you at shared primary key. Class table inheritance involves a single table for generalized data as well as the four specialized tables. Shared primary key is generally used in conjunction with class table inheritance.
您已经发现并丢弃了单独的表继承。其他答案已经指向了共享主键。类表继承涉及一个通用数据表以及四个专用表。共享主键通常与类表继承一起使用。