处理以下案件的最佳方式？ [英] Best way to handle following case?
问题描述
嘿，
我正在开发一个数据库，用于产品申报（食品和东西）。
关注感兴趣的桌子
- 包含不同成分的表格（IDIngredient，名称，能量，脂肪，蛋白质，碳水化合物，购买价格等值）
- 包含不同产品的表格（IDArticle，Name，...）
- 包含收件人的表格（IDArticle，IDIngredient，Amount）
- 一个表格，其中包含每个成分列表IDArticle
我的挑战是数据库计算价值的一些（不是全部）产品（例如生产成本，脂肪/能量/蛋白质/碳水化合物）每100克）用作其他产品的成分....所以IDArticle最终成为另一个收件人的IDIngredient可以这么说。
我不知道该怎么做并且在我将自己挖到一个太大的洞之前，我会欣赏一些关于该怎么做的暗示或建议。我总能感觉到自己的方式。
（只是为了提出一个想法）我已经制作了一个表格（请参见附图），用户可以将表格和其他类型的表格放在一起过敏原等信息。用户应该能够在这里查找所有必要的成分，包括那些IDArticles / IDAingredients。一旦用户选择了一种成分，必要的信息就会写入收件表（以及其他相关表格）。
（我的想法到目前为止）我猜第一步就是检查 -box"这样数据库就能够识别用户应该使用哪些文章作为一种成分。
也许最好的是将这些文章写入成分表的宏？如何避免双重录入？文章上的名称可能会更改（用户错误）宏更新，pluss IDArticle和IDIngredient对于这些产品不一样。也许是自动编号的文章表中的新列。对于每个新的已检查文章，该列给出了一个数字，如果它是新记录，则写入成分表，或者如果它已经存在则用作查找功能......？
最坏的情况，用户必须手动输入产品作为成分表中的成分，但计算出的值应该是动态的，包括成分列表......所以如果有解决方案，那将非常好。
非常感谢任何帮助。
Best，
Martin
Hey,
I am working on a database that will be used for declaration on products (food and stuff).
Following tables are of interest
- a table containing different ingredients (IDIngredient, Name, values such as energy, fat, protein, carbohydrats, purchase price)
- a table containing different products (IDArticle, Name,...)
- a table containing recipies (IDArticle, IDIngredient, Amount)
- a table that gives the ingredientlist per IDArticle
My challenge is that some (not all) of the products that the database calculates values for, (such as cost to produce, fat/energy/protein/carbohydrats per 100 gram) are used as ingredients in other products.... so the IDArticle ends up with being an IDIngredient in another recipie so to speak.
I am not sure what to do and would appreciate a hint or advice on what to go for, before I dig myself into a too big hole. I can always feel my way onwards.
(Just to give an idea) I have made a form (please see attached picture) where the user can put together the recipie and other types of information such as allergens etc. The user should be able to look up all the necessary ingredients here, including those IDArticles/IDAingredients. Once the user selects an ingredient, necessary information is written into the recipie table (and other relevant tables).
(My thinking so far) I guess first step would be a "check-box" so that the database can be able to identify which articles the user should be able to use as an ingredient.
Maybe the best would be a macro that writes those articles into the ingredienttable? How to avoid double entries? The name on the article could change (user error) between macro updates, pluss IDArticle and IDIngredient can''t be the same for those products. Maybe a new column in article table with autonumber. For each new "checked" article, the column gives a number, which is either written into ingredient table if it is a new record, or used as lookup function if it already exists...?
In worst case, the user would have to manually enter the product as an ingredient in the ingredienttable, but the calculated values should be dynamic, including the ingredientlist.... so it would be really nice if there is a solution to it.
Any help is very much appreciated.
Best,
Martin
推荐答案
Martin Lang ： - 一个包含收件人的表格（IDArticle，IDIngredient，Amount）
- 一个表格，给出每个IDArticle的成分列表
Martin Lang: - a table containing recipies (IDArticle, IDIngredient, Amount)
- a table that gives the ingredientlist per IDArticle
当前者涵盖这些信息时，为什么后者是必要的？
我通常认为，你的成分是用食谱数据混合在一起组成的产品（或文章）。但是，有些产品本身被用作其他配方的成分是否都正确？
Why is the latter necessary when the former covers this information?
I assume generally, that your ingredients are mixed together using recipe data to form a product (or article). However, some products are themselves used as ingredients in other recipes Is that all correct?
@NeoPa
嗯，当打印报告等时，后者是必要的。一些报告，用户希望将成分显示为面包（面粉，水，盐，酵母），而其他地方则是用户希望的结果
面包< br $>
面粉
水
盐
酵母
Nico''在另一个帖子中的解决方案使得两个表格成为必要:)
回答你的上一个问题...正确：）
@NeoPa
Well, the latter is necessary when printing reports etc. Some reports, the user would like to have the ingredients shown as Bread (Flour, Water, Salt, Yeast) whereas other places the user would like it as
Bread
Flour
Water
Salt
Yeast
Nico''s solution in another post makes the two tables necessary :)
To answer your last question... correct :)
当然是食谱表也给你那个设施。
无论如何。如果您的某个项目既可以是成分，也可以是生产的文章，那么在我看来，您可以选择两种方法：
Surely the Recipes table gives you that facility too.
Anyway. If you have a situation where an item can be both an ingredient, as well as a produced article, then it seems to me you have a choice of two approaches :
- 有一个处理两者的递归表。它的设计需要包含两种类型所需的字段。一般来说，这涉及从中获取数据的不同结构和方法。
- 较少规范化，但可以实际，复制在Ingredients表中也需要的结果项。存在的这类项目越多，这种方法就越不合适。
我会自己选择以前的方法，但我很感激早期的反馈，没有经验的数据库设计师可以找到相应的方法来解决这个问题。很难。
我会留给你做出决定。
I would choose the former approach myself, but I do appreciate from earlier feedback that inexperienced db designers can find getting their heads around this approach quite difficult.
I''ll leave it to you to make your decision.
这篇关于处理以下案件的最佳方式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
